Using FOR XML EXPLICIT
For still greater control over the structure of your XML results, use EXPLICIT mode. EXPLICIT mode enables you to design the nesting hierarchy of your resulting XML elements in a precise fashion. But be forewarned: EXPLICIT mode adds a bit of complexity to the query.
If you are like me and try to get things coded in the simplest way possible, you probably won't use EXPLICIT mode too often. Sometimes, however, EXPLICIT mode is the best way to do the job. For example, you might want to combine column results from multiple tables into a single XML element without being tied to the flat XML produced by RAW mode or to the select-list-ordered structure produced by AUTO mode. Or you might need a combination of both attribute and element-centric XML.
The first requirement for queries that use EXPLICIT mode is that the select statement needs to be written in such a way as to produce a rowset that is in (what Microsoft calls) universal table format. This format makes your table structure XML-friendly by providing columns that indicate parent-child relationships for the selected rows. Listing 41.5 provides an example of a query that returns a rowset in universal table format, and Table 41.1 shows the rowset that was generated by the query in Listing 41.5.
Listing 41.5 A Query That Generates a Rowset in Universal Table Format
SELECT 1 AS Tag, NULL AS Parent, C.ContactName AS [Customer!1!CN], NULL AS [Order!2!ID] FROM Customers C WHERE C.CustomerID = 'ANTON' UNION ALL SELECT 2 AS Tag, 1 AS Parent, NULL, O.OrderID FROM Customers C JOIN Orders O ON C.CustomerID = O.CustomerID WHERE C.CustomerID = 'ANTON' ORDER BY [Order!2!ID]
Table 41.1. The Rowset Generated by the Query in Listing 41.5
If you add FOR XML EXPLICIT to the query in Listing 41.5, the resulting XML is as follows :
<Customer CN="Antonio Moreno"> <Order ID="10365"/> <Order ID="10507"/> <Order ID="10535"/> <Order ID="10573"/> <Order ID="10677"/> <Order ID="10682"/> <Order ID="10856"/> </Customer>
The three guidelines for EXPLICIT queries are as follows:
The first two columns of every select statement in EXPLICIT queries must be named Tag and Parent and be of int datatype.
The value of Parent in every row must be equal to a value of Tag in a preceding row. This tells the parser which rows produce parent elements and which become child elements.
Only one row can have a Parent field value of NULL , indicating that the elements produced by the rows in this select statement are top-level elements ”they need no Parent element. (Also remember that a row must not have a Tag value equal to its Parent value.)
The universal table has an implied self-referential constraint between Parent and Tag. Every row whose Parent field value is 1 is a child row of the row whose Tag value is 1, and so on. The XML produced reflects this heritage by nesting Tags under their respective Parents.
The first select statement of EXPLICIT mode queries performs a few important functions. First, it establishes the top-most XML elements because its Parent is NULL and its Tag field is 1. All other selected columns that result in a non-null value are mapped as either attributes or child elements (depending on the value of Directive, explained in one of the following bullets) of the elements produced by these rows. Note the special syntax used to alias them:
[ ElementName ! TagNumber ! AttributeName ! Directive ]
The parts of syntax in this alias are as follows:
In the first select statement in Listing 41.5, the fourth column NULL AS [Order!2!ID] establishes that an element at Tag level 2 named Orders will have an attribute ID that must be populated in the fourth column of the select statement where Tag is 2.
Columns in the second (and any subsequent ) select statement do not use the special aliasing syntax. Here, only the values needed to populate columns aliased with TagNumber 2 (or greater, in any subsequent select statements) are selected.
Note how the selected null values correspond to the non-null columns selected in the first statement, and vice-versa. This will help you remember which select statement needs to produce which attributes.
Take, for example, the third column ( NULL ) in the second select statement. It indicates that rows produced here must leave this space empty because the third column in the first select already has a non-null value (produced by C.CustomerName ). You can see how these select statements work in parallel, leaving empty columns in their rows where values are to be produced by parent or child rows.
Looking at the null values in the universal table, you can see that this is true. Remembering this will also help you be sure you select the same number of columns in all your statements, and that the union will succeed.
The final line specifies the ordering of the produced elements. The best way to understand how the ORDER BY clause works is to remove the FOR XML EXPLICIT clause and look at the resultset. Remember: Parent elements (rows) must precede child elements (rows). Order your rows by the value of TagNumber with this in mind.