Skip to content

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

Latest commit

 

History

History
148 lines (129 loc) · 5.39 KB

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

File metadata and controls

148 lines (129 loc) · 5.39 KB
title Applying an XSL Transformation (SQLXML)
description View an example of a SQL query that applies an XSL transformation by using the XslPath property of the SqlXmlCommand object in the SQLXML Managed Classes.
author MikeRayMSFT
ms.author mikeray
ms.date 03/14/2017
ms.service sql
ms.subservice xml
ms.topic reference
helpviewer_keywords
applying XSL transformations [SQLXML]
Managed Classes [SQLXML], applying XSL transformations
SQLXML Managed Classes, applying XSL transformations
XSL Transformations [SQLXML]
monikerRange =azuresqldb-current||>=sql-server-2016||>=sql-server-linux-2017||=azuresqldb-mi-current

Applying an XSL Transformation (SQLXML Managed Classes)

[!INCLUDE SQL Server Azure SQL Database] In this example, a 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;  
      }  
   }