XSLT–A Relational DB Approach Using DataSet.GetXML Output

All these hierarchies are getting in my way!

I was speaking with a friend of mine the other day about XSLT. He hasn’t used it much and I told him of an approach that makes it easy to use once you get the hang of it. I have found that for most developers XSLT has a steep learning curve. Some things about XSLT just stumps us.

For example, we are used to how variables work. You set them and then you can change them later if you want to. Well, XSLT doesn’t work like that. Oh, it has variables but once set, they can’t be changed, which isn’t intuitive to a developer.

Also, developers sometimes create deep hierarchies in their XML. This can make retrieving the data in XSLT challenging—especially when some levels have sub-levels and other levels don’t.

If you create XML the way that I suggest here and use the patterns for XSLT that I demonstrate, you’ll have an easier time with the whole thing.

I’ve included the XSLTDataApproach.zip which is a zip file of the project, the Northwinds database and some sample XML. You might want to download this first and check it out before moving forward.

(By the way, you can use the approach even if you don’t code in .NET. )

Creating the XML
The first step is to make your life easy by creating XML that is easy to consume in XSLT. Instead of deep hierarchies, use XML structured like a relational database. Here’s a piece of XML from the example project below (you might recognize the data from the Northwind database.):

   1: <?xml version="1.0" encoding="utf-8" ?>
   2: <Data>
   3:   <Customers>
   4:     <CustomerID>VINET</CustomerID>
   5:     <CompanyName>Vins et alcools Chevalier</CompanyName>
   6:     <ContactName>Paul Henriot</ContactName>
   7:     <ContactTitle>Accounting Manager</ContactTitle>
   8:     <Address>59 rue de l'Abbaye</Address>
   9:     <City>Reims</City>
  10:     <PostalCode>51100</PostalCode>
  11:     <Country>France</Country>
  12:     <Phone>26.47.15.10</Phone>
  13:     <Fax>26.47.15.11</Fax>
  14:   </Customers>

There are three and only three levels to the XML file. If you’ve ever used the DataSet.GetXML() function, you’ll recognize this XML structure immediately.

First, you have the document level. I called it Data. You can call it whatever you like. If you’re making this with a .NET DataSet (or DataTable) this is the name of the DataSet (or DataTable).

The second level is the table row level. You’ll have an element here for each row in each table. So, if you have three rows in the Customers table, you’ll have three Customers elements.

The third level is the column (field) level. For each field that you want in a row in the table, you create an element and place the value in there.

That’s it. It’s that simple.

Notice that we don’t use attributes. This makes things simpler in the XSLT as well. Plus, we avoid issues with fields that contain XML elements and special characters. Using elements, we can just use a CDATA tag set if we have text that will cause problems.

Creating the XML from a DataSet

It is so convenient that the DataSet.GetXML() function emits this exact structure. Here’s some sample code in C# showing how to create the above XML for the Customer, Orders, Order Details and Products tables for a given CustomerID in the Northwind database:

static string GetCustomerOrdersXML(string customerID)
{
    SqlConnection conn = new SqlConnection(@"Server=.\SQLExpress;AttachDbFilename=|DataDirectory|\Northwind.mdf;Database=Northwind;Trusted_Connection=Yes;");
    conn.Open();
    SqlCommand cmd = new SqlCommand(
        "SELECT [Customers].* FROM [Customers] WHERE CustomerID = @CustomerID;" + 
        "SELECT [Orders].* FROM [Orders] WHERE CustomerID = @CustomerID;" +
        "SELECT [Order Details].* FROM [Order Details] JOIN [Orders] ON [Orders].OrderID = [Order Details].OrderID AND [Orders].CustomerID = @CustomerID;" +
        "SELECT [Products].* FROM [Products] JOIN [Order Details] ON [Products].ProductID = [Order Details].ProductID JOIN [Orders] ON [Orders].OrderID = [Order Details].OrderID AND [Orders].CustomerID = @CustomerID",
        conn);
    cmd.Parameters.AddWithValue("@CustomerID", customerID);
    SqlDataReader dr = cmd.ExecuteReader();
    DataSet ds = new DataSet("Data");
    ds.Load(dr, LoadOption.OverwriteChanges, new string[4] { "Customers", "Orders", "OrderDetails", "Products" });
    conn.Close();
    return ds.GetXml();
}

