Skip to content

applying-an-xsl-transformation-sqlxml-managed-classes.md

Latest commit

 

History

History
153 lines (134 loc) · 5.27 KB

applying-an-xsl-transformation-sqlxml-managed-classes.md

File metadata and controls

153 lines (134 loc) · 5.27 KB
title Applying an XSL Transformation (SQLXML Managed Classes) | 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
applying XSL transformations [SQLXML]
Managed Classes [SQLXML], applying XSL transformations
SQLXML Managed Classes, applying XSL transformations
XSL Transformations [SQLXML]
ms.assetid 8562043b-3e9f-41a3-bb41-92b9f14363c4
caps.latest.revision 23
author douglaslMS
ms.author douglasl
manager jhubbard

Applying an XSL Transformation (SQLXML Managed Classes)

In this example, an SQL query is executed against the AdventureWorks database. The XSL transformation is applied to the query result to generate a two-column table of the employees' first and last names.

The XslPath property of the SqlXmlCommand object is used to specify the XSL file and its directory path.

Note

In the code, you must provide the name of the instance of Microsoft [!INCLUDEssNoVersion] in the connection string.

using System;  
using Microsoft.Data.SqlXml;  
using System.IO;  
class Test  
{  
      static string ConnString = "Provider=SQLOLEDB;Server=(local);database=AdventureWorks;Integrated Security=SSPI";  
      public static int testXSL()  
      {  
         //Stream strm;  
         SqlXmlCommand cmd = new SqlXmlCommand(ConnString);  
         cmd.CommandText = "SELECT TOP 20 FirstName, LastName FROM Person.Contact FOR XML AUTO";  
         cmd.XslPath = "MyXSL.xsl";  
         cmd.RootTag = "root";  
         using (Stream strm = cmd.ExecuteStream()){  
            using (StreamReader sr = new StreamReader(strm)){  
               Console.WriteLine(sr.ReadToEnd());  
            }  
        }  
         return 0;  
      }  
      public static int Main(String[] args)  
      {  
         testXSL();     
         return 0;  
      }  
   }

This is the XSL style sheet you can use to test the application:

<?xml version='1.0' encoding='UTF-8'?>  
 <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">   
    <xsl:output method="html"/>  
    <xsl:template match = '*'>  
        <xsl:apply-templates />  
    </xsl:template>  
    <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>

To test this example, you must have the [!INCLUDEmsCoName] .NET Framework installed on your computer.

To test the application

  1. Save the XSL style sheet in a file (MyXSL.xsl).

  2. Save the C# code (DocSample.cs) that is provided in this example in the same folder in which the style sheet is stored.

  3. Compile the code. To compile the code at the command prompt, use:

    csc /reference:Microsoft.Data.SqlXML.dll DocSample.cs

    This creates an executable (DocSample.exe).

  4. At the command prompt, execute DocSample.exe.

Applying an XSL Transformation in the .NET Framework

Instead of applying an XSL transformation in the middle tier, as described previously, you can apply an XSL transformation on the client side (in the .NET Framework). The following revised C# code shows how the XSL transformation is applied in the .NET Framework.

Note

In the code, you must provide the name of the instance of [!INCLUDEssNoVersion] in the connection string.

using System;  
using System.Xml;  
using Microsoft.Data.SqlXml;  
using System.IO;  
using System.Xml.XPath;  
using System.Xml.Xsl;  
  
class Test  
{  
      static string ConnString = "Provider=SQLOLEDB;Server=(local);database=AdventureWorks;Integrated Security=SSPI";  
      public static int testXSL()  
      {  
         //Stream strm;  
         SqlXmlCommand cmd = new SqlXmlCommand(ConnString);  
         cmd.CommandText = "SELECT TOP 20 FirstName, LastName FROM Person.Contact FOR XML AUTO";  
         cmd.RootTag = "root";  
         using (Stream strm = cmd.ExecuteStream()){  
            XmlReader reader = new XmlReader(strm);  
            XPathDocument xd = new XPathDocument(reader, XmlSpace.Preserve);  
            XslCompiledTransform xslt = new XslCompiledTransform();  
            xslt.Load("MyXSL.xsl");  
            XmlWriter writer = XmlWriter.Create("xslt_output.html");  
            xslt.Transform(xd, writer);  
            reader.Close();  
            writer.Close();  
         }  
         return 0;  
      }  
      public static int Main(String[] args)  
      {  
         testXSL();     
         return 0;  
      }  
   }