11.4 Hierarchical XML

XML that comes from a database, whether generated directly from a DataSet or through an XmlDataDocument, is inherently relational. That is, each table is represented by a single element, and its columns are represented by elements within it. Relations between tables are indicated by foreign key constraints and row identifiers. This makes perfect sense for relational data, but sometimes you might want to use a more hierarchical format.

XML is ideal for representing hierarchical data, because it is itself a tree-oriented format. The data from the coupon database could easily be represented in a combination of relational hierarchical XML structures, as shown in Example 11-12.

Example 11-12. Hierarchical representation of coupon database

<AngusHardware>
  <customers>
    <customer_id>1</customer_id>
    <name>Mark's Roofing</name>
    <address1>99 Beltline Pkwy</address1>
    <address2>Suite 100</address2>
    <city>Wannaque</city>
    <state>NH</state>
    <zip>05461     </zip>
  </customers>
  <coupons>
    <coupon_code>077GH     </coupon_code>
    <discount_amount>15</discount_amount>
    <discount_type>0</discount_type>
    <expiration_date>2002-11-09T14:17:41.6370000-05:00</expiration_date>
  </coupons>
  <coupons>
    <coupon_code>665RQ     </coupon_code>
    <discount_amount>15</discount_amount>
    <discount_type>0</discount_type>
    <expiration_date>2002-11-30T00:00:00.0000000-05:00</expiration_date>
    <coupon_redemptions>
      <coupon_code>665RQ     </coupon_code>
      <total_discount>21.5</total_discount>
      <redemption_date>2002-11-10T00:00:00.0000000-05:00</redemption_date>
      <customer_id>1</customer_id>
    </coupon_redemptions>
  </coupons>
  <coupons>
    <coupon_code>81BIN     </coupon_code>
    <discount_amount>10</discount_amount>
    <discount_type>1</discount_type>
    <expiration_date>2003-01-31T00:00:00.0000000-05:00</expiration_date>
  </coupons>
  <coupons>
    <coupon_code>99GGY     </coupon_code>
    <discount_amount>5</discount_amount>
    <discount_type>0</discount_type>
    <expiration_date>2002-12-31T00:00:00.0000000-05:00</expiration_date>
  </coupons>
</AngusHardware>

This differs from the straight relational output from the DataSet, shown in Example 11-13.

Example 11-13. Relational representation of coupon database

<AngusHardware>
  <customers>
    <customer_id>1</customer_id>
    <name>Mark's Roofing</name>
    <address1>99 Beltline Pkwy</address1>
    <address2>Suite 100</address2>
    <city>Wannaque</city>
    <state>NH</state>
    <zip>05461     </zip>
  </customers>
  <coupons>
    <coupon_code>077GH     </coupon_code>
    <discount_amount>15</discount_amount>
    <discount_type>0</discount_type>
    <expiration_date>2002-11-09T14:17:41.6370000-05:00</expiration_date>
  </coupons>
  <coupons>
    <coupon_code>665RQ     </coupon_code>
    <discount_amount>15</discount_amount>
    <discount_type>0</discount_type>
    <expiration_date>2002-11-30T00:00:00.0000000-05:00</expiration_date>
  </coupons>
  <coupons>
    <coupon_code>81BIN     </coupon_code>
    <discount_amount>10</discount_amount>
    <discount_type>1</discount_type>
    <expiration_date>2003-01-31T00:00:00.0000000-05:00</expiration_date>
  </coupons>
  <coupons>
    <coupon_code>99GGY     </coupon_code>
    <discount_amount>5</discount_amount>
    <discount_type>0</discount_type>
    <expiration_date>2002-12-31T00:00:00.0000000-05:00</expiration_date>
  </coupons>
  <coupon_redemptions>
    <coupon_code>665RQ     </coupon_code>
    <total_discount>21.5</total_discount>
    <redemption_date>2002-11-10T00:00:00.0000000-05:00</redemption_date>
    <customer_id>1</customer_id>
  </coupon_redemptions>
</AngusHardware>

There is one major difference between the relational and hierarchical views. In the relational view, each row's elements are direct children of the root element. In the hierarchical view, however, the coupon_redemptions element is a child of the coupons element; because any coupon_redemptions row can only be related to exactly one coupons row, it makes sense to present them in this hierarchical fashion.

How can you have the DataSet present this hierarchical XML view of the data? There are a couple of ways: transformation and synchronizing data.

11.4.1 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.

Example 11-14. Program to transform a DataSet to another XML format

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.

Example 11-15. XSLT stylesheet to transform relational XML to hierarchical XML

<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>

11.4.2 Synchronizing Data

Take a look at the following code snippet:

AngusHardware dataSet = new AngusHardware( );

XmlDataDocument doc = new XmlDataDocument(dataSet);
doc.Load("HierarchicalCoupons.xml");

XmlTextWriter writer = new XmlTextWriter(Console.Out);
writer.Formatting = Formatting.Indented;

dataSet.WriteXml(writer);

You've already seen code that uses this pattern It creates an instance of a DataSet, specifically the one we generated for the Angus Hardware coupon database. Then it loads the DataSet with data from an XML file by creating an XmlDataDocument for it. When the data is written to the console, it appears in a relational XML format.

But if you change the last line, as shown below, you get hierarchical XML output that matches the format and content of the document you loaded into the XmlDataDocument:

doc.WriteTo(writer);

The XmlDataDocument knows how to map between the relational form of the DataSet and the hierarchical form of the document it's been loaded with, as long as the element names are the same. In fact, it handily ignores any elements it's not familiar with from the schema in the DataSet when it writes the relational XML.

This is handy because you can read in XML from the hierarchical format and edit it using the DataSet, then output it back into the hierarchical XML. In other words, you can accomplish all the work this lengthy bit of DOM code does:

dataSet.EnforceConstraints = false;
XmlElement coupons = doc.CreateElement("coupons");
doc.DocumentElement.AppendChild(coupons);

XmlElement coupon_code = doc.CreateElement("coupon_code");
coupon_code.AppendChild(doc.CreateTextNode("542HH")); 
coupons.AppendChild(coupon_code);

XmlElement discount_amount = doc.CreateElement("discount_amount");
discount_amount.AppendChild(doc.CreateTextNode("10")); 
coupons.AppendChild(discount_amount);

XmlElement discount_type = doc.CreateElement("discount_type");
discount_type.AppendChild(doc.CreateTextNode(
  ((int)DiscountType.Percentage).ToString( ))); 
coupons.AppendChild(discount_type);

XmlElement expiration_date = doc.CreateElement("expiration_date");
expiration_date.AppendChild(doc.CreateTextNode(
  new DateTime(2003, 1, 31).ToString(
  "yyyy-MM-ddT00:00:00.0000000-05:00"))); 
coupons.AppendChild(expiration_date);
dataSet.EnforceConstraints = true;

with this single line of DataSet code:

dataSet.coupons.AddcouponsRow("542HH", 10, 0, new DateTime(2003,1,31));

I think you'd have to agree that that's worthwhile.