New Features in SQLXML 2'0 XML for SQL Server 2000 Web Release 2 Karthik Ravindran MSXML Beta Produc

1
New Features in SQLXML 2.0 (XML for SQL Server
2000 Web Release 2) Karthik RavindranMSXML
Beta Product Lead (PSS)Microsoft Corporation
2
Agenda

• Review XML features introduced in Microsoft SQL
  Server 2000 RTM
• Review new features introduced in XML for SQL
  Server 2000 Web Release 1
• Examine the following new features introduced in
  XML for SQL Server 2000 Web Release 2 (SQLXML
  2.0)
• Enhancements to the SQLXML IIS virtual directory
  management MMC snap-in
• Client-side XML formatting
• Support for XSD mapping schemas
• The SQLXMLOLEDB provider
• SQLXML managed classes (.NET)
• DiffGrams
• Examine issues related to the side-by-side
  installation of SQLXML 2.0

3
Key XML Features Introduced in SQL Server 2000 RTM

• Accessing SQL Server 2000 data in XML format
• Accessing SQL Server 2000 data using HTTP
• SQLXML IIS virtual directories
• FOR XML extension to the TSQL SELECT statement
• URL-based queries to access data

4
Key XML Features Introduced in SQL Server 2000
RTM (2)

• SQLXML template queries
• XPath queries against annotated XDR mapping
  schemas
• OPENXML()
• ADO 2.6 Extensions to the ADODB.Command object
  to facilitate the execution of SQLXML queries

5
SQLXML Architecture in SQL Server 2000 RTM
Backend SQL Server
Middle Tier IIS Server
Client (IE) SQLXML HTTP Request
SQLISAPI (Sqlisapi.dll)
SQL Server 2000
SQLXML (sqlxmlx.dll)
IIS
SQLOLEDB
FOR XML
OPENXML
XSL
Templates
Schemas
PUBS
6
XML Features Introduced in SQLXML Web Release 1

• XML Updategrams
• A mechanism to insert, update, and delete data in
  a SQL Server 2000 database using information
  contained in an XML document
• The XML document (XML Updategram) will contain
  predefined and custom elements/attributes to
  represent the pre- and post-modification states
  of the data
• The Updategram can optionally use an annotated
  XDR mapping schema to map its custom elements and
  attributes to database tables and columns
• Listen to the WebCast on SQLXML Updategrams for
  an in-depth introduction to the technology
• http//support.microsoft.com/servicedesks/Webcast
  s/WC100201/wcblurb100201.asp

7
XML Features Introduced in SQLXML Web Release 1
(2)

• SQLXML Bulk Load
• A programmable COM component
• Used to programmatically bulk insert data into
  SQL Server 2000 tables
• Source of data is an XML document
• The XML data document uses an annotated XDR
  mapping schema to map the XML elements and
  attributes to database tables and columns
• The bulk loaded data is not stored in the
  database in native XML format

8
Key New Features in SQLXML 2.0

• Enhancements to the SQLXML IIS Virtual Directory
  Management MMC snap-in
• Client-side XML formatting
• Support for XSD mapping schemas
• The SQLXMLOLEDB provider
• SQLXML managed classes (.NET)
• DiffGrams

9
SQLXML Architecture in SQLXML 2.0
Backend SQL Server
Middle Tier IIS Server
Client (IE) SQLXML HTTP Request
SQLISAPI (Sqlis2.dll)
SQL Server 2000
SQLXML (sqlxml2.dll)
IIS
SQLOLEDB
FOR XML
OPENXML
XSL
Templates
Schemas
PUBS
10
SQLXML 2.0 IIS Virtual Directory Management
Snap-in
To launch, click the Start button, select
Programs, select SQLXML 2.0, and click Configure
IIS Support
11
Upgrading Existing SQLXML IIS Virtual Directories
12
Upgrading Existing SQLXML IIS Virtual Directories
(2)

• What happens when executing an upgrade?
• The SQLXML 2.0 ISAPI filter (Sqlis2.dll) is
  registered to process SQLXML HTTP requests
  targeted at the virtual directory
• Existing virtual directory settings are retained
  in the SQLXML 2.0 version
• New virtual directory configuration options
  introduced in SQLXML 2.0 are added to the virtual
  directorys Properties dialog box

13
Creating a New SQLXML 2.0 IIS Virtual Directory
14
Creating a New SQLXML 2.0 IIS Virtual Directory
(2)
15
Creating a New SQLXML 2.0 IIS Virtual Directory
(3)
16
Creating a New SQLXML 2.0 IIS Virtual Directory
(4)
17
Creating a New SQLXML 2.0 IIS Virtual Directory
(5)
18
Creating a New SQLXML 2.0 IIS Virtual Directory
(6)
19
Creating a New SQLXML 2.0 IIS Virtual Directory
(7)
20
Testing the Base SQLXML Functionality

• Execute a URL query using IE
• Example http//IISServer/pubs?sqlSelect from
  Authors FOR XML AUTOROOTROOT
• Execute a query using a SQLXML template stored in
  the /template folder
