Applying an XSL Transformation (SQLXMLOLEDB Provider)

  • 아티클

In this sample ADO application, an SQL query is executed, and an XSL transformation is applied to the result. Setting the ClientSideXML property to True enforces the processing of the rowset on the client side. The command dialect is set to {5d531cb2-e6ed-11d2-b252-00c04f681b71}, because the SQL query is specified in a template and this dialect must be specified when executing a template. The xsl property specifies the XSL file to use to apply the transformation. The value of Base Path property is used to search for the XSL file. If you specify a path in the value of the xsl property, the path is relative to the path that is specified in the Base Path property.

This example shows how to use the following SQLXMLOLEDB Provider-specific properties:

  • ClientSideXML
  • xsl

In this client-side ADO sample application, an XML template that consists of an SQL query is executed on the server.

Because the ClientSideXML property is set to True, the SELECT statement without the FOR XML clause is sent to the server. The server executes the query and returns a rowset to the client. The client then applies the FOR XML transformation to the rowset and produces the XML document.

The xsl property is specified in the application; therefore, the XSL transformation is applied to the XML document that is generated on the client, and the result is a two-column table.

To execute the template command, the XML template dialect — {5d531cb2-e6ed-11d2-b252-00c04f681b71} — must be specified.

[!참고] In the code, you must provide the name of the instance of Microsoft SQL Server in the connection string. Also, this example specifies the use of the SQL Native Client (SQLNCLI) for the data provider which requires additional network client software to be installed. For more information, see System Requirements for SQL Native Client.

Option Explicit
Sub main()
Dim oTestStream As New ADODB.Stream
Dim oTestConnection As New ADODB.Connection
Dim oTestCommand As New ADODB.Command
oTestConnection.Open "provider=SQLXMLOLEDB.4.0;data provider=SQLNCLI;data source=SqlServerName;initial catalog=AdventureWorks;Integrated Security=SSPI;"
Set oTestCommand.ActiveConnection = oTestConnection
oTestCommand.Properties("ClientSideXML") = True
oTestCommand.CommandText = _
        "<ROOT xmlns:sql='urn:schemas-microsoft-com:xml-sql' >" & _
       " <sql:query> " & _
        "   SELECT TOP 25 FirstName, LastName FROM Person.Contact FOR XML AUTO " & _
        "   </sql:query> " & _
        " </ROOT> "
oTestStream.Open
' You need the dialect if you are executing a template.
oTestCommand.Dialect = "{5d531cb2-e6ed-11d2-b252-00c04f681b71}"
oTestCommand.Properties("Output Stream").Value = oTestStream
oTestCommand.Properties("Base Path").Value = "c:\Schemas\SQLXML4\ExecuteTemplateWithXSL\"
oTestCommand.Properties("xsl").Value = "myxsl.xsl"
oTestCommand.Execute , , adExecuteStream

oTestStream.Position = 0
oTestStream.Charset = "utf-8"
Debug.Print oTestStream.ReadText(adReadAll)
End Sub
Sub Form_Load()
 main
End Sub

The XSL template follows. The result of applying this XSL template is a two-column table.

<?xml version='1.0' encoding='UTF-8'?>          
 <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0"> 
                                          
    <xsl:template match = 'Person.Contact'>
       <TR>
         <TD><xsl:value-of select = '@FirstName' /></TD>
         <TD><B><xsl:value-of select = '@LastName' /></B></TD>
       </TR>
    </xsl:template>
    <xsl:template match = '/'>
      <HTML>
        <HEAD>
           <STYLE>th { background-color: #CCCCCC }</STYLE>
        </HEAD>
        <BODY>
         <TABLE border='1' style='width:300;'>
           <TR><TH colspan='2'>Contacts</TH></TR>
           <TR>
              <TH >First name</TH>
              <TH>Last name</TH>
           </TR>
           <xsl:apply-templates select = 'ROOT' />
         </TABLE>
        </BODY>
      </HTML>
    </xsl:template>
</xsl:stylesheet>