Excerpt from Professional ASP.NET 2.0 Special Edition
Generating Custom XML from SQL 2005
by Scott Hanselman
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
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.
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" xmlns="urn:hanselman.com/northwind"> <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).