Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved.
Terms
of Use |
Trademarks
Article ID: 271621 - Last Review: November 22, 2005 - Revision: 2.5
How To Retrieve XML Data with a Template File from a Visual Basic Client
This article was previously published under Q271621
SUMMARY
This sample in this article demonstrates how to retrieve an XML document from SQL Server 2000 by using a template file that contains parameters. More specifically, a query is issued against SQL Server 2000 by using an ActiveX Data Objects (ADO) 2.6 command stream. This query fetches the XML
DataStream.
This sample reads the "products.xml" template file from disk and sets the
command text for the
ADODB.Command object for the query to run.
You must set the following properties for the
Properties collection of the
ADODB.Command object:
- Base Path. This property establishes the location of the template and the Extensible Style Sheet Language (XSL) files.
- Output Stream. This property designates where the resulting XML data stream will be piped.
- Dialect. The dialect defines the syntax and the general rules that the provider uses to parse the string or the stream. By setting the command language dialect, you specify 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 unique identifier (GUID) and is set by using the Dialect property of the Command object.
- XSL. This property transforms the XML document.
NOTE:
Output Stream may be any object that supports an
IStream or
ISequentialStream interface. Objects that support the
IStream interfaces are the
ADODB.Stream object, the Microsoft Internet Information Services (IIS) 5.0
Response object, and a Microsoft XML (MSXML)
DomDocument object.
MORE INFORMATION
- Create a new Standard EXE project in Visual Basic. Form1 is created by default.
- On the Project menu, click Components, and then add a reference to both Microsoft Internet Controls and Microsoft ActiveX Data Objects 2.6.
- Add a Web browser to the form, name it WebBrowser1, and then size it accordingly.
- Add a frame to the form, and place two option buttons in it. Name the first button optXSLYes with the caption XSL Yes, and then name the second button optXSLNo with the caption XSL No.
- Add two command buttons. Name the first button cmdTestIt with the caption Test, and then name the second button cmdExitProgram with the caption Exit.
- Paste the following Visual Basic code in the Code window of the form:
Option Explicit
Const DBGUID_DEFAULT As String = "{C8B521FB-5CF3-11CE-ADE5-00AA0044773D}"
Private Sub cmdExitProgram_Click()
Unload Me
End
End Sub
Private Sub cmdTestIt_Click()
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
' open the database connection
cn.Open "provider=sqloledb;data source=.;initial catalog=northwind;uid=sa;pwd="
' open the command stream that will eventually contain 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 = adTypeText
' read the template file from disk into the command stream to execute
cmdStream.LoadFromFile App.Path & "\products.xml"
' set the command connection
Set cmd.ActiveConnection = cn
' set the command's command stream to hook the template query to the command you want to run.
Set cmd.CommandStream = cmdStream
' set the command dialect
cmd.Dialect = 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") = App.Path
' set up the output stream that will receive the output of the command execute.
cmd.Properties("Output Stream") = cmdOutput
' set the XSL to process if the user requested the output to be fixed.
If optXSLYes Then
' set the file name for the XSL: this inherits the "base path" setting
cmd.Properties("XSL") = "products.xsl"
' set the file extension to ".htm", mainly so the Web browser displays
' set the output as a table. Try with a ".xml" extension... what is displayed?
txtOutputFileName = App.Path & "\queryout.htm"
Else
txtOutputFileName = App.Path & "\queryout.xml"
End If
' execute the command stream with the settings specified above.
cmd.Execute , , adExecuteStream
' position the stream back to the beginning: the "file position" in the
'stream will be at the end of the stream. Writing to a stream will append
'on the end and only while the output from the command execute generates.
cmdOutput.Position = 0
'save the output to a file, this is only needed to use the navigate on the Web browser control
cmdOutput.SaveToFile txtOutputFileName, adSaveCreateOverWrite
' Navigate/display the results of the command executes.
WebBrowser1.Navigate txtOutputFileName
' close and clean-up the objects used.
cmdOutput.Close
cmdStream.Close
cn.Close
Set cmdOutput = Nothing
Set cmdStream = Nothing
Set cmd = Nothing
Set cn = Nothing
End Sub
- To create a template file, create a new text file, paste the following template into the file, and then save it with a name of 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>
- As an option, create a file named products.xsl with the following code to transform the output:
<?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>
<base href="http://localhost/3tier" />
</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>
REFERENCES
For more information about SQL Server 2000, see SQL Server Books Online.
For more information on XML, visit the following Microsoft Web site:
For more information about XML, visit the following World Wide Web Consortium (W3C) Web site:
APPLIES TO
- Microsoft Visual Basic 6.0 Professional Edition
- Microsoft Visual Basic Enterprise Edition for Windows 6.0
- Microsoft Data Access Components 2.6
- Microsoft Data Access Components 2.7
- Microsoft SQL Server 2000 Standard Edition
- Microsoft XML Parser 2.6
- Microsoft XML Parser 3.0
- Microsoft XML Core Services 4.0
Community Feedback System
Very often, it takes hours to solve a problem. Very often, you've looked high
and low, and have tried a lot of solutions. When you finally found it, chances
are, it was because someone else helped you. Here's your chance to give back.
Use our community feedback tool to let others know what worked for you and what
didn't.
Please also understand that the community feedback system is not warranted to be
correct, it's simply a system that we've built to let people try and help each
other. If something in a feedback response doesn't make sense to you, or you're
not comfortable making changes that the feedback talks about (like registry
edits), please consult a professional.
Thank you for using kbAlertz.com Feedback System.
-- Scott Cate
Be the first to leave feedback, to help others about this knowledge base
article.
(Optional) Name
(Optional)
Public URL Or Email
Comments
No
HTML -- Text Only Please