Parameters 

In EXPLICIT mode, the first two columns in the SELECT statement must be named Tag and Parent, respectively. Tag and Parent are metadata columns, and their values are used to determine the parent-child relationship, or nesting, of the elements in the XML document that is returned by the query.

• Tag column    This is the first column specified in the select list. The Tag column stores the tag number of the current element. Permitted values for tag numbers are 1 to 255.

• Parent column    This column stores the tag number for the parent of the current element. If the value in this column is NULL, the row is placed at the top level of the XML hierarchy.

Adding data columns to the query 

In addition to the Tag and Parent columns, the query must also contain one or more data columns. The names of these data columns control how the columns are interpreted during tagging. Each column name is split into fields separated by an exclamation mark (!). The following fields can be specified for data columns:

ElementName!TagNumber!AttributeName!Directive

ElementName    the name of the element. For a given row, the name of the element generated for the row is taken from the ElementName field of the first column with a matching tag number. If there are multiple columns with the same TagNumber, the ElementName is ignored for subsequent columns with the same TagNumber. In the example above, the first row generates an element called <first_name>.

TagNumber    the tag number of the element. For a row with a given tag value, all columns with the same value in their TagNumber field will contribute content to the element that corresponds to that row.

AttributeName    specifies that the column value is an attribute of the ElementName element. For example, if a data column had the name prod_id!1!color, then color would appear as an attribute of the <prod_id> element.

Directive    this optional field allows you to control the format of the XML document further. You can specify any one of the following values for Directive:

Writing an EXPLICIT mode query 

Suppose you want to write a query using FOR XML EXPLICIT that generates the following XML document:

<employee emp_id='129'>
   <customer cust_id='107' region='Eastern'/>
   <customer cust_id='119' region='Western'/>
   <customer cust_id='131' region='Eastern'/>
</employee>

<employee emp_id='195'>
   <customer cust_id='109' region='Eastern'/>
   <customer cust_id='121' region='Central'/>
</employee>

You do this by writing a SELECT statement that returns the following result set in the exact order specified, and then appending FOR XML EXPLICIT to the query.

When you write your query, only some of the columns for a given row become part of the generated XML document. A column is included in the XML document only if the value in the TagNumber field (the second field in the column name) matches the value in the Tag column.

In the example, the third column is used for the two rows that have the value 1 in their Tag column. In the fourth and fifth columns, the values are used for the rows that have the value 2 in their Tag column. The element names are taken from the first field in the column name. In this case, <employee> and <customer> elements are created.

The attribute names come from the third field in the column name, so an emp_id attribute is created for <employee> elements, while cust_id and region attributes are generated for <customer> elements.

The following steps explain how to construct the FOR XML EXPLICIT query that generates an XML document similar to the one found at the beginning of this section using the sample database.

To write a FOR XML EXPLICIT query

1. Write a SELECT statement to generate the top-level elements.

   In this example, the first SELECT statement in the query generates the <employee> elements. The first two values in the query must be the Tag and Parent column values. The <employee> element is at the top of the hierarchy, so it is assigned a Tag value of 1, and a Parent value of NULL.

   Note  If you are writing an EXPLICIT mode query that uses a UNION, then only the column names specified in the first SELECT statement are used. Column names that are to be used as element or attribute names must be specified in the first SELECT statement because column names specified in subsequent SELECT statements are ignored.

   To generate the <employee> elements for the table above, your first SELECT statement is as follows:

   SELECT
         1       AS tag,
         NULL    AS parent,
         emp_id  AS [employee!1!emp_id],
         NULL    AS [customer!2!cust_id],
         NULL    AS [customer!2!region]
   FROM employee

2. Write a SELECT statement to generate the child elements.

   The second query generates the <customer> elements. Because this is an EXPLICIT mode query, the first two values specified in all the SELECT statements must be the Tag and Parent values. The <customer> element is given the tag number 2, and because it is a child of the <employee> element, it has a Parent value of 1. The first SELECT statement has already specified that emp_id, cust_id, and region are attributes.

   SELECT
         2,
         1,
         emp_id,
         cust_id,
         region
   FROM employee KEY JOIN sales_order

