Retrieving Result Sets into Streams

ADO and SQL Server

Retrieving Result Sets into Streams

In addition to receiving results in a Recordset object, an ADO application can use the Stream object to contain these results in XML format. These results also can be streamed into any object that supports the OLE DB IStream interface, (for example, the ASP Response object.

This example demonstrates using a stream to access the Northwind database. It uses an Active Server Page (ASP) and is written in Microsoft Visual Basic Scripting Edition (VBScript).

FOR XML Queries

The FOR XML clause, which allows SQL Server to return data in the form of an XML document, has been added to the SELECT statement in SQL Server 2000. The syntax of the FOR XML clause is:

FOR XML [RAW|AUTO|EXPLICIT]

FOR XML RAW generates generic row elements with column values as attributes. FOR XML AUTO uses heuristics to generate a hierarchical tree with element names based on table names. FOR XML EXPLICIT provides complete control over the format of the XML returned by the query. For more information, see Retrieving XML Data Using FOR XML.

The command can be entered in the form of:

<ROOT xmlns:sql='urn:schemas-microsoft-com:xml-sql'>
SELECT * FROM PRODUCTS ORDER BY PRODUCTNAME FOR XML AUTO

The command can also be entered in the form of a template query. When constructing a template query for use with the ADO Command object, the application must enclose the command text in <sql:query> </sql:query> tags to reference an XML Namespace specific to SQL Server queries. The command is entered in the form of:

<sql:query> SELECT * FROM PRODUCTS ORDER BY PRODUCTNAME FOR XML AUTO </sql:query>

The application must also specify where to send the output of the query. When using the FOR XML clause, the application can specifies a Stream object to receive the resulting XML output. In this example, the application uses the ASP Response object by setting the Output Stream property on the ADO Command object:

adoCmd.Properties("Output Stream") = Response

After the output stream has been associated with the Command object using the Output Stream property, the command can be executed. The application sets the adExecuteStream parameter to retrieve results in the form of a stream instead as a record set, which is the default. This example encloses the stream in XML tags that create an XML data island.

Response.write "<XML ID='MyDataIsle'>"
adoCmd.Execute , , adExecuteStream
Response.write "</XML>"

At this point in the code execution, the application has streamed XML to the client browser and to display it using client-side VBScript to bind the XML document to an instance of the Document Object Model (DOM), looping through each child node to build a list of products in HTML:

<SCRIPT language="VBScript" For="window" Event="onload">

Dim xmlDoc
Set xmlDoc = MyDataIsle.XMLDocument
xmlDoc.resolveExternals=false
xmlDoc.async=false

Dim root, child
Set root = xmlDoc.documentElement

For each child in root.childNodes
   dim OutputXML
   OutputXML = document.all("log").innerHTML
   document.all("log").innerHTML = OutputXML & "<LI>" & child.getAttribute("ProductName") & "</LI>"
Next

</SCRIPT>
</HEAD>
<BODY>
<H3>Client-side processing of XML Document MyDataIsle</H3>
<UL id=log>
</UL>
</BODY>
</HTML>

Example

This is the complete code listing from the ASP described previously. The ASP:

Queries SQL Server 2000.
Binds the resulting XML stream to the DOM.
Displays data from several nodes.

<%@ LANGUAGE = VBScript    %>
<%  Option Explicit      %>

<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Developer Studio"/>
<META HTTP-EQUIV="Content-Type" content="text/html" charset="iso-8859-1"/>
<TITLE>ADO 2.6 E</TITLE>

<!-- #include file="adovbs.inc" -->
<%
   Response.Write "<H3>Server-side processing</H3>"

       Dim adoConn
Set adoConn = Server.CreateObject("ADODB.Connection")

Dim sConn
sConn = "Provider=SQLOLEDB;Data Source=MYSERVER1;Initial Catalog=Northwind;User ID=SA;Password=;"
adoConn.ConnectionString = sConn
adoConn.CursorLocation = adUseClient
adoConn.Open

   Dim adoCmd
Set adoCmd = Server.CreateObject("ADODB.Command")
Set adoCmd.ActiveConnection = adoConn

   Dim sQuery
sQuery = "<ROOT xmlns:sql='urn:schemas-microsoft-com:xml-sql'><sql:query>SELECT * FROM PRODUCTS ORDER BY PRODUCTNAME FOR XML AUTO</sql:query></ROOT>"

   Dim adoStreamQuery
Set adoStreamQuery = Server.CreateObject("ADODB.Stream")
adoStreamQuery.Open
   adoStreamQuery.WriteText sQuery, adWriteChar
   adoStreamQuery.Position = 0

   Set adoCmd.CommandStream = adoStreamQuery
   adoCmd.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}"

   Response.write "Pushing XML to client for processing "  & "<BR/>"

   adoCmd.Properties("Output Stream") = Response
Response.write "<XML ID='MyDataIsle'>"
   adoCmd.Execute , , adExecuteStream
   Response.write "</XML>"
%>

<SCRIPT language="VBScript" For="window" Event="onload">
   
   Dim xmlDoc
   Set xmlDoc = MyDataIsle.XMLDocument
   xmlDoc.resolveExternals=false
   xmlDoc.async=false
      
   Dim root, child
   Set root = xmlDoc.documentElement

   For each child in root.childNodes
      dim OutputXML
      OutputXML = document.all("log").innerHTML
      document.all("log").innerHTML = OutputXML & "<LI>" & child.getAttribute("ProductName") & "</LI>"
   Next
   
</SCRIPT>
</HEAD>
<BODY>
   <H3>Client-side processing of XML Document MyDataIsle</H3>
   <UL id=log>
   </UL>
</BODY>
</HTML>

Retrieving Result Sets into Streams

ADO and SQL Server

Retrieving Result Sets into Streams

FOR XML Queries

Example

See Also

Get in touch