The SQL command has 4 select statements so that you can get all the data in one request using the DataSet Load() command. Ideally this would be in a stored procedure.

The GetCustomerOrdersXML function creates the DataSet and loads it from an open DataReader creating a new table for each result set. You can name each of the tables (result sets) by providing an array of the names in the order they are being returned as a parameter to the Load() command. These names become the row element names in the XML. This is also incredibly convenient. We’ve created usable XML without having to do a single string concatenation. Of course, you could also use SQL Server’s XML features but the approach above is usually easier to crank out and maintain.

Pay close attention to the structure of the SELECT statements.

What you’ll notice is that I’m joining only for the purposes of finding the data. I’m actually just returning data from one table at a time in each SELECT statement on purpose. I want to do my actual joining in the XSLT through the use of nested templates (you’ll see below). Doing this gives you some flexibility in the future if you want to move data around within the template or use the same data in a different way. Make sure that your result sets include primary and foreign keys since you’ll be using XSLT searches to join them.

Creating the XSLT

Finally, here’s the XSLT in the format that I’m talking about:

   1: <?xml version="1.0" encoding="UTF-8" ?>
   2: <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
   3:  
   4:   <xsl:output indent="yes" method="html"/>
   5:   <xsl:preserve-space elements = "*" />
   6:  
   7:   <xsl:template name="EmitProduct">
   8:     <xsl:param name="ProductID" />
   9:       <xsl:for-each select="/Data/Products[ProductID=$ProductID]"><xsl:value-of select="ProductName"/></xsl:for-each>
  10:   </xsl:template>
  11:  
  12:   <xsl:template name="EmitOrderDetails">
  13:     <xsl:param name="OrderID" />
  14:     <table cellpadding="2" cellspacing="0" border="1" width="100%">
  15:       <tr>
  16:         <th>Product</th>
  17:         <th>Unit Price</th>
  18:         <th>Quantity</th>
  19:       </tr>
  20:       <xsl:for-each select="/Data/OrderDetails[OrderID=$OrderID]">
  21:         <tr>
  22:           <td><xsl:call-template name="EmitProduct"><xsl:with-param name="ProductID" select="ProductID" /></xsl:call-template></td>
  23:           <td><xsl:value-of select="UnitPrice"/></td>
  24:           <td><xsl:value-of select="Quantity"/></td>
  25:         </tr>
  26:       </xsl:for-each>
  27:     </table>
  28:   </xsl:template>
  29:   
  30:   <xsl:template name="EmitOrder">
  31:     <xsl:param name="OrderID" />
  32:     <table cellpadding="2" cellspacing="0" border="1"  width="100%">
  33:       <xsl:for-each select="/Data/Orders[OrderID=$OrderID]">
  34:         <tr>
  35:           <td style="background:#e0e0e0">Order ID:</td>
  36:           <td style="background:#e0e0e0"><xsl:value-of select="OrderID"/></td>
  37:           <td style="background:#e0e0e0">Date:</td>
  38:           <td style="background:#e0e0e0"><xsl:value-of select="OrderDate"/></td>
  39:         </tr>
  40:         <tr>
  41:           <td colspan="4">
  42:             <xsl:call-template name="EmitOrderDetails">
  43:               <xsl:with-param name="OrderID" select="OrderID" />
  44:             </xsl:call-template>
  45:           </td>
  46:         </tr>
  47:       </xsl:for-each>
  48:     </table>
  49:  
  50:   </xsl:template>
  51:  
  52:   <xsl:template name="EmitOrders">
  53:     <xsl:param name="CustomerID" />
  54:     <xsl:for-each select="/Data/Orders[CustomerID=$CustomerID]">
  55:       <xsl:call-template name="EmitOrder">
  56:         <xsl:with-param name="OrderID" select="OrderID" />
  57:       </xsl:call-template>
  58:     </xsl:for-each>
  59:   </xsl:template>
  60:  
  61:   <xsl:template match="/">
  62:  
  63:     <xsl:for-each select="/Data/Customers">
  64:       <h1><xsl:value-of select="CompanyName"/></h1>
  65:       <h2><xsl:value-of select="Address"/>, <xsl:value-of select="City"/>, <xsl:value-of select="PostalCode"/></h2>
  66:       <table cellpadding="2" cellspacing="0" border="0" width="800">
  67:         <tr>
  68:           <td>
  69:             <xsl:call-template name="EmitOrders"><xsl:with-param name="CustomerID" select="CustomerID" /></xsl:call-template>
  70:         </td>
  71:         </tr>
  72:       </table>
  73:     </xsl:for-each>
  74:   </xsl:template>
  75:  
  76:   </xsl:stylesheet>

