Transformation
Because
both the relational and hierarchical views are XML, one can be
transformed into the other with an XSLT transformation. A program to
transform the DataSet from one format to another
is shown in Example 11-14.
using System;
using System.Data;
using System.Data.SqlClient;
using System.Xml;
using System.Xml.Xsl;
public class TransformData {
public static void Main(string [ ] args) {
DataSet dataSet = new DataSet("AngusHardware");
SqlConnection connection = new SqlConnection(
"Initial Catalog=AngusHardware; Integrated Security=SSPI; User ID=sa");
SqlDataAdapter customersAdapter = new SqlDataAdapter(
"SELECT * FROM customers", connection);
SqlDataAdapter couponsAdapter = new SqlDataAdapter(
"SELECT * FROM coupons", connection);
SqlDataAdapter couponRedemptionsAdapter = new SqlDataAdapter(
"SELECT * FROM coupon_redemptions", connection);
customersAdapter.Fill(dataSet, "customers");
couponsAdapter.Fill(dataSet, "coupons");
couponRedemptionsAdapter.Fill(dataSet, "coupon_redemptions");
XmlDataDocument doc = new XmlDataDocument(dataSet);
XmlTextWriter writer = new XmlTextWriter(Console.Out);
writer.Formatting = Formatting.Indented;
XslTransform transform = new XslTransform( );
transform.Load("Coupons.xsl");
transform.Transform(doc, null, writer);
}
}
You’ve seen most of
this already at one point or another. The main variation that you
have not seen yet is the inclusion of several
SqlDataAdapter instances in the same
DataSet. Once the DataSet is
populated using each
SqlDataAdapter’s Fill(
) method, it’s a simple matter to create
an XmlDataDocument and an
XslTransform. The XslTransform
is loaded from the stylesheet Coupons.xsl, and the output goes to the
console.
The beauty of this approach is that it does any transformation that
can be specified via an XSLT stylesheet. Example 11-15
shows an example of a stylesheet that does the transformation from
relational XML to hierarchical XML. You could just as easily write
one to produce an HTML or plain text view of the
DataSet.
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
<xsl:output method="xml" />
<xsl:template match="/">
<xsl:apply-templates select="AngusHardware" />
</xsl:template>
<xsl:template match="AngusHardware">
<AngusHardware>
<xsl:apply-templates select="customers" />
<xsl:apply-templates select="coupons" />
</AngusHardware>
</xsl:template>
<xsl:template match="customers">
<xsl:copy-of select="." />
</xsl:template>
<xsl:template match="coupons">
<coupons>
<xsl:copy-of select="./coupon_code" />
<xsl:copy-of select="./discount_amount" />
<xsl:copy-of select="./discount_type" />
<xsl:copy-of select="./expiration_date" />
<xsl:variable name="coupon_code" select="./coupon_code" />
<xsl:if test="count(//coupon_redemptions[coupon_code=$coupon_code]) > 0">
<xsl:for-each select="//coupon_redemptions[coupon_code=$coupon_code]">
<xsl:copy-of select="." />
</xsl:for-each>
</xsl:if>
</coupons>
</xsl:template>
</xsl:stylesheet>