360 likes | 866 Views
2. Audience Prerequisites. Working knowledge of the following:Programming the Microsoft
E N D
1. Programming ADO to Execute SQLXML Queries Karthik RavindranPSS System.xml and MSXML Product Lead Microsoft Corporation
2. 2
3. 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. 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. 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. 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. 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. 8 OLEDB Providers to Execute SQLXML Queries SQLOLEDB
SQLXMLOLEDB
9. 9 The SQLOLEDB Provider Recommended OLEDB data provider to connect to SQL Server databases (Sqloledb.dll)
Relies on Sqlxmlx.dll (Sqlxmlx.dll/Sqlxml2.dll/Sqlxml3.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. 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. 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. 12 Architecture of Executing SQLXML Queries Using a SQLXML 3.0 IIS Virtual Directory
13. 13 Architecture of Executing SQLXML Queries Using the SQLOLEDB Provider
14. 14 Architecture of Executing SQLXML Queries Using the SQLXMLOLEDB Provider
15. 15 Architecture of Executing SQLXML Queries with Server-Side XML Formatting Using the SQLXMLOLEDB Provider
16. 16 Architecture of Executing SQLXML Queries with Client-Side XML Formatting Using the SQLXMLOLEDB Provider
17. 17 Common Steps in Executing SQLXML Queries Using ADO
18. 18 Step 1: Create an Instance of and Open an ADO Connection
Sample SQLOLEDB connection string
"Provider=SQLOLEDB; Data source=(local); Initial catalog=Northwind;" & _
"user id=sa; Password=;"
Sample SQLXMLOLEDB connection string
"Provider=SQLXMLOLEDB.3.0;Data Provider=SQLOLEDB; " & _
" Data source=(local); Initial catalog=Northwind; User id=sa; Password=;"
19. 19 Step 2: Optionally Set the SQLXML Version Dynamic Property cn.Properties("SQLXML Version") = "SQLXML.3.0"
20. 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. 21 Step 4: Specify the Command Dialect Set the Dialect property to specify the type of SQLXML query
cmd.Dialect = <DIALECT GUID>
Dialect GUIDs
{C8B521FB-5CF3-11CE-ADE5-00AA0044773D} - DBGUID_DEFAULT
{5D531CB2-E6Ed-11D2-B252-00C04F681B71} - DBGUID_MSSQLXML
{ec2a4293-e898-11d2-b1b7-00c04f680c56} – DBGUID_XPATH
22. 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 = "<FOR XML AUTO Query>"
or
Set cmd.CommandStream = <IStream object>
23. 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. 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. 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. 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. 27 Executing a FOR XML NESTED Query
28. 28 Execute a SQLXML Template GetEmployees.xml
<ROOT xmlns:sql='urn:schemas-microsoft-com:xml-sql'>
<sql:query>
SELECT EmployeeID, FirstName, LastName
FROM Employees FOR XML AUTO
</sql:query>
</ROOT>
29. 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. 30 Execute an XPath Query Against a Mapping Schema <xsd:schema xmlns:xsd='http://www.w3.org/2001/XMLSchema'
xmlns:sql='urn:schemas-microsoft-com:mapping-schema'>
<xsd:element name= 'root' sql:is-constant='1'>
<xsd:complexType>
<xsd:sequence>
<xsd:element ref = 'Employee'/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name='Employee' sql:relation='Employees'>
<xsd:complexType>
<xsd:attribute name='EmployeeID' type='xsd:integer' />
<xsd:attribute name='FirstName' type='xsd:string'/>
<xsd:attribute name='LastName' type='xsd:string' />
</xsd:complexType>
</xsd:element>
</xsd:schema>
31. 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[@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. 32 Execute an Updategram <ROOT xmlns:updg='urn:schemas-microsoft-com:xml-updategram'>
<updg:sync >
<updg:before>
<Employees EmployeeID='1' />
</updg:before>
<updg:after>
<Employees LastName='Davolio' />
</updg:after>
</updg:sync>
</ROOT>
33. 33 Execute an Updategram (2) cn.Open " Provider=SQLOLEDB….. "
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. 34 ASP Sample – Transforming Query Results <%
Set cn = CreateObject("ADODB.Connection")
cn.Open “<Connection String>”
Set cmd = CreateObject("ADODB.Command")
Set cmd.ActiveConnection = cn
Set TemplateStream = CreateObject("ADODB.Stream")
TemplateStream.Open
TemplateStream.Charset = "utf-8"
TemplateStream.LoadFromFile Server.MapPath("GetEmployees.xml")
Set cmd.CommandStream = TemplateStream
cmd.Properties("Output Stream").Value = Response
cmd.Dialect = "{5d531cb2-e6ed-11d2-b252-00c04f681b71}"
cmd.Properties("xsl").Value = Server.MapPath("Employees.xsl")
cmd.Execute , , 1024
%>
35. 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