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

MORE INFORMATION

1. Create a new Standard EXE project in Visual Basic. Form1 is created by default.
2. On the Project menu, click Components, and then add a reference to both Microsoft Internet Controls and Microsoft ActiveX Data Objects 2.6.
3. Add a Web browser to the form, name it WebBrowser1, and then size it accordingly.
4. 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.
5. Add two command buttons. Name the first button cmdTestIt with the caption Test, and then name the second button cmdExitProgram with the caption Exit.
6. 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
   					

7. 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> 
   					

8. 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="/">
           <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>