• Sample Template (GetAuthors.xml)
• ltROOT xmlnssql"urnschemas-microsoft-comxml-sql
  "gt
• ltsqlquerygt
• Select from Authors FOR XML AUTO
• lt/sqlquerygt
• lt/ROOTgt
• To test template
• http//IISServer/pubs/template/GetAuthors.xml

21
Testing the Base SQLXML Functionality (2)

• Execute an XPath query against an annotated
  mapping schema stored in the /schema folder
• Sample Schema (AuthorSchema.xml)
• ltxsdschema xmlnssql"urnschemas-microsoft-comm
  apping-schema" xmlnsxsd"http//www.w3.org/2001/X
  MLSchema"gt
• ltxsdelement name"Author" sqlrelation"Authors
  " gt
• ltxsdcomplexTypegt
• ltxsdattribute name"AuthorId"
  sqlfield"au_id" type"xsdstring"/gt
• ltxsdattribute name"FName"
  sqlfield"au_fname" type"xsdstring"/gt
• ltxsdattribute name"LName"
  sqlfield"au_lname" type"xsdstring"/gt
• lt/xsdcomplexTypegt
• lt/xsdelementgt
• lt/xsdschemagt
• Sample XPath query
• http//IISServer/pubs/schema/AuthorSchema.xml/Auth
  or_at_AuthorId"409-56-7008"

22
Client-Side XML Formatting

• In previous versions of SQLXML, the XML
  transformation of the rowset generated by a FOR
  XML query was executed on the server
• XML transformation of the rowset generated by the
  Select statement in a FOR XML query can now be
  optionally executed on the client
• This option can be used to offload the XML
  formatting of rowset data to the client
• The FOR XML NESTED clause
• To support client-side XML formatting, SQLXML 2.0
  introduces the following
• Run on the client IIS virtual directory property
• client-side-xml attribute for the sqlquery
  SQLXML Template element
• The SQMLXMLOLEDB OLE DB provider

23
Architecture of Server-Side XML Formatting

• The SQLXMLOLEDB OLE DB provider will use
  Sqlxml2.dll
• The SQLOLEDB OLE DB provider will use
  Sqlxmlx.dll
• Set the SQLXML Version dynamic property of the
  SQLOLEDB provider
• to SQLXML.2.0 to make it use Sqlxml2.dll

24
Architecture of Client-Side XML Formatting

• The SQLXMLOLEDB OLE DB provider must be used
  when
• client-side XML formatting is required
• SQLOLEDB is the only supported data provider in
  this release

25
Support in SQLXML Templates for Client-Side XML
Formatting

• The ltsqlquerygt element has been extended to
  support a new client-side-xml attribute
• Setting the client-side-xml attribute to 1
  indicates that client-side XML formatting is to
  be used when executing the template query
• The client-side-xml attribute setting in a SQLXML
  template overrides the Run on Client IIS Virtual
  Directory property setting
• ltROOT xmlnssql"urnschemas-microsoft-comxml-sql
  "gt
• ltsqlquery client-side-xml"1"gt
• Select from authors FOR XML NESTED
• lt/sqlquerygt
• lt/ROOTgt

26
Support for XSD Schemas

• SQLXML 2.0 fully supports XSD schemas
• SQLXML annotated mapping schemas can now be
  written using the XSD schema definition language
• XML Updategrams can reference XSD mapping schemas
• SQLXML BulkLoad can use XSD mapping schemas
• XPath queries can be executed against annotated
  XSD Schemas

27
The SQLXMLOLEDB OLE DB Provider

• An OLE DB provider that exposes SQLXML 2.0
  functionality through ADO
• Not a rowset provider can only output results as
  a stream
• Must specify the adExecuteStream ADO flag when
  executing commands that use this provider
• Uses a data provider to connect to the data
  source
• SQLOLEDB and SQL Server 2000 are the only
  supported data provider and data source in this
  release

28
Using the SQLXMLOLEDB Provider to Execute a FOR
XML Query

• Dim oTestStream As New ADODB.Stream
• Dim oTestConnection As New ADODB.Connection
• Dim oTestCommand As New ADODB.Command
• Dim conStr As String
• conStr "providerSQLXMLOLEDB.2.0data
  providerSQLOLEDB _
• data source(local)initial
  catalogpubsuser idsapassword"
• oTestConnection.Open conStr
• oTestCommand.ActiveConnection oTestConnection
• oTestCommand.Properties("ClientSideXML") "True"
• oTestCommand.CommandText "SELECT from Authors
  Employees FOR " _
• 
  "XML NESTED"
• oTestStream.Open
• oTestCommand.Properties("Output Stream").Value
  oTestStream
• oTestCommand.Properties("xml root") "root"
• oTestCommand.Execute , , adExecuteStream
• oTestStream.Position 0

29
Using the SQLXMLOLEDB Provider to Execute a
Template Query

• Dim oTestStream As New ADODB.Stream
• Dim oTestConnection As New ADODB.Connection
• Dim oTestCommand As New ADODB.Command
• Dim conStr As String
• conStr "providerSQLXMLOLEDB.2.0data
  providerSQLOLEDBdata source(local)" _
