Simplify the Creation of XML from SQL Server Data

by Hugh Lynch

CTO, Restricted Stock Systems, Inc.

Introduction

Like many developers, my team and I were enchanted by the

possibilities of XML and XSL/XSLT when they emerged a few years ago.  It seemed

we could describe anything using XML and morph it to our hearts’ content using

XSL.  We wove the new technology deep into our designs, building substantial

web applications in which the data is rendered as XML then transformed to HTML

and other formats. 

While I am content with the decision to embrace XML, there

were significant obstacles.  Chiefly, how to create XML from our relational

data in SQL Server?  Existing techniques held early promise, but proved either too

simplistic (FOR XML AUTO) or too cumbersome (FOR XML EXPLICIT, XML Views, etc)

for widespread adoption. 

We wanted something simple and efficient to leverage our existing

tools, experience and the capabilities of SQL Server.  We invented a simple

XML-assembly grammar that we embed in the results returned from stored

procedures, utilizing SQL Server’s ability to return multiple “chained” result

sets from a single command invocation.

Next, we created a general-purpose interpreter (we

implemented COM and .NET versions) to parse the embedded XML-assembly

instructions and render the returned data accordingly as XML.

Finally, we developed two general-purpose invocation

utilities, one for the command line and one web-based (implemented in .NET and ASP.NET,

respectively).  These tools provide simple mechanisms to invoke parameterized stored

procedures, process the results using the interpreter, and output the resulting

XML, either raw or transformed.

The benefits of this approach, which we call “data shapes”

or simply “shapes”, include:

a.       Simplicity. 

With the grammar defined and the interpreter written, developers simply write

conforming stored procedures to create arbitrarily complex XML documents. 

Unlike some alternatives, complex documents do not require complex procedures –

just select the data to assemble and accompany it with simple text instructions

like “RELATE Employee.CompanyID to Company.CompanyID” (more on the grammar

later).

b.      Efficiency. 

Returning all of the data for a document (sometimes dozens of recordsets) from

a stored procedure keeps database trips to an absolute minimum.  The technique

also delegates the assembly of the XML from the database server to the

interpreter, which can be distributed across middle- or presentation-layer

machines.  Writing XML involves conversion and formatting of native data types

as text, character escaping, and filtering of child recordsets, all of which

consume valuable CPU cycles.

c.       Performance

and Control.  Writing shapes as stored procedures gives the developer all

the benefits of that language:  pre-compiled execution plans, caching, parameterization,

conditional logic, temporary storage, permission controls, nested calls, etc. 

d.      Familiarity. 

While the grammar of shapes is no doubt novel, the tools used to create, edit,

test and analyze them are not.  Shapes can be developed in Query Analyzer and

Visual Studio or any other T-SQL tool.  Their performance can be evaluated

using SQL Profiler, and dissected by examining execution plans.  Because a

shape encapsulates both data selection and assembly instruction in a single

scriptable procedure, it can be easily migrated between development, test and

production environments.

This article provides a complete definition of the shape

grammar as well as functional explanations of the shape interpreter and shape

invoker utilities.  The accompanying download includes the binary and

configuration files necessary to experiment with shapes on your own. 

Installation instructions are listed at the end of the article. 

You can also see shapes in action at http://tech.rssgroup.com/shapes.

Let’s begin with some examples.

Example 1 – Hello, World!

Tradition dictates that all new programming technologies

introduce themselves, so here is a very simple shape listing:

create procedure dbo.shpHello
as
   select 'dataset root.Hello' _shp
   select 'Hello, World!' Msg
go

which can be invoked through ASP.NET, using the URL:

http://.../shpHello.aspx

And here are the results it produces:

<?xml version="1.0" encoding="utf-8"?>
<Hello Msg="Hello, World!"/>

While trivial, there are a couple of things to note.  The

shape instruction ('dataset root.Hello') tells the interpreter to name the following result set “root” and to represent each record within it as a <Hello> element.  By default, the interpreter maps the single column of the data set (Msg) to an attribute of the same name in the <Hello> element.

Example 2 – A Realistic Report

Using Microsoft’s standard Northwind database, we will create a shape to return all of the orders created by a specified employee during a specified date range.  The data will be organized by customer.

The following URL invokes the stored procedure dbo.shpEmployeeActivity with three parameters: employeeID, startDate and endDate.

http://.../shpEmployeeActivity.aspx?ID_Employee=6&startDate=1/1/97&endDate=1/31/97

Here’s the listing of the stored procedure.  The embedded instruction statements are again highlighted in blue.  The procedure uses a table variable to store orderIDs.

