Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. How To Retrieve XML Data by Using a SQL XML Query in an ASP Client

How To Retrieve XML Data by Using a SQL XML Query in an ASP Client

View products that this article applies to.

Summary

When you run an ADODB command stream and specify SQL SELECT with the FOR XML AUTO clause, an XML document stream is fetched from SQL Server, sent to the response object, and piped to the client.

Properties to be set for the properties of the ADODB.Command collection Output Stream and Dialect are as follows:
  • Output Stream: Designates where the resulting XML data stream will be piped.
  • Dialect: Defines the syntax and general rules that the provider uses to parse the string or stream. Setting the command language dialect specifies how the Microsoft OLE DB Provider for SQL Server interprets the command text that is received from ADO. The dialect is specified by a globally uniqe identifier (GUID) and is set using the Dialect property of the Command object.

↑ Back to the top

More information

Create a file called TestXMLStream.ASP, paste in the following code, and make sure that there is script source access in TestXMLStream Internet Information Service (IIS) properties:

Note You must change uid=<user name> and pwd=<strong password> to the correct values before you run this code. Make sure that the uid has the appropriate permissions to perform this operation on the database.

<%@ Language=VBScript %>

<!--#include file="adovbs.inc" -->
<%

    Dim adoCmd 'As ADODB.Command
    Dim adoConn        '   As ADODB.Connection    
    Dim adoStreamQuery '   As ADODB.Stream
    Dim outStrm        '   As ADODB.Stream
    Dim txtResults     '   String for results
    dim sConn          '   String for connection
    dim CmdStream      '   as ADODB.Stream
    
    sConn = "Provider=SQLOLEDB;Data Source=.;Initial Catalog=Northwind;uid=<user name>;pwd=<strong password>"
    Set adoConn = CreateObject("ADODB.Connection")    
    Set adoStreamQuery = CreateObject("ADODB.Stream")
    
    adoConn.ConnectionString = sConn
    adoConn.Open
    
    Set adoCmd = CreateObject("ADODB.Command")  
    set adoCmd.ActiveConnection = adoConn    
    
    adoConn.CursorLocation =  adUseClient
    
    Set adoCmd.ActiveConnection = adoConn
      sQuery = "<ROOT xmlns:sql='urn:schemas-microsoft-com:xml-sql'>"
      sQuery = sQuery & "<sql:query>SELECT * FROM PRODUCTS FOR XML AUTO</sql:query>"
      sQuery = sQuery & "</ROOT>"
             
    adoStreamQuery.Open                             '   Open the command stream so it may be written to
    adoStreamQuery.WriteText sQuery,  adWriteChar   '   Set the input command stream's text with the query string
    adoStreamQuery.Position = 0                     '   Reset the position in the stream, otherwise it will be at EOS
    
    Set adoCmd.CommandStream = adoStreamQuery       '   Set the command object's command to the input stream set above
    adoCmd.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}"   ' Set the dialect for the command stream to be a SQL query.
    Set outStrm = CreateObject("ADODB.Stream")      '   Create the output stream
    outStrm.Open
    adoCmd.Properties("Output Stream") = response   '   Set command's output stream to the output stream just opened    
    adoCmd.Execute , , adExecuteStream              '   Execute the command, thus filling up the output stream.

%>

↑ Back to the top

References

For SQL Server 2000 and schema, see SQL Server Books Online

For information on XML, see the following Microsoft Web site:
http://msdn.microsoft.com/xml

↑ Back to the top

Keywords: KB272269, kbhowto

↑ Back to the top

Article Info
Article ID : 272269
Revision : 8
Created on : 7/13/2004
Published on : 7/13/2004
Exists online : False
Views : 407