Using the SQL Server FOR XML Clause
With a standard SQL SELECT statement, you submit your SELECT statement to the
database for execution and get results back in the form of rows. SQL Server extends the
SELECT statement to allow you to query the database and get results back as XML. To
do this, you add a FOR XML clause to the end of your SELECT statement. The FOR
XML clause specifies that SQL Server is to return results as XML.
The FOR XML clause has the following syntax:
FOR XML {RAW | AUTO | EXPLICIT}
[, XMLDATA]
[, ELEMENTS]
[, BINARY BASE64]

The RAW, AUTO, and EXPLICIT keywords indicate the XML mode. Table 16.1
shows
a description of the keywords used in the FOR XML clause. In the next sections, you'll
examine some examples of the use of the FOR XML clause.
Table 16.1: FOR XML KEYWORDS
KEYWORD DESCRIPTION
RAW Specifies that each row in your result set is returned as an XML <row>
element. The column values for each row in the result set become
attributes of the <row> element.
AUTO Specifies that each row in the result set is returned as an XML element
The name of the table is used as the name of the tag in the row elements.
EXPLICIT Indicates your SELECT statement specifies a parent-child relationship.
This relationship is then used by SQL Server to generate XML with the
appropriate nested hierarchy.
XMLDATA Specifies that the XML schema is to be included in the returned XML.
ELEMENTS Specifies that the column values are returned as subelements of the row;
otherwise the columns are returned as attributes of the row. You can use
this option only with the AUTO mode.


Figure 16.1: Running a SELECT statement containing a FOR XML RAW clause in
Query Analyzer

Note By default, the maximum number of characters displayed by Query Analyzer per
column is 256. Any results longer than 256 characters will be truncated. For the
examples in this section, you'll need to increase the maximum number of characters
to 8,192. To do this, you select Tools ➣ Options in Query Analyzer and set the
Maximum Characters Per Column field to 8,192.
Here's the XML line returned by the example, which I copied from Query Analyzer and
added some return characters to make it easier to read:
<row
CustomerID="ALFKI"
CompanyName="Alfreds Futterkiste"
ContactName="Maria Anders"/>
<row
CustomerID="ANATR"
CompanyName="Ana Trujillo Emparedados y helados"
ContactName="Ana Trujillo"/>
<row
CustomerID="ANTON"
CompanyName="Antonio Moreno Taquería"
ContactName="Antonio Moreno"/>
Notice that each customer is placed within a <row> tag. Also, the column values appear
as attributes within each row; for example, in the first row, the CustomerID attribute is
ALFKI.
Using the AUTO Mode
You use the AUTO mode to specify that each row in the result set is returned as an XML
element. The name of the table is used as the name of the tag in the row elements.
Listing 16.2

When using the EXPLICIT mode, you must provide at least two SELECT statements.
The first SELECT specifies the parent row (or rows), and the second specifies the child
rows. The rows retrieved by the two SELECT statements are related through special
columns named Tag and Parent. Tag specifies the numeric position of the element, and
Parent specifies the Tag number of the parent element (if any).
Let's consider an example that uses two SELECT statements. The first SELECT retrieves
the CustomerID, CompanyName, and ContactName for the row from the Customers table
that has a CustomerID of ALFKI. The second SELECT additionally retrieves the
OrderID and OrderDate from the row in the Orders table that also has a CustomerID of
ALFKI. The first SELECT statement is as follows:
SELECT
1 AS Tag,
0 AS Parent,
CustomerID AS [Customer!1!CustomerID],
CompanyName AS [Customer!1!CompanyName],
ContactName AS [Customer!1!ContactName],
NULL AS [Order!2!OrderID!element],
NULL AS [Order!2!OrderDate!element]
FROM Customers
WHERE CustomerID = 'ALFKI'
The Tag column specifies the numeric position of the row in the XML hierarchy. The
Parent column identifies the parent, which is 0 in the previous SELECT statement; that's
because this row is the parent, or root, in the XML hierarchy.

Note You can also use a Tag value of NULL to indicate the root.
The CustomerID, CompanyName, and ContactName columns in the previous SELECT
are supplied an alias using the AS keyword, followed by a string that uses the following
format:
[elementName!tag!attributeName!directive]
where

directive, your column type must be varchar, nvarchar, text, or