create procedure dbo.shpEmployeeActivity (
  @employeeID  int
, @startDate   smalldatetime
, @endDate     smalldatetime
as
   set nocount on
   -- create a table variable to hold the set of filtered orderIDs
   declare @nOrders int
   declare @tblOrders table (
      OrderID int primary key)
   insert @tblOrders
   select orderID
    from orders 
    where employeeID = @employeeID
    and orderDate between @startDate and @endDate
   set @nOrders = @@rowcount
   set nocount off
   select 'dataset root.Employee' _shp
   select EmployeeID, LastName, FirstName, Title, ...
    from Employees
    where employeeID=@employeeID
   if @nOrders>0
    begin
      select 'dataset Customers.Customer relate to root' _shp
      select * 
       from customers
       where customerID in 
         (select customerID from orders where orderID in 
            (select orderID from @tblOrders))
      select 'dataset Orders.Order relate customerID to Customers.customerID' _shp
      select *
       from orders
       where orderID in (select orderID from @tblOrders)

      select 'dataset OrderDetails.Item relate orderID to Orders.orderID' _shp
      select *
       from [Order Details]
       where orderid in (select orderID from @tblOrders)
   end
go

And here are the results (wider date ranges produce a lot more data):

<?xml version="1.0" encoding="utf-8"?>
<Employee EmployeeID="6" LastName="Suyama" FirstName="Michael"...>
   <Notes><![CDATA[Michael is a graduate of...]]></Notes>
   <Customer CustomerID="GOURL" CompanyName="Gourmet Lanchonetes"...>
      <Order OrderID="10423" EmployeeID="6" OrderDate="1/23/1997"...>
         <Item ProductID="31" UnitPrice="10.0000" Quantity="14" Discount="0" />
         <Item ProductID="59" UnitPrice="44.0000" Quantity="20" Discount="0" />
      </Order>
   </Customer>
   <Customer CustomerID="LAMAI" CompanyName="La maison d'Asie"...>
      <Order OrderID="10425" EmployeeID="6" OrderDate="1/24/1997"...>
         <Item ProductID="55" UnitPrice="19.2000" Quantity="10" Discount="0.25" />
         <Item ProductID="76" UnitPrice="14.4000" Quantity="20" Discount="0.25" />
      </Order>
   </Customer>
</Employee>

As in the prior example, records were mapped to elements and columns were mapped to attributes of the same name, except for the SQL text-typed column “Notes”, which was mapped to a CDATA block within a child element in order to preserve whitespace and minimize character escaping.

Let’s take a closer look at the embedded instructions.

   select 'dataset root.Employee' _shp

   select ... from employees where ...
   select 'dataset Customers.Customer relate to root' _shp
   select * from customers where ...
   select 'dataset Orders.Order relate customerID to Customers.customerID' _shp
   select * from orders where ...
   select 'dataset OrderDetails.Item relate orderID to Orders.orderID' _shp
   select * from [Order Details] where ...

Notice how the selection of each data set (SELECT [fields] FROM

is preceded by an instruction (SELECT

‘dataset...’ _shp).  These instructions tell the shape interpreter what

is coming next and where to put it. 

Here’s what the instructions mean:

Name the following result set “root” and represent each

record within it as an <Employee> element.

Name the following result set “Customers” and represent each

record within it a <Customer> element.  Make the <Customer>

elements appear as child elements of the <Employee> elements produced by

the “root” result set.  In this case, there is only one <Employee>

element because we are selecting by primary key.  If there were more than one,

the <Customer> elements would be repeated as child elements of each.

Name the following result set

“Orders” and represent each record within it as an <Order> element.  Make

the <Order> elements appear as child elements of the <Customer>

elements produced by the “Customers” result set.  The <Order> elements

will be filtered such that only those orders with matching customerID fields

appear as children of each <Customer> element.  When rendering to XML,

the shape interpreter will not render the customerID attribute of the

<Order> element, since it is implicit in the XML structure.

Name the following result set “OrderDetails” and represent each

record within it as an <Item> element.  Make the <Item> elements

appear as child elements of the <Order> elements produced by the “Orders”

result set, grouped by orderID.

Note that any Order records that do not match a selected

Customer record will not appear in the XML.  Nor will any OrderDetails records

appear that do not match a selected Order.  In other words, if the where

clauses (in orange) were omitted, the XML produced would be identical, but the

amount of data loaded by SQL Server and passed to the interpreter would

increase dramatically.

Because this error is not detectable in the output, except

through degraded performance, it can be difficult to diagnose.  It is always a

good idea to test your shapes in Query Analyzer to verify that the data sets

are appropriately filtered.  It is also possible that a future version of the

interpreter could detect this condition and produce a warning.

While this shape is intended to be representative of actual,

useful shapes, it does not fully exercise the shape grammar, which provides

fine controls over the mapping and formatting of records and columns.  Simple

options are available to:

a.       wrap

record elements in a grouping element.  For example, <Customer> elements

could be wrapped by a <Customers> element.

b.      render

all columns as child elements

c.       render

an individual column as an attribute, child element, free text, CDATA block or

nested XML

d.      format

numeric and date columns using Excel-like formatting strings

e.       omit

a column from the XML output

To get a better sense of the possibilities, let’s examine

the full grammar.

Shape Grammar