Skip to content

applying-an-xsl-transformation-sqlxmloledb-provider.md

Latest commit

 

History

History
108 lines (93 loc) · 4.89 KB

applying-an-xsl-transformation-sqlxmloledb-provider.md

File metadata and controls

108 lines (93 loc) · 4.89 KB
title Applying an XSL Transformation (SQLXMLOLEDB Provider) | Microsoft Docs
ms.custom
ms.date 03/14/2017
ms.prod sql-server-2016
ms.reviewer
ms.suite
ms.technology
dbe-xml
ms.tgt_pltfrm
ms.topic reference
helpviewer_keywords
SQLXMLOLEDB Provider, applying XSL transformations
applying XSL transformations [SQLXML]
xsl property
Base Path property
XSL Transformations [SQLXML]
ms.assetid cb5e41ab-dd20-4873-af20-f417bd1bbf6d
caps.latest.revision 30
author douglaslMS
ms.author douglasl
manager jhubbard

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.

Note

In the code, you must provide the name of the instance of Microsoft [!INCLUDEssNoVersion] in the connection string. Also, this example specifies the use of the [!INCLUDEssNoVersion] Native Client for the data provider which requires additional network client software to be installed. For more information, see System Requirements for SQL Server 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=SQLNCLI11;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>