• "initial catalogpubsuser idsapassword"
• oTestConnection.Open conStr
• oTestCommand.ActiveConnection oTestConnection
• oTestCommand.Properties("ClientSideXML") "True"
• oTestCommand.CommandText _
• "ltROOT xmlnssql'urnschemas-microsoft-co
  mxml-sql' gt" _
• " ltsqlquerygt " _
• " SELECT FROM Authors FOR XML NESTED
  " _
• "lt/sqlquerygt " _
• " lt/ROOTgt "
• oTestStream.Open

30
SQLXML Managed Classes for .NET

• SQLXML 2.0 introduces a set of managed classes to
  expose SQLXML 2.0 functionality inside the
  Microsoft .NET Framework
• Advantages over the SQL Server native .NET data
  provider
• Can be used to execute SQLXML Template queries
• Can be used to execute Paths queries against
  mapping schemas
• Managed classes object model
• The SqlXmlCommand object
• The SqlXmlParameter object
• The SqlXmlAdapter object

31
Using the SQLXML Managed Classes Executing a FOR
XML Query

• Dim sqlCmd As New SqlXmlCommand("ProviderS
  QLOLEDB _
• Data Source.Initial
  CatalogpubsUser IdsaPassword"
• sqlCmd.CommandType SqlXmlCommandType.Sql
  
• sqlCmd.RootTag "ROOT"
• sqlCmd.ClientSideXml True
• sqlCmd.CommandText "Select from
  Authors where _
• au_fname like ? for XML NESTED"
• Dim sqlParam As SqlXmlParameter
• sqlParam sqlCmd.CreateParameter()
• sqlParam.Value "A"
• Dim strm As Stream
• strm sqlCmd.ExecuteStream
• Dim strmReader As New StreamReader(strm)
• Debug.WriteLine (strmReader.ReadToEnd())

32
Using the SQLXML Managed Classes Executing an
XPath query

• Dim sqlCmd As New SqlXmlCommand("ProviderS
  QLOLEDB _
• Data Source.Initial
  CatalogpubsUser IdsaPassword")
• sqlCmd.CommandType SqlXmlCommandType.XPa
  th
• sqlCmd.BasePath "c\sqlxmlvdirs\pubs\sch
  ema"
• sqlCmd.SchemaPath "AuthorSchema.xml"
• sqlCmd.RootTag "ROOT"
• sqlCmd.ClientSideXml True
• sqlCmd.CommandText "Author"
• Dim sqlDa As New SqlXmlAdapter(sqlCmd)
• Dim ds As New DataSet()
• sqlDa.Fill(ds)
• DataGridAuthors.DataSource ds

33
Diffgrams

• Similar in concept and usage to XML Updategrams
  (differs in syntax)
• An XML serialization format used to describe the
  original and current state of data
• Facilitates the usage of XML to insert, update,
  and delete data in a SQL Server 2000 table
• Used extensively to marshal data within an ADO
  .NET dataset across the network
• Used when the Update method of the SqlXmlAdapter
  object is invoked to post data changes effected
  on a dataset populated using its Fill method
• Can be stored as a template in a SQLXML IIS
  virtual directory and accessed using the HTTP GET
  method

34
Side-By-Side installation of SQLXML 2.0

• SQLXML 2.0 installs in side-by-side mode
• Microsoft Visual Basic applications that need to
  use the SQLXML 2.0 Virtual Directory Control Type
  library should reference Sqlvdr2.dll
• VB applications that need to use the SQLXML 2.0
  Bulk Load object model should reference
  Xblkld2.dll
• ASP and client-side script should use
  version-dependent PROG IDs to instantiate and use
  SQLXML 2.0 components
• SQLVDir.SQLVDirControl.2.0
• SQLXMLBulkLoad.SQLXMLBulkLoad.2.0
• Use the Configure IIS Support utility in the
  SQLXML 2.0 Program group to create SQLXML 2.0 IIS
  virtual directories

35
What Did We Cover Today?

• Reviewed XML features introduced in SQL Server
  2000 RTM
• Reviewed new features introduced in XML for SQL
  Server 2000 Web Release 1
• Examined the following new features introduced in
  XML for SQL Server 2000 Web Release 2 (SQLXML
  2.0)
• Enhancements to the SQLXML IIS virtual directory
  management MMC snap-in
• Client-side XML formatting
• Support for XSD mapping schemas
• The SQLXMLOLEDB provider
• SQLXML managed classes (.NET)
• DiffGrams
• Examined issues related to the side-by-side
  installation of SQLXML 2.0

36
Thank you for joining us for todays Microsoft
Support WebCast. For information about all
upcoming Support WebCasts and access to the
archived content (streaming media files,
PowerPoint slides, and transcripts), please
visit http//support.microsoft.com/webcasts/ We
sincerely appreciate your feedback. Please send
any comments or suggestions regarding the
Support WebCasts to feedback_at_microsoft.com and
include Support WebCasts in the subject line.