Use EXPLICIT Mode with FOR XML
As described in the topic, Constructing XML Using FOR XML, RAW and AUTO mode do not provide much control over the shape of the XML generated from a query result. However, EXPLICIT mode provides the most flexibility in generating the XML you want from a query result.
The EXPLICIT mode query must be written in a specific way so that the additional information about the required XML, such as expected nesting in the XML, is explicitly specified as part of the query. Depending on the XML you request, writing EXPLICIT mode queries can be cumbersome. You may find that Using PATH Mode with nesting is a simpler alternative to writing EXPLICIT mode queries.
Because you describe the XML you want as part of the query in EXPLICIT mode, you must ensure that the generated XML is well formed and valid.
The EXPLICIT mode transforms the rowset that results from the query execution into an XML document. In order for EXPLICIT mode to produce the XML document, the rowset must have a specific format. This requires that you write the SELECT query to produce the rowset, the universal table, with a specific format so the processing logic can then produce the XML you want.
First, the query must produce the following two metadata columns:
-
The first column must provide the tag number, integer type, of the current element, and the column name must be Tag. Your query must provide a unique tag number for each element that will be constructed from the rowset.
-
The second column must provide a tag number of the parent element, and this column name must be Parent. In this way, the Tag and the Parent column provide hierarchy information.
These metadata column values, together with the information in the column names, are used to produce the XML you want. Note that your query must provide column names in a specific way. Also note that a 0 or NULL in the Parent column indicates that the corresponding element has no parent. The element is added to the XML as a top-level element.
To understand how the universal table generated by a query is processed into generating XML result, assume that you have written a query that produces this universal table:
Note the following about this universal table:
-
The first two columns are Tag and Parent and are meta columns. These values determine the hierarchy.
-
The column names are specified in a certain way, as described later in this topic.
-
In generating the XML from this universal table, the data in this table is partitioned vertically into column groups. The grouping is determined based on the Tag value and the column names. In constructing XML, the processing logic selects one group of columns for each row and constructs an element. The following applies in this example:
-
For Tag column value 1 in the first row, the columns whose names include the same tag number, Customer!1!cid and Customer!1!name, form a group. These columns are used in processing the row, and you may have noticed that the shape of the generated element is <Customer id=... name=...>. Column name format is described later in this topic.
-
For rows with Tag column value 2, columns Order!2!id and Order!2!date form a group that is then used in constructing elements, <Order id=... date=... />.
-
For rows with Tag column value 3, columns OrderDetail!3!id!id and OrderDetail!3!pid!idref form a group. Each of these rows generates an element, <OrderDetail id=... pid=...>, from these columns.
-
-
Note that in generating XML hierarchy, the rows are processed in order. The XML hierarchy is determined as shown in the following:
-
The first row specifies Tag value 1 and Parent value NULL. Therefore, the corresponding element, <Customer> element, is added as a top-level element in the XML.
<Customer cid="C1" name="Janine">
-
The second row identifies Tag value 2 and Parent value 1. Therefore, the element, <Order> element, is added as a child of the <Customer> element.
<Customer cid="C1" name="Janine"> <Order id="O1" date="1/20/1996">
-
The next two rows identify Tag value 3 and Parent value 2. Therefore, the two elements, <OrderDetail> elements, are added as children of the <Order> element.
<Customer cid="C1" name="Janine"> <Order id="O1" date="1/20/1996"> <OrderDetail id="OD1" pid="P1"/> <OrderDetail id="OD2" pid="P2"/> -
The last row identifies 2 as the Tag number and 1 as the Parent tag number. Therefore, another <Order> element child is added to the <Customer> parent element.
<Customer cid="C1" name="Janine"> <Order id="O1" date="1/20/1996"> <OrderDetail id="OD1" pid="P1"/> <OrderDetail id="OD2" pid="P2"/> </Order> <Order id="O2" date="3/29/1997"> </Customer>
-
To summarize, the values in the Tag and Parent meta columns, the information provided in the column names, and the correct ordering of the rows produce the XML you want when you use EXPLICIT mode.
Universal Table Row Ordering
In constructing the XML, the rows in the universal table are processed in order. Therefore, to retrieve the correct children instances associated with their parent, the rows in the rowset must be ordered so that each parent node is immediately followed by its children.
When writing EXPLICIT mode queries, column names in the resulting rowset must be specified by using this format. They provide transformation information including element and attribute names and other additional information, specified by using directives.
This is the general format:
ElementName!TagNumber!AttributeName!Directive
Following is the description of the parts of the format.
The following examples illustrate the use of EXPLICIT mode.
