Share via

Retrieving Resultsets into Streams

  • Article

Instead of receiving results in the traditional Recordset object, ADO can instead retrieve query results into a stream. The ADO Stream object (or other objects that support the COM IStream interface, such as the ASP Request and Response objects) can be used to contain these results. One use for this feature is to retrieve results in XML format. With SQL Server 2000, for example, XML results can be returned in multiple ways, such as using the FOR XML clause with a SQL SELECT query or using an XPath query.

To receive query results in stream format rather than in a Recordset, you must specify the adExecuteStream constant from ExecuteOptionEnum as a parameter of the Execute method of a Command object. If your provider supports this feature, the results will be returned in a stream upon execution. You might be required to specify additional provider-specific properties before the code executes. For example, with the Microsoft OLE DB Provider for SQL Server, properties such as Output Stream in the Properties collection of the Command object must be specified. For more information about SQL Server-specific dynamic properties related to this feature, see XML-Related Properties in the SQL Server Books Online.

FOR XML Query Example

The following example is written in VBScript to the SQL Server 2000 Northwind database:

<!-- BeginRecordAndStreamVBS -->
<%@ 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>FOR XML Query Example</TITLE>

<STYLE>
   BODY
   {
      FONT-FAMILY: Tahoma;
      FONT-SIZE: 8pt;
      OVERFLOW: auto
   }

   H3
   {
      FONT-FAMILY: Tahoma;
      FONT-SIZE: 8pt;
      OVERFLOW: auto
   }
</STYLE>

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

   Response.Write "Page Generated @ " & Now() & "<BR/>"

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

   Dim sConn
   sConn = "Provider=SQLOLEDB;Data Source=" & _
      Request.ServerVariables("SERVER_NAME") &amp; ";" & _
      Initial Catalog=Northwind;Integrated Security=SSPI;"

   Response.write "Connect String = " & sConn & "<BR/>"

   adoConn.ConnectionString = sConn
   adoConn.CursorLocation = adUseClient

   adoConn.Open

   Response.write "ADO Version = " & adoConn.Version & "<BR/>"
   Response.write "adoConn.State = " & adoConn.State & "<BR/>"

   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 WHERE ProductName='Gumbär Gummibärchen' FOR XML AUTO</sql:query></ROOT>"

   Response.write "Query String = " & sQuery & "<BR/>"

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

   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 , , 1024
   Response.write "</XML>"

%>

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

   If xmlDoc.parseError.Reason <> "" then
      Msgbox "parseError.Reason = " & xmlDoc.parseError.Reason
   End If
            
   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>
<!-- EndRecordAndStreamVBS -->

A new FOR XML clause has been added to the SELECT statement in SQL Server 2000. The FOR XML clause instructs SQL Server to return data in the form of an XML document. For more information about SQL Server FOR XML queries, see Retrieving XML Data Using FOR XML in the SQL Server Books Online.

FOR XML Syntax

FOR XML [RAW|AUTO|EXPLICIT]

FOR XML RAW generates generic row elements with column values as attributes. FOR XML AUTO uses heuristics to generate an hierarchical tree with element names based on table names. FOR XML EXPLICIT generates a universal table with relationships fully described by metadata.

An example SQL SELECT FOR XML statement follows:

SELECT * FROM PRODUCTS ORDER BY PRODUCTNAME FOR XML AUTO

The command may be specified in a string as shown above, assigned to CommandText, or in the form of an XML template query assigned to CommandStream. For more information about XML template queries, see Command Streams in ADO or Using Streams for Command Input in the SQL Server Books Online.

As an XML template query, the FOR XML query appears as follows:

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

This example specifies the ASP Response object for the Output Stream property:

adoCmd.Properties("Output Stream") = Response

Next, specify adExecuteStream parameter of Execute. This example wraps the stream in XML tags to create an XML data island:

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

At this point, XML has been streamed to the client browser and it is ready to be displayed. This is done using client side VBScript to bind the XML document to an instance of the DOM and looping through each child node to build a list of Products in HTML.