15.1.1.FOR XML EXPLICIT Mode

This mode gives the developer the most control over the generated result tree but requires a verbose query formulation. Each row must begin with two integer columns, the first identifying the element represented by the row and the second the parent element type of this element. Consider:

select 1 as tag, null as parent,
       "CategoryID" as [category!1!cid],
       "CategoryName" as [category!1!name],
       NULL as [product!2!pid],
       NULL as [product!2!name!element]
from "Demo".."Categories"
union all
select 2, 1, "category" ."CategoryID", NULL, "ProductID", "ProductName"
    from "Demo".."Categories" "category", "Demo".."Products" as "product"
    where "product"."CategoryID" = "category"."CategoryID"
order by [category!1!cid], 5
for xml explicit;

This query makes a two level tree where Categories have Product children. The selection in the first UNION term specifies the element types in the result set. The two first columns, TAG and PARENT are required in all EXPLICIT queries. Subsequent columns have an extended AS declaration that specifies which element they belong to, what that element is called in XML and what the column will be called. A row where TAG has a value of 1 will pick the columns which has [xxx!1!yyy] as their alias; rows with a TAG of 2 will pick columns with an alias with [xxx!2!yyy] and so on.

If consecutive rows have a different TAG but the same PARENT, these will be siblings of different types. This possibility does not exist with the other FOR XML modes.

If the PARENT is 0 or NULL, then any previously open elements in the result are closed and the element of the row becomes a top-level element. When PARENT refers to the TAG of a presently open element in the set, all children of that element are closed and the row's element is inserted as the next child of the last element with the TAG equal to the new row's PARENT. All open tags are closed at the end of the result set.

[Note] Note

Since each level of the tree is generated by a different term in the UNION ALL, an ORDER BY will invariably be needed to group the children after their parents. If the parent rows have NULLs in place of the child row's key values, the parent gets sorted first because NULL collates first.