Applying an XSLT Transform to a DataSet

Article
10/17/2014

The WriteXml method of the DataSet enables you to write the contents of a DataSet as XML data. A common task is to then transform that XML to another format using XSL Transformations (XSLT). However, synchronizing a DataSet with an XmlDataDocument enables you to apply an XSLT stylesheet to the contents of a DataSet without having to first write the contents of the DataSet as XML data using WriteXml.

The following example populates a DataSet with tables and relationships, synchronizes the DataSet with an XmlDataDocument, and writes a portion of the DataSet as an HTML file using an XSLT stylesheet. Following are the contents of the XSLT stylesheet.

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">

<xsl:template match="CustomerOrders">
  <HTML>
  <STYLE>
  BODY {font-family:verdana;font-size:9pt}
  TD   {font-size:8pt}
  </STYLE>
    <BODY>
    <TABLE BORDER="1">
      <xsl:apply-templates select="Customers"/>
    </TABLE>

    </BODY>
  </HTML>
</xsl:template>

<xsl:template match="Customers">
    <TR><TD>
      <xsl:value-of select="ContactName"/>, <xsl:value-of select="Phone"/><BR/>
    </TD></TR>
      <xsl:apply-templates select="Orders"/>
</xsl:template>

<xsl:template match="Orders">
  <TABLE BORDER="1">
    <TR><TD valign="top"><B>Order:</B></TD><TD valign="top"><xsl:value-of select="OrderID"/></TD></TR>
    <TR><TD valign="top"><B>Date:</B></TD><TD valign="top"><xsl:value-of select="OrderDate"/></TD></TR>
    <TR><TD valign="top"><B>Ship To:</B></TD>
        <TD valign="top"><xsl:value-of select="ShipName"/><BR/>
        <xsl:value-of select="ShipAddress"/><BR/>
        <xsl:value-of select="ShipCity"/>, <xsl:value-of select="ShipRegion"/>  <xsl:value-of select="ShipPostalCode"/><BR/>
        <xsl:value-of select="ShipCountry"/></TD></TR>
  </TABLE>

</xsl:template>

</xsl:stylesheet>

The following code is the code to fill the DataSet and apply the XSLT style sheet.

Note If the DataSet that you are applying an XSLT style sheet to contains relations, you will achieve best performance if you set the Nested property of the DataRelation to true for each nested relation. This allows you to use XSLT style sheets that implement natural top-down processing to navigate the hierarchy and transform the data, as opposed to using performance-intensive XPath location axes (for example, preceding-sibling and following-sibling in style sheet node test expressions) to navigate the data hierarchy. For more information on nested relations, see Nested DataRelations.

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Xml
Imports System.Xml.Xsl

Public Class Sample
  Public Shared Sub Main()
    Dim nwindConn As SqlConnection = New SqlConnection("Data Source=localhost;Initial Catalog=northwind;Integrated Security=SSPI")
    nwindConn.Open()

    Dim myDataSet As DataSet = New DataSet("CustomerOrders")

    Dim custDA As SqlDataAdapter = New SqlDataAdapter("SELECT * FROM Customers", nwindConn)
    custDA.Fill(myDataSet, "Customers")

    Dim ordersDA As SqlDataAdapter = New SqlDataAdapter("SELECT * FROM Orders", nwindConn)
    ordersDA.Fill(myDataSet, "Orders")

    nwindConn.Close()

    myDataSet.Relations.Add("CustOrders", _
                            myDataSet.Tables("Customers").Columns("CustomerID"), _
                            myDataSet.Tables("Orders").Columns("CustomerID")).Nested = true

    Dim xmlDoc As XmlDataDocument = New XmlDataDocument(myDataSet) 
      
    Dim xslTran As XslTransform = New XslTransform
    xslTran.Load("transform.xsl")
             
    Dim writer As XmlTextWriter = New XmlTextWriter("xslt_output.html", System.Text.Encoding.UTF8)

    xslTran.Transform(xmlDoc, Nothing, writer)
    writer.Close()
  End Sub
End Class
[C#]
using System;
using System.Data;
using System.Data.SqlClient;
using System.Xml;
using System.Xml.Xsl;

public class Sample
{
  public static void Main()
  {
    SqlConnection nwindConn = new SqlConnection("Data Source=localhost;Initial Catalog=northwind;Integrated Security=SSPI;");
    nwindConn.Open();

    DataSet custDS = new DataSet("CustomerDataSet");

    SqlDataAdapter custDA = new SqlDataAdapter("SELECT * FROM Customers", nwindConn);
    custDA.Fill(custDS, "Customers");

    SqlDataAdapter ordersDA = new SqlDataAdapter("SELECT * FROM Orders", nwindConn);
    ordersDA.Fill(custDS, "Orders");

    nwindConn.Close();

    custDS.Relations.Add("CustOrders",
                         custDS.Tables["Customers"].Columns["CustomerID"],
                         custDS.Tables["Orders"].Columns["CustomerID"]).Nested = true;

    XmlDataDocument xmlDoc = new XmlDataDocument(custDS); 
      
    XslTransform xslTran = new XslTransform();
    xslTran.Load("transform.xsl");
             
    XmlTextWriter writer = new XmlTextWriter("xslt_output.html", System.Text.Encoding.UTF8);

    xslTran.Transform(xmlDoc, null, writer);
    writer.Close();
  }
}

Applying an XSLT Transform to a DataSet

See Also

Additional resources