Programming ADO to Execute SQLXML Queries Karthik Ravindran PSS System'xml and MSXML Product Lead Mi

1
Programming ADO to Execute SQLXML Queries
Karthik RavindranPSS System.xml and MSXML
Product Lead Microsoft Corporation
2
Audience Prerequisites

• Working knowledge of the following
• Programming the Microsoft ActiveX Data Objects
  (ADO) model to access relational databases
• Using TSQL to access data stored in Microsoft SQL
  Server databases
• XML and its related standards (XPath, XML
  schemas, XSLT, and parser concepts)

3
Audience Prerequisites (2)

• Working knowledge of the following
• Base XML features introduced in SQL Server 2000
  RTM
• Configuring and using SQLXML IIS virtual
  directories
• Types of SQLXML queries
• XML Updategrams (introduced in SQLXML 2.0)
• ASP
• Microsoft Visual Basic 6.0

4
Agenda

• Introduce the ADO extensions for executing SQLXML
  queries
• Understand the architecture of executing SQLXML
  queries using the SQLOLEDB provider
• Understand the architecture of executing SQLXML
  queries using the SQLXMLOLEDB provider
• Understand the differences between the SQLOLEDB
  and the SQLXMLOLEDB providers
• Examine SQLXML-specific dynamic properties of the
  ADO Command and Connection objects
• Examine the process of using ADO to execute
  common types of SQLXML queries

5
Overview of the ADO Extensions for SQLXML

• Used to execute SQLXML queries in ADO
  applications without using the following SQLXML
  components
• SQLXML IIS virtual directories
• The SQLXML ISAPI filter
• Common application categories
• VB/VC EXEs
• VB/VC DLLs
• ASP applications
• Require MDAC 2.6 or later (implemented initially
  in ADO 2.6)
• Implemented in the ADO Connection and Command
  objects as new properties and dynamic properties
  (provider-specific)

6
Types of SQLXML Queries that Can Be Executed
Using the ADO SQLXML Extensions

• FOR XML AUTO SELECT queries
• SQLXML Template queries
• XPath queries against annotated mapping schemas
  (XSD schemas require SQLXML 2.0 or later)
• Updategrams (requires SQLXML 2.0 or later)
• XSLT transformations on the XML generated by
  SQLXML queries

7
Why Would You Want to Use These Extensions?

• Must access SQL Server 2000 data as XML in client
  applications without executing HTTP requests to
  access a SQLXML IIS virtual directory
• Must access SQL Server 2000 data as XML in
  middle-tier components without executing HTTP
  requests to access a SQLXML IIS virtual directory
• Must access SQL 2000 data as XML in Web
  applications without configuring SQLXML IIS
  virtual directories
• Avoid using the default ADO XML persistence
  format when accessing SQL 2000 data as XML

8
OLEDB Providers to Execute SQLXML Queries

• SQLOLEDB
• SQLXMLOLEDB

9
The SQLOLEDB Provider

• Recommended OLEDB data provider to connect to SQL
  Server databases (Sqloledb.dll)
• Relies on Sqlxmlx.dll (Sqlxmlx.dll/Sqlxml2.dll/Sql
  xml3.dll) to translate SQLXML queries to TSQL
  statements
• Can be used to execute
• FOR XML SELECT queries
• SQLXML Template queries
• XPath queries against annotated mapping schemas
• Updategrams
• XSLT transformations on the XML generated by
  SQLXML queries
• Only supports server-side XML formatting

10
The SQLXMLOLEDB Provider

• Introduced in SQLXML 2.0
• Not a data provider it uses SQLOLEDB as the
  underlying data provider
• Not a rowset provider can execute commands only
  in the Write to output stream mode
• Can be used to run all the query types listed for
  the SQLOLEDB provider
• Additional features are not available when using
  SQLOLEDB
• Client-side XML formatting
• XPath queries against mapping XSD schemas that
  define a target namespace

11
ADO SQLXML Extensions

