Programming Stream Objects

Programming Stream Objects

For Access programmers, Stream objects primarily are of interest for returning attribute-centric XML data documents from SQL Server 2000. The T-SQL statement for the query must terminate with the FOR XML AUTO or FOR XML RAW option. Both options return a well-formed XML document using Microsoft's xml-sql schema. Unlike the .xml files saved from Recordset objects with the adPersistXML option, the stream doesn't include the schema elements. Like the rowset schema, xml-sql isn't compatible with Access 2003's native XML schema. SQL Server HTTP template queries, which can return HTML tables to Web browsers from FOR XML AUTO queries, require the xml-sql schema.

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.