In the last two posts we examined FOR XML AUTO and FOR XML RAW. It is time for us to examine the most hated FOR XML directive: EXPLICIT.
EXPLICIT is the most powerful directive that you can use with FOR XML, which gives you total control over the shape of XML being generated. Using EXPLICIT mode, you can generate XML documents with almost any structure and shape. However, the syntax of FOR XML EXPLICIT is quite complex and that makes many people hate this handsome young guy!
FOR XML AUTO, RAW and PATH can be used along with a SELECT query to transform the result of the query into an XML document. You can add FOR XML AUTO, RAW or PATH to 'almost' any SELECT query and generate XML documents. However, FOR XML EXPLICIT expects the output of the relational query to be in a certain format. It expects a few mandatory elements in the query output and expects certain relationships established among the rows tha represents an XML hierarchy.
FOR XML EXPLICIT expects the output of the query to be in a certain format. The structure of the resultset returned by the query determines the structure of the XML document. For example, take a look at the XML fragment given below:
<Customer CustNo="J001">
<LineItems ItemNo="D001" Qty="1" />
<LineItems ItemNo="Z001" Qty="1" />
</Customer>
To generate an XML document in the structure given above (using EXPLICIT) the SELECT query should return a result set as given below.
Tag Parent Customer!1!CustNo LineItems!2!ItemNo LineItems!2!Qty
--- ------ ----------------- ------------------ ---------------
1 NULL J001 NULL NULL
2 1 NULL D001 1
2 1 NULL Z001 1
The Tag and Parent are mandatory columns. FOR XML EXPLICIT will generate an error if those two columns are not present in the resultset. Tag and Parent together establishes a parent-child relationship that represents the hierarchy of the XML document we wanted to create.
The other columns contain the data that need to be transformed into the XML document. The columns need to be named in a certain fashion. In the example given above, each column has three part names, separated by an exclamation mark. The first part of the column name indicates the element name in which an attribute or element needs to be created with the value stored in the current column. The second part indicates the level where the value of the current column should be placed in the XML hierarchy. The third part specifies the name of the attribute or element to be generated.
A simple FOR XML EXPLICIT query
Let us now write a FOR XML EXPLICIT query that generates the XML output we saw earlier. Use the script given in this post to generate the sample tables and populate them with sample data before running the examples in this post.
SELECT
1 AS Tag,
NULL AS Parent,
CustomerNumber AS 'Customer!1!CustNo',
NULL AS 'LineItems!2!ItemNo',
NULL AS 'LineItems!2!Qty'
FROM OrderHeader o
INNER JOIN Customers c ON o.CustomerID = c.CustomerID
AND o.OrderID = 1
UNION ALL
SELECT
2 AS Tag,
1 AS Parent,
NULL,
i.ItemNumber,
o.Quantity
FROM Items i
INNER JOIN OrderDetails o ON i.ItemID = o.ItemID
AND o.OrderID = 1
/*
Tag Parent Customer!1!CustNo LineItems!2!ItemNo LineItems!2!Qty
--- ------ ----------------- ------------------ ---------------
1 NULL J001 NULL NULL
2 1 NULL D001 1
2 1 NULL Z001 1
*/
FOR XML EXPLICIT
/*
<Customer CustNo="J001">
<LineItems ItemNo="D001" Qty="1" />
<LineItems ItemNo="Z001" Qty="1" />
</Customer>
*/
Generating elements instead of attributes
By default, FOR XML EXPLICIT generates values as attributes. You can instruct SQL Server to generate elements by specifying ELEMENTS in the column name. The following example generates Customer Number as an element.
SELECT
1 AS Tag,
NULL AS Parent,
CustomerNumber AS 'Customer!1!CustNo!ELEMENT',
NULL AS 'LineItems!2!ItemNo',
NULL AS 'LineItems!2!Qty'
FROM OrderHeader o
INNER JOIN Customers c ON o.CustomerID = c.CustomerID
AND o.OrderID = 1
UNION ALL
SELECT
2 AS Tag,
1 AS Parent,
NULL,
i.ItemNumber,
o.Quantity
FROM Items i
INNER JOIN OrderDetails o ON i.ItemID = o.ItemID
AND o.OrderID = 1
FOR XML EXPLICIT
/*
<Customer>
<CustNo>J001</CustNo>
<LineItems ItemNo="D001" Qty="1" />
<LineItems ItemNo="Z001" Qty="1" />
</Customer>
*/
Shaping the XML output
Let us see a bit more complicated XML shaping requirement. Assume that we need the XML output in a slightly different shape. Here is the new shape of the XML output.
<Orders>
<Order CustNo="J001" OrderNo="SO101">
<LineItems ItemNo="D001" Qty="1" />
<LineItems ItemNo="Z001" Qty="1" />
</Order>
<Order CustNo="J001" OrderNo="SO102">
<LineItems ItemNo="D001" Qty="1" />
</Order>
</Orders>
Let us write a FOR XML EXPLICIT query to generate an XML document with the structure given above. Here is the first attempt.
SELECT
1 AS Tag,
NULL AS Parent,
CustomerNumber AS 'Order!1!CustNo',
OrderNumber AS 'Order!1!OrderNo',
NULL AS 'LineItems!2!ItemNo',
NULL AS 'LineItems!2!Qty'
FROM OrderHeader o
INNER JOIN Customers c ON o.CustomerID = c.CustomerID
UNION ALL
SELECT
2 AS Tag,
1 AS Parent,
NULL,
NULL,
i.ItemNumber,
o.Quantity
FROM Items i
INNER JOIN OrderDetails o ON i.ItemID = o.ItemID
FOR XML EXPLICIT, ROOT('Orders')
/*
<Orders>
<Order CustNo="J001" OrderNo="SO101" />
<Order CustNo="J001" OrderNo="SO102">
<LineItems ItemNo="D001" Qty="1" />
<LineItems ItemNo="Z001" Qty="1" />
<LineItems ItemNo="D001" Qty="1" />
</Order>
</Orders>
*/
Well, that is very close to what we needed, but the LineItems are not placed under the correct Order elements. The reason for this is the order of rows in the result set. If you remove the FOR XML EXPLICIT from the query, here is what you will get.
Tag Parent Order!1!CustNo Order!1!OrderNo LineItems!2!ItemNo LineItems!2!Qty
--- ------ -------------- --------------- ------------------ ---------------
1 NULL J001 SO101 NULL NULL
1 NULL J001 SO102 NULL NULL
2 1 NULL NULL D001 1
2 1 NULL NULL Z001 1
2 1 NULL NULL D001 1
Note that the last three rows (with Tag 2) which generate the LineItems elements appear together in the resultset. FOR XML EXPLICIT generates the elements in the same order as they appear in the result set. So to fix the above problem, we need to change the order in which the rows appear in the resultset. Here is the correct order of the rows.
Tag Parent Order!1!CustNo Order!1!OrderNo LineItems!2!ItemNo LineItems!2!Qty
--- ------ -------------- --------------- ------------------ ---------------
1 NULL J001 SO101 NULL NULL
2 1 NULL NULL D001 1
2 1 NULL NULL Z001 1
1 NULL J001 SO102 NULL NULL
2 1 NULL NULL D001 1
Based upon this understanding, let us go ahead and write a query that returns the results in the order given above. To order the rows correctly, we might need a sorting column. The example given below generates a sorting column on-the-fly and uses it for sorting.
SELECT
1 AS Tag,
NULL AS Parent,
CustomerNumber AS 'Order!1!CustNo',
OrderNumber AS 'Order!1!OrderNo',
REPLACE(STR(OrderID,4) + STR(0,4),' ','0') AS 'Order!1!Sort!HIDE',
NULL AS 'LineItems!2!ItemNo',
NULL AS 'LineItems!2!Qty'
FROM OrderHeader o
INNER JOIN Customers c ON o.CustomerID = c.CustomerID
UNION ALL
SELECT
2 AS Tag,
1 AS Parent,
NULL,
NULL,
REPLACE(STR(OrderID,4) + STR(OrderDetailID,4),' ','0'),
i.ItemNumber,
o.Quantity
FROM Items i
INNER JOIN OrderDetails o ON i.ItemID = o.ItemID
ORDER BY 'Order!1!Sort!HIDE'
FOR XML EXPLICIT, ROOT('Orders')
/*
<Orders>
<Order CustNo="J001" OrderNo="SO101">
<LineItems ItemNo="D001" Qty="1" />
<LineItems ItemNo="Z001" Qty="1" />
</Order>
<Order CustNo="J001" OrderNo="SO102">
<LineItems ItemNo="D001" Qty="1" />
</Order>
</Orders>
*/
Note the usage of 'HIDE' attribute on Sort column. It is an indicator for SQL Server not to include the column in the XML output being generated.
I will come up with a follow-up post on FOR XML EXPLICIT covering a few interesting scenarios where FOR XML EXPLICIT can be used to solve a given problem.
See also: