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.
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="http://support.microsoft.com">
<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>
For more information about SQL 2000, please see SQL Server Books Online.
For more information on XML, please see the following Web sites: