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]
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.
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.