Using ADO to Execute XML Queries

To submit an XML query template, you need to use an ADO Command object connected to a SQL Server database using the SQLOLEDB provider. You can either create a Connection object explicitly and assign it to a Command object's ActiveConnection property, or you can simply construct an OLE DB connection string for the ActiveConnection property, for which a Connection object will be created implicitly. The following example uses an explicit connection:

 Dim conDB 'AS ADODB.Connection Dim cmdXML 'AS ADODB.Command Set conDB = CreateObject("ADODB.Connection") ' Connect to the database using Integrated Security. With conDB     .Provider = "SQLOLEDB"     .ConnectionString = "DATA SOURCE=myDBServer;" & _          "INITIAL CATALOG=Northwind;" & _         "INTEGRATED SECURITY=SSPI;"     .Open End With Set cmdXML = CreateObject("ADODB.Command") 'Assign the Connection object to the Command object. Set cmdXML.ActiveConnection = conDB 

Submitting an XML Query Using the MSSQLXML Dialect

To ensure that the SQLOLEDB provider "knows" that the submitted query is an XML template query and not a conventional Transact-SQL command, we need to specify the dialect of the command. To do this, you need to set the Dialect property of the Command object to a globally unique identifier (GUID) representing the MSSQLXML dialect. This dialect indicates to the SQLOLEDB provider that the command is an XML query document. You then simply need to assign the XML query to the CommandText property, as shown in the following code:

 'Create the query template. Dim strQry 'As String strQry = "<Invoice xmlns:sql='urn:schemas-microsoft-com:xml-sql'>" strQry = strQry & "<sql:query>" strQry = strQry & "SELECT OrderID, OrderDate FROM Orders " strQry = strQry & "WHERE OrderID = 10248 FOR XML AUTO" strQry = strQry & "</sql:query></Invoice>" 'Specify the MSSQLXML dialect. cmdXML.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}" cmdXML.CommandText = strQry 

SQL Server doesn't provide constants for the command dialect GUIDs, so you might want to declare your own constants to make your code more readable.

The SQLOLEDB provider supports three dialect GUIDs: {C8B521FB-5CF3-11CE-ADE5-00AA0044773D}, which represents a Transact-SQL query and is the default; {5D531CB2-E6Ed-11D2-B252-00C04F681B71}, which represents an XML template query; and {EC2A4293-E898-11D2-B1B7-00C04F680C56}, which represents an XPath query. The SQL Server documentation refers to the first GUID as DBGUID_DEFAULT or DBGUID_SQL, the second as DBGUID_ MSSQLXML, and the third as DBGUID_XPATH.

Finally, to receive the XML results, we need to use an ADO Stream object. This object must be opened and assigned to the Command object's Output Stream property, which is a provider-specific property (supported only by the SQLOLEDB provider) accessed through the Properties collection of the Command object. The following code shows this procedure:

 'Create Stream object for results. Dim stmXMLout 'AS ADODB.Stream Set stmXMLout = CreateObject("ADODB.Stream") 'Assign the result stream. stmXMLout.Open cmdXML.Properties("Output Stream") = stmXMLout 

Visual Basic normally requires that you use the Set keyword to assign a value to an object variable. The Output Stream property is an exception to this rule. Although a stream is an object, an error will be raised if you try to assign a value using Set.

Now that the required configuration is in place, we can execute the query. We must specify the adExecuteStream option, which has a value of 1024, to ensure that the results are returned as a stream, as shown here:

 'Execute the query. cmdXML.Execute, , adExecuteStream 

The query (or queries) in the XML template will then be executed and the resulting XML document will be written to the result stream. To process the results, simply read the contents of the result stream. In the following example, the results are displayed in a message box:

 'Process the results. Dim strXML 'AS String strXML = Replace(stmXMLout.ReadText, ">", ">" & Chr(10) + Char(13)) MsgBox strXML, vbInformation, "XML Invoice" 

For clarity, Listing 3-1 displays the full code listing. You can find the code listing in ADOQuery.vbs in the Demos\Chapter3 folder on the companion CD.

