Generating Custom XML from SQL 2005

There are a number of ways to programmatically generate custom XML from a database. Before reading Professional ASP.NET 2.0 Special Edition, you probably used FOR XML AUTO to generate fairly basic XML and then modified the XML in post processing to meet your needs. This was formerly a very common pattern. FOR XML AUTO was fantastically easy; and FOR XML EXPLICIT, a more explicit way to generate XML, was very nearly impossible to use.

SQL Server 2005 adds the new PATH method to FOR XML that makes arbitrary XML creation available to mere mortals. SQL 2005’s XML support features very intuitive syntax and very clean namespace handling.

Here is an example of a query that returns custom XML. The WITH XMLNAMESPACS commands at the start of the query set the stage by defining a default namespace and using column-style name aliasing to associate namespaces with namespace prefixes. In this example addr: is the prefix for urn:hanselman.com/northwind/address.

use Northwind;
WITH XMLNAMESPACES (
 DEFAULT 'urn:hanselman.com/northwind'
 , 'urn:hanselman.com/northwind/address' as "addr"
)
SELECT CustomerID as "@ID",
       CompanyName,
       Address as "addr:Address/addr:Street",
       City as "addr:Address/addr:City",
       Region as "addr:Address/addr:Region",
       PostalCode as "addr:Address/addr:Zip",
       Country as "addr:Address/addr:Country",
       ContactName as "Contact/Name",
       ContactTitle as "Contact/Title",
       Phone as "Contact/Phone",
       Fax as "Contact/Fax"
FROM Customers
FOR XML PATH('Customer'), ROOT('Customers'), ELEMENTS XSINIL

The aliases using the AS keyword declaratively describe the elements and their nesting relationships, whereas the PATH keyword defines an element for the Customers table. The ROOT keyword defines the root node of the document.

The ELEMENTS keyword, along with XSINIL, describes how you handle null. Without these keywords, no XML element is created for a row’s column that contains null; this absence of data in the database causes the omission of data in the resulting XML document. When the ELMENTS XSINIL combination is present, an element outputs using an explicit xsi:nil syntax such as <addr:Region xsi_nil="true" />.

When you run the example, SQL 2005 outputs an XML document like the one that follows. Note the namespaces and prefixes are just as you defined them.

<Customers xmlns_xsi="http://www.w3.org/2001/XMLSchema-instance"
           xmlns_addr="urn:hanselman.com/northwind/address"
           >
  <Customer ID="ALFKI">
    <CompanyName>Alfreds Futterkiste</CompanyName>
    <addr:Address>
      <addr:Street>Obere Str. 57</addr:Street>
      <addr:City>Berlin</addr:City>
      <addr:Region xsi_nil="true" />
      <addr:Zip>12209</addr:Zip>
      <addr:Country>Germany</addr:Country>
    </addr:Address>
    <Contact>
      <Name>Maria Anders</Name>
      <Title>Sales Representative</Title>
      <Phone>030-0074321</Phone>
      <Fax>030-0076545</Fax>
    </Contact>
  </Customer>
...the rest of the document removed for brevity...

The resulting XML can now be manipulated using an XmlReader or any of the techniques covered in Chapter 13, “Working with XML” of Professional ASP.NET 2.0 Special Edition (Wrox, 2006, ISBN: 0470041781).

More by Author

Get the Free Newsletter!

Subscribe to Developer Insider for top news, trends & analysis

Must Read