It’s pretty long so you might want to study it a second or look at the one in the zip file above. When transformed with the XML created by the above routine, you’ll get this snippet of HTML:

Vins et alcools Chevalier

59 rue de l’Abbaye, Reims, 51100

Order ID: 10248 Date: 1996-07-04T00:00:00-04:00
Product Unit Price Quantity
Queso Cabrales 14.0000 12
Singaporean Hokkien Fried Mee 9.8000 10
Mozzarella di GiovanniMozzarella di Giovanni 34.8000 5
Order ID: 10274 Date: 1996-08-06T00:00:00-04:00
Product Unit Price Quantity
Flotemysost 17.2000 20
Mozzarella di GiovanniMozzarella di Giovanni 27.8000 7
Order ID: 10295 Date: 1996-09-02T00:00:00-04:00
Product Unit Price Quantity
Gnocchi di nonna Alice 30.4000 4
Order ID: 10737 Date: 1997-11-11T00:00:00-05:00
Product Unit Price Quantity
Konbu 6.0000 4
Jack’s New England Clam Chowder 9.6500 12
Order ID: 10739 Date: 1997-11-12T00:00:00-05:00
Product Unit Price Quantity
Inlagd Sill 19.0000 6
Filo Mix 7.0000 18

Let’s walk through the template.

The main template starts on line 61. We loop through each of the customers—in this case there’s only one—and then we emit the company name and the address. Then, we call the EmitOrders template.

EmitOrders starts on line 52. You’ll notice that we pass in a parameter for the CustomerID. This is where the "XSLT Joining" is taking place and shows the real power of an XSLT "variable". We use that $CustomerID variable to select only the rows in the Order’s table for that customer ID. In this example, it will be all the rows for the customer ID since that’s how we created the XML. Next, we call the EmitOrder (no s) template passing in the OrderID as the parameter (starting to see the pattern yet?).

EmitOrder starts on line 30. It looks for orders with the given OrderID. Of course, there should only be one. It emits the order ID, the date and then calls the EmitOrderDetails() template to display every item on the order.

By now, you should see the pattern. Every detail table is being given it’s own template with the primary or foreign keys as parameters. The values being emitted in the transform are searched for in the XML by specifying the key to the rows. If it is a foreign key, you’ll possibly have many rows. If it’s a primary key, then you’ll have just one row.

There’s more going on in the template. For example, I didn’t discuss the EmitProduct template. Play around with the data and the templates in the VS 2005 project  until you get the feel for it. I’ve already created the sandbox for you. Also, I include a play XML as well as the Northwind.mdf in the project to make things easy for you.

Let me know what you think about this approach. It has worked very well for me.