• The Connection object
• Dynamic property SQLXML Version (SQLOLEDB only)
• The Command object
• Properties
• CommandStream
• Dialect
• Dynamic properties (provider-specific)
• XML Root
• Output Stream
• Base Path
• Mapping Schema
• XSL
• ClientSideXml (SQLXMLOLEDB only)
• Namespaces (SQLXMLOLEDB only)

12
Architecture of Executing SQLXML Queries Using a
SQLXML 3.0 IIS Virtual Directory
Backend SQL Server
Middle Tier IIS Server
Client (InternetExplorer) SQLXML HTTP Request
TSQL Statement(s)
SQLOLEDB (Sgloledb.dll)
Rowset (Client- side XML formatting) XML
(Server-side formatting)
SQLISAPI (Sqlis3.dll)
SQL Server 2000
IIS
TSQL
Rowset/XML
SQLXML query
Data as XML
SQLXML (Sqlxml3.dll)
FOR XML
OPENXML
XML
Msxml4.dll
PUBS
XSL
Templates
Schemas
13
Architecture of Executing SQLXML Queries Using
the SQLOLEDB Provider
ADOApp.exe
SQLXML commands
TSQL commands
ADO CODE
SQLOLEDB (Sqloledb.dll)
cn.Open .. .. cmd.Execute . . cn.Close
Result stream
SQL Server 2000
FOR XML
Output XML stream
SQLXML commands
TSQL Stmts
SQLXML (Sqlxml3.dll)
Sqlxmlx.dll Sqlxml2.dll Sqlxml3.dll
Databases
Msxml4.dll
XSL
Templates
Schemas
SQLXML Version dynamic property of the ADO
Connection object
14
Architecture of Executing SQLXML Queries Using
the SQLXMLOLEDB Provider
XSL
Templates
Schemas
ADOApp.exe
SQLXML commands
Rowset / XML stream
ADO CODE
cn.Open .. .. cmd.Execute . . cn.Close
Result XML stream
SQLXMLOLEDB (Sqlxml3.dll)
TSQL commands
TSQL Commands
SQL Server 2000
SQLOLEDB (Sqloledb.dll)
Msxml4.dll
FOR XML
Output (Rowset / XML stream)
Databases
15
Architecture of Executing SQLXML Queries with
Server-Side XML Formatting Using the SQLXMLOLEDB
Provider
16
Architecture of Executing SQLXML Queries with
Client-Side XML Formatting Using the SQLXMLOLEDB
Provider
17
Common Steps in Executing SQLXML Queries Using ADO
18
Step 1 Create an Instance of and Open an ADO
Connection
Set cn New ADODB.Connection cn.Open
"ltConnection Stringgt"

• Sample SQLOLEDB connection string
• "ProviderSQLOLEDB Data source(local) Initial
  catalogNorthwind" _
• "user idsa Password"
• Sample SQLXMLOLEDB connection string
• "ProviderSQLXMLOLEDB.3.0Data ProviderSQLOLEDB
  " _
• " Data source(local) Initial catalogNorthwind
  User idsa Password"

19
Step 2 Optionally Set the SQLXML Version Dynamic
Property

• cn.Properties("SQLXML Version") "SQLXML.3.0"

• SQLOLEDB uses Sqlxmlx.dll (SQLXML 1.0) by default
• Set the SQLXML Version dynamic property of the
  ADO Connection object if
• SQLOLEDB is used as the primary provider
• and
• The query requires SQLXML 2.0 or later
• Templates that reference annotated mapping XSD
  schemas
• XPath queries against annotated mapping XSD
  schemas
• Updategrams

20
Step 3 Create an Instance of an ADO Command
Object

• Create an instance of the Command object and
  associate it with the connection
• Set cmd New ADODB.Command
• Set cmd.ActiveConnection cn

21
Step 4 Specify the Command Dialect

• Set the Dialect property to specify the type of
  SQLXML query
• cmd.Dialect ltDIALECT GUIDgt
• Dialect GUIDs
• C8B521FB-5CF3-11CE-ADE5-00AA0044773D -
  DBGUID_DEFAULT