Listing 3-1.

 ADOQuery.vbs Const adExecuteStream=1024 Const MSSQLXML_DIALECT = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}" Dim conDB 'As ADODB.Connection Dim cmdXML 'As ADODB.Command Set conDB = CreateObject("ADODB.Connection") ' Connect to the database using Integrated Security. With conDB     .Provider = "SQLOLEDB"     .ConnectionString = "DATA SOURCE=(local);" & _         "INITIAL CATALOG=Northwind;" & _          "INTEGRATED SECURITY=SSPI;"     .Open End With Set cmdXML = CreateObject("ADODB.Command") 'Assign the Connection object to the Command object. Set cmdXML.ActiveConnection = conDB 'Create the query template. Dim strQry 'As String strQry = "<Invoice xmlns:sql='urn:schemas-microsoft-com:xml-sql'>" strQry = strQry & "<sql:query>" strQry = strQry & "SELECT OrderID, OrderDate FROM Orders " strQry = strQry & "WHERE OrderID = 10248 FOR XML AUTO" strQry = strQry & "</sql:query></Invoice>" 'Specify the MSSQLXML dialect and assign the query. cmdXML.Dialect = MSSQLXML_DIALECT cmdXML.CommandText = strQry 'Create Stream object for results. Dim stmXMLout 'AS ADODB.Stream Set stmXMLout = CreateObject("ADODB.Stream") 'Assign the result stream. stmXMLout.Open cmdXML.Properties("Output Stream") = stmXMLout 'Execute the query. cmdXML.Execute , , adExecuteStream 'Process the results. Dim strXML 'As String strXML = Replace(stmXMLout.ReadText, ">", ">" & Chr(10) + Char(13)) MsgBox strXML, vbInformation, "XML Invoice" 

Submitting an XML Query as a Stream Object

The procedure I just demonstrated is similar to that used to execute any other database query. However, you'll encounter some limitations using the CommandText property for XML queries. First, for very large query documents (particularly when they're read from a file), you incur a cost by reading the document and then writing it to the CommandText property. Second, the CommandText property supports only UNICODE encoding, which might not be the format of an XML template stored in a file.

An alternative approach is to use a Stream object for the inbound query. The SQLOLEDB provider supports a CommandStream property of the Command object that can be used to submit a command as a stream in a similar way that the CommandText property can be used to submit a command as a string.

To use this technique, you create a Stream object for the inbound query, as shown here:

 Dim stmXMLin 'AS ADODB.Stream 'Create Stream object for inbound query. Set stmXMLin = CreateObject("ADODB.Stream") 

Next we need to write the XML query template into the inbound stream and then reposition the current character marker in the stream to the beginning, ready to be read by the SQLOLEDB provider, as shown here:

 'Write query to inbound stream. stmXMLin.Open stmXMLin.WriteText strQry, adWriteChar 'Set Stream object position to the beginning of the stream. stmXMLin.Position = 0 

To assign the inbound stream to the Command object, we need to use the CommandStream property:

 'Assign Stream object to Command object. Set cmdXML.CommandStream = stmXMLin 

For clarity, Listing 3-2 shows the complete code listing necessary to submit a query as a stream. You can also find the code listing in ADOQueryStream.vbs in the Demos\Chapter3 folder on the companion CD.

Listing 3-2.

 ADOQueryStream.vbs Dim conDB 'AS ADODB.Connection Dim cmdXML 'AS ADODB.Command Set conDB = CreateObject("ADODB.Connection") ' Connect to the database using Integrated Security. With conDB     .Provider = "SQLOLEDB"     .ConnectionString = "DATA SOURCE=(local);" & _         "INITIAL CATALOG=Northwind;" & _           "INTEGRATED SECURITY=SSPI;"     .Open End With Set cmdXML = CreateObject("ADODB.Command") 'Assign the Connection object to the Command object. Set cmdXML.ActiveConnection = conDB 'Create Stream object for inbound query. Dim stmXMLin 'AS ADODB.Stream Set stmXMLin = CreateObject("ADODB.Stream") 'Create the query template. Dim strQry 'AS String strQry = "<Invoice xmlns:sql='urn:schemas-microsoft-com:xml-sql'>" strQry = strQry & "<sql:query> strQry = strQry & "SELECT OrderID, OrderDate FROM Orders " strQry = strQry & "WHERE OrderID = 10248 FOR XML AUTO" strQry = strQry & "</sql:query></Invoice>" 'Write query to inbound stream. stmXMLin.Open stmXMLin.WriteText strQry, adWriteChar 'Set Stream object position to the beginning of the stream. stmXMLin.Position = 0 'Assign Stream object to Command object. Set cmdXML.CommandStream = stmXMLin 'Create stream for outbound result. Dim stmXMLout 'AS ADODB.Stream Set stmXMLout = CreateObject("ADODB.Stream") 'Assign the result stream. stmXMLout.Open cmdXML.Properties("Output Stream") = stmXMLout 'Execute the query. cmdXML.Execute, , adExecuteStream 'Process the results. Dim strXML 'AS String strXML = Replace(stmXMLout.ReadText, ">", ">" & Chr(10) + Char(13)) MsgBox strXML, vbInformation, "XML Invoice" 

This approach overcomes the limitations associated with using the CommandText property for XML queries. First, using a stream to read and write large amounts of text is more efficient than using strings. Second, the CommandStream property supports not just UNICODE but any encoding format understood by the XML parser. For these reasons, using the CommandStream property is nearly always preferable to using CommandText.