How To Retrieve XML Data by Using a Template File in an ASP Client (272266)

SUMMARY

The following sample demonstrates how to run a SQL 2000 template file while specifying an XSL file to transform the XML data. More specifically, a query is issued against SQL Server 2000 by using the command stream of ADO 2.6: This query fetches the XML datastream.

This sample reads the template file products.xml from disk and sets the command text for the ADODB.Command for the query to run. Properties that are set for the Properties collection of the ADODB.Command include Base Path, Output Stream, Dialect, and XSL:

Base Path establishes the location where the template and the XSL files reside.
Output Stream designates where the resulting XML data stream will be piped.
The Dialect defines the syntax and the general rules that the provider uses to parse the string or the stream. When you set the command language dialect, this specifies how the Microsoft OLE DB provider for Microsoft SQL Server interprets the command text that is received from ADO. The dialect is specified by a globally unique identifier (GUID) and is set by using the Dialect property of the Command object.
You use the XSLT file name and the location to process the output XML stream.

NOTE: The Output Stream may be any object that supports an IStream or ISequentialStream interface. Objects that support the IStream interfaces are the ADODB.Stream, IIS5 Response object, and an MSXML DomDocument.

Finally, the sample also sets the XSL setting in order to transform the XML document.

MORE INFORMATION

Create a file called TestTemplate.asp, and then paste in the following code:

<%@ Language=VBScript %>
<!--#include file="adovbs.inc" -->
<%
    Dim cn                  '   As New ADODB.Connection
    Dim cmd                 '   As New ADODB.Command
    Dim cmdStream           '   As New ADODB.Stream
    Dim cmdOutput           '   As New ADODB.Stream
    Dim txtOutputFileName   '   As String
    dim txtTemp             '   utility string
    
    set cn = CreateObject("ADODB.Connection")
    set cmd = CreateObject("ADODB.Command")
    set cmdStream = CreateObject("ADODB.Stream")
    set cmdOutput = CreateObject("ADODB.Stream") 
    
    ' open the database connection
    cn.Open "provider=sqloledb;data source=.;initial catalog=northwind;uid=sa;pwd="
    
    ' open the command stream, which contains the templated query
    cmdStream.Open
    ' set the character set to ASCII
    cmdStream.Charset = "ascii"
    ' set the command stream type to text, not binary
    cmdStream.Type = 1      'adTypeText
    ' read the template file from disk into the command stream to run
    txtTemp  = server.MapPath(".") & "\products.xml"
    
    cmdStream.LoadFromFile txtTemp      ' App.Path & "\products.xml"
    ' set the command connection 
    Set cmd.ActiveConnection = cn
    ' set the command's command stream, thereby hooking the template query 
    ' to the command you want to execute.
    Set cmd.CommandStream = cmdStream
    ' set the command dialect
    cmd.Dialect = "{C8B521FB-5CF3-11CE-ADE5-00AA0044773D}" 'DBGUID_DEFAULT
    ' open the output stream to receive the results for the command execute.
    cmdOutput.Open
    ' set the base path for where the template file resides.  Currently this must
    ' point to a file on disk, remote templates via http://...template.xml" are not allowed.
    cmd.Properties("Base Path") = server.MapPath(".") '  App.Path
    ' set up the output stream that will receive the output of the command execute
    cmd.Properties("Output Stream") = Response 
    ' set the XSL to process the output XML stream 
    cmd.Properties("XSL") = "products.xsl"
    ' execute the command stream 
    cmd.Execute , , adExecuteStream
  
    Set cmdOutput = Nothing
    Set cmdStream = Nothing
    Set cmd = Nothing
    Set cn = Nothing
    
%>

</BODY>
</HTML>

The previous example uses both a template and an XSL file.

Create and name the following template files products.xml:

<?xml version='1.0' ?>          
<root xmlns:sql="urn:schemas-microsoft-com:xml-sql">
    <sql:header>
        <sql:param name="ProdName">%</sql:param>
    </sql:header>
    <sql:query>
        SELECT      *
        FROM        Products
        WHERE       ProductName like '%' + @ProdName + '%'
        ORDER BY    ProductName                     
        FOR XML AUTO
    </sql:query>
</root>

Create and name the following XSL file products.xsl:

<?xml version='1.0' ?>          
<xsl:stylesheet xmlns:xsl="http://www.w3.org/TR/WD-xsl">
    <xsl:template match="/">
        <html>
            <head>
                <title>MSDN ADO Product Sample with SQL Server 2000 Features</title>
            </head>
            <body>
            <table border="0" cellPadding="1" cellSpacing="1" 
		      width="100%" style="COLOR: black; FONT-FAMILY: Arial; 
		      FONT-SIZE: 12pt.;FONT-WEIGHT: 500">
            <tr bgColor="#336699" align="center">
                <TD><P ><STRONG><FONT color="white" size="2">Product ID:</FONT></STRONG></P></TD>
                <TD><P ><STRONG><FONT color="white" size="2">Product Name:</FONT></STRONG></P></TD>
                <TD><P ><STRONG><FONT color="white" size="2">Unit Price:</FONT></STRONG></P></TD>
                <TD><P ><STRONG><FONT color="white" size="2">Units In Stock:</FONT></STRONG></P></TD>
                <TD><P ><STRONG><FONT color="white" size="2">Restock Level:</FONT></STRONG></P></TD>
                <TD><P ><FONT color="white" size="2"><STRONG>Units On Order:</STRONG></FONT></P></TD>
            </tr>
                                
            <xsl:for-each select="root/Products">
                <tr style="COLOR: black; FONT-FAMILY: Arial; FONT-SIZE: 0.8em; FONT-WEIGHT: 500">
                    <td bgColor="#F0F0F0"><xsl:value-of select="@ProductID"/></td>
                    <td bgColor="#F0F0F0"><xsl:value-of select="@ProductName"/></td>
                    <td bgColor="#F0F0F0"><xsl:value-of select="@UnitPrice"/></td>
                    <td bgColor="#F0F0F0"><xsl:value-of select="@UnitsInStock"/></td>
                    <td bgColor="#F0F0F0"><xsl:value-of select="@ReorderLevel"/></td>
                    <td bgColor="#F0F0F0"><xsl:value-of select="@UnitsOnOrder"/></td>
                </tr>
            </xsl:for-each>
            </table>
            </body>
         </html>
    </xsl:template>
</xsl:stylesheet>

How To Retrieve XML Data by Using a Template File in an ASP Client (272266)

SUMMARY

MORE INFORMATION

REFERENCES