• 5D531CB2-E6Ed-11D2-B252-00C04F681B71 -
  DBGUID_MSSQLXML
• ec2a4293-e898-11d2-b1b7-00c04f680c56
  DBGUID_XPATH

22
Step 5 Specify the Query to Execute

• Use the CommandText or the CommandStream
  properties of the Command object to specify the
  SQLXML query to execute
• cmd.CommandText "ltFOR XML AUTO Querygt"
• or
• Set cmd.CommandStream ltIStream objectgt

23
Step 6 Specify Stream Object to Which the Query
Results Must Be Generated

• Set the Output Stream dynamic property of the
  Command object
• Frequently used Stream objects
• ADODB.Stream
• The ASP Response object
• The MSXML DOMDocument object
• Set ResultStream New ADODB.Stream
• ResultStream.Open
• cmd.Properties("Output Stream").Value
  ResultStream

24
Step 7 Set Other SQLXML Extension Dynamic
Properties as Required

• Example
• cmd.Properties("Base Path").Value App.Path
• cmd.Properties("Mapping Schema").Value
  "Employees.xsd"
• cmd.Properties("xsl").Value "Employees.xsl"
• cmd.Properties("Output Encoding") "utf-8"

25
Step 8 Execute the Command and Access the Results

• cmd.Execute , , adExecuteStream
• ' ResultStream here is an ADODB.Stream object
• ResultStream.Position 0
• Debug.Print ResultStream.ReadText(adReadAll)
• ' Code to close and release Connection, Command
  and
• ' Stream objects

26
Executing a FOR XML AUTO Query

• ' Code to establish connection (SQLOLEDB or
  SQLXMLOLEDB)
• ' and create an instance of the Command object
• cmd.CommandText "SELECT FROM Customers FOR
  XML AUTO"
• cmd.Dialect DBGUID_DEFAULT
• Set ResultStream New ADODB.Stream
• ResultStream.Open
• cmd.Properties("Output Stream").Value
  ResultStream
• cmd.Properties("xml root") "root"
• cmd.Execute , , adExecuteStream
• ' Code to access query result

27
Executing a FOR XML NESTED Query

• ' Code to establish connection (SQLXMLOLEDB)
• ' and create an instance of the Command object
• cmd.CommandText "SELECT FROM Customers FOR
  XML NESTED"
• cmd.Dialect DBGUID_DEFAULT
• Set ResultStream New ADODB.Stream
• ResultStream.Open
• cmd.Properties("Output Stream").Value
  ResultStream
• cmd.Properties("xml root") "root"
• cmd.Properties("ClientSideXML") True
• cmd.Execute , , adExecuteStream
• ' Code to access query result

28
Execute a SQLXML Template

• GetEmployees.xml
• ltROOT xmlnssql'urnschemas-microsoft-comxml-sql
  'gt
• ltsqlquerygt
• SELECT EmployeeID, FirstName, LastName
• FROM Employees FOR XML AUTO
• lt/sqlquerygt
• lt/ROOTgt

29
Execute a SQLXML Template (2)

• ' Code to establish connection and create an
  instance of the Command object
• Set TemplateStream New ADODB.Stream
• TemplateStream.Open
• TemplateStream.Charset "utf-8"
• TemplateStream.LoadFromFile App.Path
  "\GetEmployees.xml"
• Set cmd.CommandStream TemplateStream
• cmd.Dialect DBGUID_MSSQLXML
• Set ResultStream New ADODB.Stream
• ResultStream.Open
• cmd.Properties("Output Stream").Value
  ResultStream
• cmd.Properties("Base Path").Value App.Path
• cmd.Properties("xsl").Value "Employees.xsl"
• cmd.Execute , , adExecuteStream

30
Execute an XPath Query Against a Mapping Schema

• ltxsdschema xmlnsxsd'http//www.w3.org/2001/XMLS
  chema'
• xmlnssql'urnschemas-microsoft-commapping-sc
  hema'gt
