Using FOR XML EXPLICIT

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

Tag	Parent	Customer!1!CN	Order!2!ID
1	NULL	Antonia Moreno	NULL
2	1	NULL	10365
2	1	NULL	10507
2	1	NULL	10535
2	1	NULL	10573
2	1	NULL	10677
2	1	NULL	10682
2	1	NULL	10856

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:

• Execute at least two select statement s.

• Join them using UNION ALL .

• Order the resulting rows using ORDER BY .

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:

• ElementName ” Defines the name of the produced element. The column C.ContactName , for example, is going to be either an attribute or subelement of Customer elements because its alias is Customer!1!CN .

• TagNumber ” Indicates that the value of the attribute or subelement of the element named in ElementName must get its value from a column in the select statement where Tag is equal to TagNumber. The alias Customer!1!CN thus indicates that Customer elements, for example, will have an attribute CN whose value is populated in select statement number one, where Tag is 1, indicated by the value (1) of TagNumber.

• AttributeName ” Provides a place for you to name the attributes or subelements of ElementName, such as CN .

• Directive ” If Directive is equal to "hide," the attribute specified in AttributeName is not displayed in the elements named ElementName. If Directive is equal to "element," the attribute becomes a child element of ElementName, working in an element-centric fashion similar to FOR XML AUTO, ELEMENTS . (A few other possible values for Directive are available that are fairly technical with respect to XML data encoding and will not be specified here. For more information, consult SQL Server Books Online.)

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.