Enhanced FOR XML

When I need to obtain an XML query result from my database, FOR XML is usually the first solution I try. Introduced in SQL Server 2000, FOR XML provides three modes—Raw, Auto, and Explicit—for formatting the XML query result. (For details about these modes, see Dave Hulse's August 2000 .NET Developer Perspectives column, "The Three Modes of SQL Server XML," InstantDoc ID 9790.) Auto mode is generally my first choice because it provides a good balance between complexity and the flexibility I need to generate the XML result I want. But Auto mode has several limitations that I frequently encounter that force me to either write a more complicated Explicit-mode query or use Extensible Style Language Transformations (XSLT) or custom code to post-process the query result on my middle-tier or client machine. For example, if you need to include data from two or more tables within the same parent element in the XML result, Auto mode won't work for you. Auto mode also can't generate an XML result that contains a mix of attributes and elements to represent your data. But with the new XML data type in SQL Server 2005, formerly code-named Yukon, you'll have another alternative.

In SQL Server 2005 Beta 1, Microsoft makes XML a full-fledged data type (as I described in my December 2003 column, "Yukon's XML Data Type," InstantDoc ID 40482). With SQL Server 2005, you can store XML-typed columns in your database, declare XML variables, and pass XML parameters. You can query an XML-typed column just as you would any other column, and you can include XML-typed columns in FOR XML queries along with natively typed columns (e.g., int, float, double, datetime). SQL Server 2005 will combine the XML and natively typed data from these columns into a single XML result.

SQL Server 2005 also extends the FOR XML clause with a new directive. The TYPE directive causes a FOR XML query to return one row with a single XML-typed column that contains the XML your query returned. SQL Server 2005 lets you combine the TYPE directive with the new support for XML-typed columns to write subqueries that return XML by using the FOR XML clause. Now let's look at an example of how you can use these new capabilities to quickly and easily write queries that produce XML results.

A Common Example

Suppose you want to produce an XML document that contains a list of orders for each of your customers. Each order should contain a list of items in the order, including the product name and the quantity ordered for each item. Figure 1 shows an example of such an XML document.

Using the Northwind sample database, you could write a query to construct a document similar to the one in Figure 1 by joining the Customers, Orders, Order Details, and Products tables. Listing 1 shows an example of such a query. If you run this query, you'll get the XML result that Figure 2 shows. The XML is similar to Figure 1's desired result, but notice that the Quantity and ProductName are represented as separate elements—Item and Products, respectively. This separation occurs because FOR XML Auto creates subelements that use the name of the table that contains the columns you specified in the SELECT clause, in the order of their occurrence. In Listing 1's query, FOR XML creates a Customers element for the CustomerID column, an Orders element for the OrderDate column, an Item element (because the query uses an AS clause to rename the Order Details table) for the Quantity column, and a Products element for the ProductName column. But Figure 2's output isn't exactly what you need because the ProductName element is nested inside the Quantity element.

If you're using SQL Server 2000's FOR XML clause, you need to write an Explicit-mode query like the one that Listing 2 shows to obtain Figure 1's XML document. I won't explain how this query works; you can learn more about Explicit-mode queries from SQL Server 2000 Books Online (BOL) or my May 2003 column, "Querying Open Content" (InstantDoc ID 38252).

I include Listing 2's query here to illustrate Explicit-mode queries' disadvantages compared with Auto-mode queries. Explicit-mode queries are much more complicated to write than Listing 1's Auto-mode query. You need a deep knowledge of the specialized column-naming conventions and row ordering SQL Server requires to build the desired XML result from the data retrieved by the query. As a result, Explicit-mode queries take more of your valuable time to write and debug than Auto-mode queries. So, it's to your advantage to write Auto-mode queries when you can. And SQL Server 2005 makes writing Auto-mode queries possible more often.

FOR XML Subqueries

Now let's look at the nested Auto-mode query in Listing 3, which uses SQL Server 2005's FOR XML TYPE directive. Unlike Listing 1's query, this query uses a correlated subquery (on OrderID) to obtain information about the items that belong to each order, including the ProductName and Quantity. You need to use two levels of nested subqueries to hide the table names from the result. Recall that Auto-mode queries create elements by using the table names for the columns in the SELECT clause. If you wrote the query with only one level of nested subquery, the Auto-mode query would generate an OrderDetails element that has a nested Products element, which isn't what you want. The second nested query lets you create a virtual table named Item by using an AS clause that contains both columns. The nested Auto-mode query then produces the desired result, with Quantity and ProductName below the same root element, Item.

The nested Auto-mode query also specifies the TYPE directive, so the query returns the result as a single anonymous XML-typed value. This value is then returned as an XML-typed column from the top-level query along with the CompanyName and OrderDate columns. Executing the top-level query creates the Customer and Order elements, then adds the XML to the Order element to produce the desired result.

Another benefit of using subqueries is the ability to generate XML results that contain data that's represented as elements and attributes. The ELEMENTS directive causes a FOR XML query to represent the column data as elements rather than as attributes, the default representation. You can use the ELEMENTS directive in any subquery, giving you even greater flexibility to format the XML result.

More Flexible XML Results

There are still limits to the types of XML formats a FOR XML Auto query can produce. But as the preceding example shows, SQL Server 2005's new technology lets you use Auto-mode queries more often to save you valuable programming time and reduce the complexity of your queries.