
	Search KbAlertz Advanced Search

Receive Microsoft Knowledge Base articles by E-Mail?

Every night we scan the Microsoft Knowledge Base. If technologies you're interested in are updated, we'll send you an e-mail. You only get one e-mail a day, and only when new articles are added.

Click here to create a
FREE account

Already have an account?
[Click here to Login]

Microsoft Knowledge Base Article

This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved. Terms of Use | Trademarks

Ads by Lake Quincy Media

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

View products that this article applies to.

This article was previously published under Q271621

Expand all | Collapse all

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.

Back to the top

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>

Back to the top

REFERENCES

For more information about SQL Server 2000, see SQL Server Books Online.

For more information on XML, visit the following Microsoft Web site:

http://msdn.microsoft.com/xml (http://msdn.microsoft.com/xml)

For more information about XML, visit the following World Wide Web Consortium (W3C) Web site:

http://www.w3.org/xml (http://www.w3.org/xml)

Back to the top

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

Back to the top

kbhowto KB271621

Back to the top

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

Ads by Lake Quincy Media


	Copyright © 2002-2007 KBALERTZ.COM. All Rights Reserved. Terms / Privacy. Discount ASP.NET Hosting

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 ADO 2.6 command strea

Microsoft Knowledge Base Article

How To Retrieve XML Data with a Template File from a Visual Basic Client

SUMMARY

MORE INFORMATION

REFERENCES

APPLIES TO

Keywords:Â