• ltxsdelement name 'root' sqlis-constant'1'gt
• ltxsdcomplexTypegt
• ltxsdsequencegt
• ltxsdelement ref 'Employee'/gt
• lt/xsdsequencegt
• lt/xsdcomplexTypegt
• lt/xsdelementgt
• ltxsdelement name'Employee' sqlrelation'Emplo
  yees'gt
• ltxsdcomplexTypegt
• ltxsdattribute name'EmployeeID'
  type'xsdinteger' /gt
• ltxsdattribute name'FirstName'
  type'xsdstring'/gt
• ltxsdattribute name'LastName'
  type'xsdstring' /gt
• lt/xsdcomplexTypegt
• lt/xsdelementgt
• lt/xsdschemagt

31
Execute an XPath Query Against a Mapping Schema
(2)

• ' Code to establish connection (SQLXMLOLEDB
  provider)
• ' and create an instance of the Command object
• Dim ResultDoc As MSXML2.DOMDocument40
• Set ResultDoc New MSXML2.DOMDocument40
• cmd.CommandText "Employee_at_FirstName'Andrew'"
• cmd.Dialect DBGUID_XPATH
• cmd.Properties("ClientSideXML") True
• cmd.Properties("xml root") "root"
• cmd.Properties("Output Stream").Value ResultDoc
• cmd.Properties("Base Path").Value App.Path
• cmd.Properties("Mapping Schema").Value
  "ESchema.xsd"
• cmd.Properties("Output Encoding") "utf-8"
• cmd.Execute , , adExecuteStream
• ' Code to access query result

32
Execute an Updategram

• ltROOT xmlnsupdg'urnschemas-microsoft-comxml-up
  dategram'gt
• ltupdgsync gt
• ltupdgbeforegt
• ltEmployees EmployeeID'1' /gt
• lt/updgbeforegt
• ltupdgaftergt
• ltEmployees LastName'Davolio' /gt
• lt/updgaftergt
• lt/updgsyncgt
• lt/ROOTgt

33
Execute an Updategram (2)

• cn.Open " ProviderSQLOLEDB.. "
• cn.Properties("SQLXML Version") "SQLXML.3.0"
• Set cmd New ADODB.Command
• Set cmd.ActiveConnection cn
• Set TemplateStream New ADODB.Stream
• TemplateStream.Open
• TemplateStream.Charset "utf-8"
• TemplateStream.LoadFromFile App.Path
  "\UpdateEmployee.xml"
• Set cmd.CommandStream TemplateStream
• Set ResultStream New ADODB.Stream
• ResultStream.Open
• cmd.Properties("Output Stream").Value
  ResultStream
• cmd.Dialect DBGUID_MSSQLXML
• cmd.Execute , , adExecuteStream

34
ASP Sample Transforming Query Results

• lt
• Set cn CreateObject("ADODB.Connection")
• cn.Open ltConnection Stringgt
• Set cmd CreateObject("ADODB.Command")
• Set cmd.ActiveConnection cn
• Set TemplateStream CreateObject("ADODB.Stream")
• TemplateStream.Open
• TemplateStream.Charset "utf-8"
• TemplateStream.LoadFromFile Server.MapPath("GetEmp
  loyees.xml")
• Set cmd.CommandStream TemplateStream
• cmd.Properties("Output Stream").Value Response
• cmd.Dialect "5d531cb2-e6ed-11d2-b252-00c04f681b
  71"
• cmd.Properties("xsl").Value Server.MapPath("Empl
  oyees.xsl")
• cmd.Execute , , 1024
• gt

35
What Did We Cover Today?

• Introduced the ADO extensions for executing
  SQLXML queries
• Examined the architecture of executing SQLXML
  queries using the SQLOLEDB provider
• Examined the architecture of executing SQLXML
  queries using the SQLXMLOLEDB provider
• Examined the differences between the SQLOLEDB and
  the SQLXMLOLEDB providers
• Examined SQLXML specific dynamic properties of
  the ADO Command and Connection objects
• Examined the process of using ADO to execute
  common types of SQLXML queries

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 supweb_at_microsoft.com.