Special Edition Using Microsoft Office Access 2003

Programming Stream Objects

For an example of using the FOR XML AUTO option in SQL Server HTTP template queries, see "Using SQL Server 2000's HTTP Query Features," p. 976.

Executing FOR XML AUTO Queries with the frmStream Form

The frmStream form has unbound text boxes to display a default T-SQL FOR XML AUTO query, the modifications to the query syntax needed to return a well-formed XML document, and the XML document resulting from execution of the Command object that specifies MSSQLXML as the query dialect. To test the frmStream form, do this:

1. Open ADOTest.mdb's or ADOTest.adp's frmStream form. The default query is a simple T-SQL query, similar to that used by the frmBatchUpdate form, with the FOR XML AUTO modifier added. SQL Server's default rowset document style is attribute-centric. Mark the Element-Centric check box to add ELEMENTS to the modifier and return an element-centric document.

2. Click the Execute FOR XML Query button to display the XML query wrapper required by SQL Server 2000 to return a well-formed XML data document. A Command object returns a Stream object that contains an XML data document, which opens in the bottom text box. The Stream object is saved to Stream.xml in the folder that contains ADOTest.mdb.

3. Click the Open Stream.xml in IE 5+ button to launch IE with file:// path/Stream.xml as the URL. IE's XML parser makes it easier to read the XML document.

4. Mark the Multi-Table Query check box to replace the simple query with a T-SQL query against the Customers and Orders tables. Making a change to the T-SQL FOR XML Query text box clears the other two text boxes.

5. Click Execute FOR XML Query again to display the resulting XML document (see Figure 30.22).

   Figure 30.22. The multi-table query with the FOR XML AUTO, ELEMENTS option returns elements from the Orders table nested within Customers table elements.

6. Click Open Stream.xml in IE 5+. The nesting of Orders elements within the Customers is more evident in IE's presentation (see Figure 30.23).

   Figure 30.23. IE 5+'s XML parser formats the document to make nesting of table elements readily apparent.

7. To see the effect of the FOR XML RAW modifier, replace AUTO with RAW in the T-SQL query, execute the command, and open the query in IE 5+ (see Figure 30.24).

   Figure 30.24. The FOR XML RAW modifier combines all attribute values for a query row in a single, generic row element.

Note

Changing the ORDER BY clause from Customers.CustomerID to Orders.OrderID generates a very different XML document strcture. In this case, most Customers elements contain a single nested order; only consecutive orders for a particular customer appear as multiple nested order elements. (See the entry for ROMEY as the first example.)

Exploring the VBA Code to Create a Stream Object

Most of the event handlers and subprocedures used by the VBA code for the frmStream form derive from those of the frmBatch form described earlier. The two important code elements behind frmStream are the Declarations section, which declares the ADODB.Command and ADODB.Stream object variables, and constants for the currently allowable GUID values of the Command.Dialect property, and the cmdExecute_Click event handler (see Listing 30.10).

Listing 30.10 Creating a Stream Object from an SQL Server FOR XML AUTO Query and Displaying the Stream in a Text Box

Option Compare Database Option Explicit Private cnnStream As New ADODB.Connection Private cmmStream As New ADODB.Command Private stmQuery As ADODB.Stream 'GUID constants for Stream.Dialect Private Const DBGUID_DEFAULT As String = _ "{C8B521FB-5CF3-11CE-ADE5-00AA0044773D}" Private Const DBGUID_SQL As String = _ "{C8B522D7-5CF3-11CE-ADE5-00AA0044773D}" Private Const DBGUID_MSSQLXML As String = _ "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}" Private Const DBGUID_XPATH As String = _ "{ec2a4293-e898-11d2-b1b7-00c04f680c56}" 'Constants for XML query prefix and suffix Private Const strXML_SQLPrefix As String = _ "<ROOT xmlns:sql='urn:schemas-microsoft-com:xml-sql'>" & vbCrLf & "<sql:query>" Private Const strXML_SQLSuffix As String = "</sql:query>" & vbCrLf & "</ROOT>" Private Sub cmdExecute_Click() 'Use Command and Stream objects to return XML as text Dim strXMLQuery As String Dim strXML As String DimlngCtr As Long On Error GoTo errGetXMLStream strXMLQuery = Me.txtQuery.Value 'Add the XML namespace and <ROOT...> and </ROOT> tags to the query text strXMLQuery = strXML_SQLPrefix & vbCrLf & strXMLQuery & vbCrLf & strXML_SQLSuffix 'Display the CommandText property value Me.txtXMLQuery.Value = strXMLQuery DoEvents 'Create a new Stream for each execution Set stmQuery = New ADODB.Stream stmQuery.Open 'Set and execute the command to return a stream With cmmStream Set .ActiveConnection = cnnStream 'Query text is used here, not an input stream .CommandText = strXMLQuery 'Specify an SQL Server FOR XML query .Dialect = DBGUID_MSSQLXML 'Specify the stream to receive the output .Properties("Output Stream") = stmQuery .Execute , , adExecuteStream End With 'Reset the stream position stmQuery.Position = 0 'Save the stream to a local file stmQuery.SaveToFile CurrentProject.Path & "\Stream.xml", adSaveCreateOverWrite cmdOpenXML.Enabled = True 'Extract the text from the stream strXML = stmQuery.ReadText 'Make the XML more readable with line feeds, if it isn't too long If Len(strXML) < 15000 Then Me.txtXML.Value = Replace(strXML, "><", ">" & vbCrLf & "<") Else If Len(strXML) > 32000 Then 'Limit the display to capacity of text box Me.txtXML.Value = Left$(strXML, 30000) Else Me.txtXML.Value = strXML End If End If Exit Sub errGetXMLStream: MsgBox Err.Description, vbOKOnly + vbExclamation, "Error Returning XML Stream" Exit Sub End Sub

This form only uses the DBGUID_MSSQLXML constant; the other three GUID constants are for reference only. ADO 2.6+'s type library doesn't have a "DialectGUIDEnum" or similar enumeration, so you must declare at least the DBGUID_MSSQLXML constant to request SQL Server to return XML data documents in the xml-sql dialect. Comments in the body of the code of the cmdExecute_Click event handler describe the purpose of each Stream-related statement.