1 / 36

Programming ADO to Execute SQLXML Queries Karthik Ravindran PSS System.xml and MSXML Product Lead Microsoft Corpora

2. Audience Prerequisites. Working knowledge of the following:Programming the Microsoft

sandra_john
Download Presentation

Programming ADO to Execute SQLXML Queries Karthik Ravindran PSS System.xml and MSXML Product Lead Microsoft Corpora

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

    1. Programming ADO to Execute SQLXML Queries Karthik Ravindran PSS 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

More Related