3. Add a UNION ALL to the query to combine the two SELECT statements together:

   SELECT
         1       AS tag,
         NULL    AS parent,
         emp_id  AS [employee!1!emp_id],
         NULL    AS [customer!2!cust_id],
         NULL    AS [customer!2!region]
   FROM employee
   UNION ALL

   SELECT
         2,
         1,
         emp_id,
         cust_id,
         region
   FROM employee KEY JOIN sales_order

4. Add an ORDER BY clause to specify the order of the rows in the result. The order of the rows is the order that is used in the resulting document.

   SELECT
         1       AS tag,
         NULL    AS parent,
         emp_id  AS [employee!1!emp_id],
         NULL    AS [customer!2!cust_id],
         NULL    AS [customer!2!region]
   FROM employee
   UNION ALL

   SELECT
         2,
         1,
         emp_id,
         cust_id,
         region
   FROM employee KEY JOIN sales_order
   ORDER BY 3, 1
   FOR XML EXPLICIT

Using the element directive 

If you wish to generate sub-elements rather than attributes, you can add the element directive to the query, as follows:

SELECT
        1          tag,
        NULL       parent,
        emp_id     [emp!1!id!element],
        emp_fname  [emp!1!name!element],
        NULL       [order!2!date!element],
        NULL       [dept!3!name!element]
FROM employee

UNION ALL
SELECT
        2,
        1,
        emp_id,
        NULL,
        order_date,
        NULL
FROM employee KEY JOIN sales_order
UNION ALL

SELECT
        3,
        1,
        emp_id,
        NULL,
        NULL,
        dept_name
FROM employee e JOIN department d
   ON e.dept_id=d.dept_id
ORDER BY 3, 1
FOR XML EXPLICIT

You get the following result from this query:

<emp>
   <id>102</id>
   <name>Fran</name>
   <dept>
      <name>R &amp; D</name>
   </dept>
</emp>

<emp>
   <id>105</id>
   <name>Matthew</name>
   <dept>
      <name>R &amp; D</name>
   </dept>
</emp>

<emp>
   <id>129</id>
   <name>Philip</name>
   <order>
      <date>2000-07-24</date>
   </order>
   <order>
      <date>2000-07-13</date>
   </order>
   <order>
      <date>2000-06-24</date>
   </order>
   ...
   <dept>
      <name>Sales</name>
   </dept>
</emp>
...

Using the hide directive 

In the following query, the employee ID is used to order the result, but the employee ID does not appear in the result because the hide directive is specified:

SELECT
        1           tag,
        NULL        parent,
        emp_id      [emp!1!id!hide],
        emp_fname   [emp!1!name],
        NULL        [order!2!date],
        NULL        [dept!3!name]
FROM employee
UNION ALL

SELECT
        2,
        1,
        emp_id,
        NULL,
        order_date,
        NULL
FROM employee KEY JOIN sales_order
UNION ALL

SELECT
        3,
        1,
        emp_id,
        NULL,
        NULL,
        dept_name
FROM employee e JOIN department d
   ON e.dept_id=d.dept_id
ORDER BY 3, 1
FOR XML EXPLICIT

This query returns the following result:

<emp name="Fran">
   <dept name="R &amp; D"/>
</emp>
<emp name="Matthew">
   <dept name="R &amp; D"/>
</emp>

<emp name="Philip">
   <order date="2000-07-24"/>
   <order date="2000-07-13"/>
   <order date="2000-06-24"/>
   <order date="2000-06-08"/>
   ...
   <dept name="Sales"/>
</emp>

<emp name="Julie">
   <dept name="Finance"/>
</emp>
...

Using the xml directive 

Using the cdata directive 

The following query uses the cdata directive to return the customer name in a CDATA section:

SELECT
        1               AS tag,
        NULL            AS parent,
        id              AS [product!1!id],
        description     AS [product!1!cdata]
FROM product
FOR XML EXPLICIT

The result produced by this query lists the description for each product in a CDATA section. Data contained in the CDATA section is not quoted:

<product id="300">
   <![CDATA[Tank Top]]>
</product>
<product id="301">
   <![CDATA[V-neck]]>
</product>

<product id="302">
   <![CDATA[Crew Neck]]>
</product>
<product id="400">
   <![CDATA[Cotton Cap]]>
</product>
...