Programming Microsoft SQL Server 2005 : FOR XML Commands (part 2) - FOR XML EXPLICIT

FOR XML EXPLICIT

FOR XML EXPLICIT is the most complex but also the most useful and flexible of the three options. It produces XML by constructing a UNION query of the various levels of output elements. So, if again you have the Customer and SalesOrderHeader tables and you want to produce XML output, you must have two SELECT statements with a UNION. If you add the SalesOrderDetail table, you must add another UNION statement and SELECT statement.

As we said, FOR XML EXPLICIT is more complex than its predecessors. For starters, you are responsible for defining two additional columns that establish the hierarchical relationship of the XML: a Tag column that acts as a row’s identifier and a Parent column that links child records to the parent record’s Tag value (similar to EmployeeID and ManagerID). You must also alias all columns to indicate the element, Tag, and display name for the XML output, as shown here. Keep in mind that only the first SELECT statement must enforce these rules because aliases in subsequent SELECT statements in a UNION query are ignored.

Listing 3. Customer and Order Header Data with FOR XML EXPLICIT

USE AdventureWorks GO SELECT TOP 2 -- limits the result rows for demo purposes 1 AS Tag, NULL AS Parent, CustomerID AS [Customer!1!CustomerID], NULL AS [SalesOrder!2!SalesOrderID], NULL AS [SalesOrder!2!OrderDate] FROM Sales.Customer AS Customer UNION ALL SELECT TOP 10 -- limits the result rows for demo purposes 2, 1, Customer.CustomerID, OrderHeader.SalesOrderID, OrderHeader.OrderDate FROM Sales.Customer AS Customer INNER JOIN Sales.SalesOrderHeader AS OrderHeader ON OrderHeader.CustomerID = Customer.CustomerID ORDER BY [Customer!1!CustomerID], [SalesOrder!2!SalesOrderID] FOR XML EXPLICIT

Execute and click the XML hyperlink to see the following results:

<Customer CustomerID="1">
  <SalesOrder SalesOrderID="43860" OrderDate="2001-08-01T00:00:00" />
  <SalesOrder SalesOrderID="44501" OrderDate="2001-11-01T00:00:00" />
  <SalesOrder SalesOrderID="45283" OrderDate="2002-02-01T00:00:00" />
  <SalesOrder SalesOrderID="46042" OrderDate="2002-05-01T00:00:00" />
</Customer>
<Customer CustomerID="2">
  <SalesOrder SalesOrderID="46976" OrderDate="2002-08-01T00:00:00" />
  <SalesOrder SalesOrderID="47997" OrderDate="2002-11-01T00:00:00" />
  <SalesOrder SalesOrderID="49054" OrderDate="2003-02-01T00:00:00" />
  <SalesOrder SalesOrderID="50216" OrderDate="2003-05-01T00:00:00" />
  <SalesOrder SalesOrderID="51728" OrderDate="2003-08-01T00:00:00" />
  <SalesOrder SalesOrderID="57044" OrderDate="2003-11-01T00:00:00" />
</Customer>

This output happens to resemble the output from Listing 2.

FOR XML EXPLICIT allows for some alternative outputs that are not achievable using RAW. For example, you can specify that certain values be composed as elements instead of attributes by including !ELEMENT on the end of the aliased column. Listing 4 demonstrates this usage.

Listing 4. Using FOR XML EXPLICIT

USE AdventureWorks GO --XML EXPLICIT SELECT TOP 2 -- limits the result rows for demo purposes 1 AS Tag, NULL AS Parent, CustomerID AS [Customer!1!CustomerID], NULL AS [SalesOrder!2!SalesOrderID], NULL AS [SalesOrder!2!OrderDate!ELEMENT] --Render as an element FROM Sales.Customer AS Customer UNION ALL SELECT TOP 10 -- limits the result rows for demo purposes 2, 1, Customer.CustomerID, OrderHeader.SalesOrderID, OrderHeader.OrderDate FROM Sales.Customer AS Customer INNER JOIN Sales.SalesOrderHeader AS OrderHeader ON OrderHeader.CustomerID = Customer.CustomerID ORDER BY [Customer!1!CustomerID], [SalesOrder!2!SalesOrderID] FOR XML EXPLICIT

Only one minor change was made (as indicated by the comment). However, this change has a major effect on the final output, as shown here:

<Customer CustomerID="1">
  <SalesOrder SalesOrderID="43860">
    <OrderDate>2001-08-01T00:00:00</OrderDate>
  </SalesOrder>
  <SalesOrder SalesOrderID="44501">
    <OrderDate>2001-11-01T00:00:00</OrderDate>
  </SalesOrder>
  <SalesOrder SalesOrderID="45283">
    <OrderDate>2002-02-01T00:00:00</OrderDate>
  </SalesOrder>
  <SalesOrder SalesOrderID="46042">
    <OrderDate>2002-05-01T00:00:00</OrderDate>
  </SalesOrder>
</Customer>
<Customer CustomerID="2">
  <SalesOrder SalesOrderID="46976">
    <OrderDate>2002-08-01T00:00:00</OrderDate>
  </SalesOrder>
  <SalesOrder SalesOrderID="47997">
    <OrderDate>2002-11-01T00:00:00</OrderDate>
  </SalesOrder>
  <SalesOrder SalesOrderID="49054">
    <OrderDate>2003-02-01T00:00:00</OrderDate>
  </SalesOrder>
  <SalesOrder SalesOrderID="50216">
    <OrderDate>2003-05-01T00:00:00</OrderDate>
  </SalesOrder>
  <SalesOrder SalesOrderID="51728">
    <OrderDate>2003-08-01T00:00:00</OrderDate>
  </SalesOrder>
  <SalesOrder SalesOrderID="57044">
    <OrderDate>2003-11-01T00:00:00</OrderDate>
  </SalesOrder>
</Customer>

					  

Notice that the OrderDate is rendered as a child element of the SalesOrder element. Although EXPLICIT mode could create robust results, it also requires creating even more complex queries to get such results. For example, to add a few more fields from OrderHeader and to add some additional fields from OrderDetail, you would have to write the query shown in Listing 5 using FOR XML EXPLICIT.

Listing 5. Using FOR XML EXPLICIT with added fields

Code View: Scroll / Show All USE AdventureWorks GO --XML EXPLICIT SELECT --TOP 2 -- limits the result rows for demo purposes 1 AS Tag, NULL AS Parent, CustomerID AS [Customer!1!CustomerID], NULL AS [SalesOrder!2!SalesOrderID], NULL AS [SalesOrder!2!TotalDue], NULL AS [SalesOrder!2!OrderDate!ELEMENT], NULL AS [SalesOrder!2!ShipDate!ELEMENT], NULL AS [SalesDetail!3!ProductID], NULL AS [SalesDetail!3!OrderQty], NULL AS [SalesDetail!3!LineTotal] FROM Sales.Customer AS Customer WHERE Customer.CustomerID IN (1, 2) UNION ALL SELECT 2, 1, Customer.CustomerID, OrderHeader.SalesOrderID, OrderHeader.TotalDue, OrderHeader.OrderDate, OrderHeader.ShipDate, NULL, NULL, NULL FROM Sales.Customer AS Customer INNER JOIN Sales.SalesOrderHeader AS OrderHeader ON OrderHeader.CustomerID = Customer.CustomerID WHERE Customer.CustomerID IN (1, 2) UNION ALL SELECT 3, 2, Customer.CustomerID, OrderHeader.SalesOrderID, OrderHeader.TotalDue, OrderHeader.OrderDate, OrderHeader.ShipDate, OrderDetail.ProductID, OrderDetail.OrderQty, OrderDetail.LineTotal FROM Sales.Customer AS Customer INNER JOIN Sales.SalesOrderHeader AS OrderHeader ON OrderHeader.CustomerID = Customer.CustomerID INNER JOIN Sales.SalesOrderDetail AS OrderDetail ON OrderDetail.SalesOrderID = OrderHeader.SalesOrderID WHERE Customer.CustomerID IN (1, 2) ORDER BY [Customer!1!CustomerID], [SalesOrder!2!SalesOrderID] FOR XML EXPLICIT

As you can see, the code has become quite complex and will become increasingly complex as you add additional data to the output. Although this query is perfectly valid in SQL Server 2005, this solution is unacceptable, which leads us to the new and improved FOR XML statement.

FOR XML’s TYPE Option

XML is an intrinsic data type of SQL Server 2005, so we can now automatically cast the XML output from the FOR XML query into an XML data type instance, as opposed to streamed results. You accomplish this by using the TYPE keyword after your FOR XML statement, like this:

USE AdventureWorks
DECLARE @xmlData AS XML

SET @xmlData =
(Select Customer.CustomerID, OrderDetail.SalesOrderID,
   OrderDetail.OrderDate
From Sales.Customer Customer
   inner join Sales.SalesOrderHeader OrderDetail
   on OrderDetail.customerid=Customer.customerid
WHERE Customer.CustomerID<3
ORDER BY Customer.CustomerID
For XML AUTO, TYPE)--Casts to XML type
SELECT @xmlData

This example declares a variable of XML and then sets that variable to a casted result of a FOR XML query using the TYPE statement. The results of this query are shown here for demonstration purposes, but you can use this new XML variable as part of an INSERT statement (to an XML column) or pass it to a stored procedure, as a couple of examples.

<Customer CustomerID="1">
  <OrderDetail SalesOrderID="43860" OrderDate="2001-08-01T00:00:00" />
  <OrderDetail SalesOrderID="44501" OrderDate="2001-11-01T00:00:00" />
  <OrderDetail SalesOrderID="45283" OrderDate="2002-02-01T00:00:00" />
  <OrderDetail SalesOrderID="46042" OrderDate="2002-05-01T00:00:00" />
</Customer>
<Customer CustomerID="2">
  <OrderDetail SalesOrderID="46976" OrderDate="2002-08-01T00:00:00" />
  <OrderDetail SalesOrderID="47997" OrderDate="2002-11-01T00:00:00" />
  <OrderDetail SalesOrderID="49054" OrderDate="2003-02-01T00:00:00" />
  <OrderDetail SalesOrderID="50216" OrderDate="2003-05-01T00:00:00" />
  <OrderDetail SalesOrderID="51728" OrderDate="2003-08-01T00:00:00" />
  <OrderDetail SalesOrderID="57044" OrderDate="2003-11-01T00:00:00" />
  <OrderDetail SalesOrderID="63198" OrderDate="2004-02-01T00:00:00" />
  <OrderDetail SalesOrderID="69488" OrderDate="2004-05-01T00:00:00" />
</Customer>

You can use FOR XML, TYPE in any valid SQL expression. The next example uses the FOR XML, TYPE syntax as an expression in the SELECT statement.

USE AdventureWorks
GO

SELECT
   CustomerID,
   (SELECT SalesOrderID,
                 TotalDue,
                 OrderDate,
                 ShipDate
   FROM Sales.SalesOrderHeader AS OrderHeader
   WHERE CustomerID = Customer.CustomerID
   FOR XML AUTO, TYPE) AS OrderHeaders
FROM Sales.Customer AS Customer
WHERE CustomerID IN (1, 2)
FOR XML AUTO

The results are shown here:

1 <OrderHeader SalesOrderID="43860" TotalDue="14603.7393" OrderDate="2001-08-
01T00:00:00" ShipDate="2001-08-08T00:00:00" />
<OrderHeader SalesOrderID="44501" TotalDue="26128.8674" OrderDate="2001-11-
01T00:00:00" ShipDate="2001-11-08T00:00:00" />
<OrderHeader SalesOrderID="45283" TotalDue="37643.1378" OrderDate="2002-02-
01T00:00:00" ShipDate="2002-02-08T00:00:00" />
<OrderHeader SalesOrderID="46042" TotalDue="34722.9906" OrderDate="2002-05-
01T00:00:00" ShipDate="2002-05-08T00:00:00" />
2 <OrderHeader SalesOrderID="46976" TotalDue="10184.0774" OrderDate="2002-08-
01T00:00:00" ShipDate="2002-08-08T00:00:00" />
<OrderHeader SalesOrderID="47997" TotalDue="5469.5941" OrderDate="2002-11-
01T00:00:00" ShipDate="2002-11-08T00:00:00" />
<OrderHeader SalesOrderID="49054" TotalDue="1739.4078" OrderDate="2003-02-
01T00:00:00" ShipDate="2003-02-08T00:00:00" />
<OrderHeader SalesOrderID="50216" TotalDue="1935.5166" OrderDate="2003-05-
01T00:00:00" ShipDate="2003-05-08T00:00:00" />
<OrderHeader SalesOrderID="51728" TotalDue="3905.2547" OrderDate="2003-08-
01T00:00:00" ShipDate="2003-08-08T00:00:00" />
<OrderHeader SalesOrderID="57044" TotalDue="4537.8484" OrderDate="2003-11-
01T00:00:00" ShipDate="2003-11-08T00:00:00" />
<OrderHeader SalesOrderID="63198" TotalDue="4053.9506" OrderDate="2004-02-
01T00:00:00" ShipDate="2004-02-08T00:00:00" />
<OrderHeader SalesOrderID="69488" TotalDue="908.3199" OrderDate="2004-05-
01T00:00:00" ShipDate="2004-05-08T00:00:00" />

					  

FOR XML PATH

If you want to create element-based XML, you can use FOR XML PATH to specify column aliases that contain valid XPath expressions that will shape your XML output.

USE AdventureWorks
GO
--XML FOR PATH
SELECT TOP 2 --limits result rows for demo purposes
ContactID AS [@Contact_ID],
FirstName AS [ContactName/First],
LastName AS [ContactName/Last],
Phone AS [ContactPhone/Phone1]
FROM Person.Contact FOR XML PATH

The output looks like this:

<row Contact_ID="1">
 <ContactName>
   <First>Gustavo</First>
   <Last>Achong</Last>
 </ContactName>
 <ContactPhone>
   <Phone1>398-555-0132</Phone1>
 </ContactPhone>
</row>
<row Contact_ID="2">
  <ContactName>
    <First>Catherine</First>
    <Last>Abel</Last>
  </ContactName>
  <ContactPhone>
    <Phone1>747-555-0171</Phone1>
  </ContactPhone>
</row>

Now let’s revisit the last example shown in Listing 4-3: FOR XML EXPLICIT. Using the TYPE option in conjunction with FOR XML PATH, you can reproduce that awful and complex query with a much simpler version, as shown here:

USE AdventureWorks
GO

SELECT
   CustomerID AS [@CustomerID],
   (SELECT SalesOrderID AS [@SalesOrderID],
            TotalDue AS [@TotalDue],
            OrderDate,
            ShipDate,
            (SELECT ProductID AS [@ProductID],
                     OrderQty AS [@OrderQty],
                     LineTotal AS [@LineTotal]
                     FROM Sales.SalesOrderDetail
                     WHERE SalesOrderID = OrderHeader.SalesOrderID
                     FOR XML PATH('OrderDetail'), TYPE)
   FROM Sales.SalesOrderHeader AS OrderHeader
   WHERE CustomerID = Customer.CustomerID
   FOR XML PATH('OrderHeader'), TYPE)
FROM Sales.Customer AS Customer
WHERE CustomerID IN (1, 2)
FOR XML PATH ('Customer')

Isn’t that much better? This query uses a subselect using the XML PATH statement in conjunction with TYPE to produce element-based XML nested inside a much larger FOR XML PATH statement. This returns each separate Order for the customer as a new child node of the Customer ID node; you can see this in the results of the following query:

<Customer CustomerID="1">
  <OrderHeader SalesOrderID="43860" TotalDue="14603.7393">
    <OrderDate>2001-08-01T00:00:00</OrderDate>
    <ShipDate>2001-08-08T00:00:00</ShipDate>
    <OrderDetail ProductID="761" OrderQty="2" LineTotal="838.917800" />
    <OrderDetail ProductID="770" OrderQty="1" LineTotal="419.458900" />
More...
  </OrderHeader>
  <OrderHeader SalesOrderID="44501" TotalDue="26128.8674">
    <OrderDate>2001-11-01T00:00:00</OrderDate>
    <ShipDate>2001-11-08T00:00:00</ShipDate>
    <OrderDetail ProductID="761" OrderQty="1" LineTotal="419.458900" />
    <OrderDetail ProductID="768" OrderQty="3" LineTotal="1258.376700" />
More...
  </OrderHeader>
  <OrderHeader SalesOrderID="45283" TotalDue="37643.1378">
    <OrderDate>2002-02-01T00:00:00</OrderDate>
    <ShipDate>2002-02-08T00:00:00</ShipDate>
    <OrderDetail ProductID="759" OrderQty="1" LineTotal="419.458900" />
    <OrderDetail ProductID="758" OrderQty="3" LineTotal="2624.382000" />
    <OrderDetail ProductID="750" OrderQty="2" LineTotal="4293.924000" />
More...
  </OrderHeader>
  <OrderHeader SalesOrderID="46042" TotalDue="34722.9906">
    <OrderDate>2002-05-01T00:00:00</OrderDate>
    <ShipDate>2002-05-08T00:00:00</ShipDate>
    <OrderDetail ProductID="763" OrderQty="2" LineTotal="838.917800" />
    <OrderDetail ProductID="757" OrderQty="4" LineTotal="3499.176000" />
More...
  </OrderHeader>
</Customer>
More...

					  

If you are familiar and comfortable with XPath, you might like some additional XML PATH features. You can use the following XPath node test functions to further control the shape of your XML output:

• data

• comment

• node

• text

• processing-instruction

The following example uses the data and comment methods of XPath. The data method takes the results of the underlying query and places them all inside one element. The comment method takes data and transforms it into an XML comment, as shown in this example:

SELECT
   Customer.CustomerID AS [@CustomerID],
   Contact.FirstName + ' ' + Contact.LastName AS [comment()],
   (SELECT SalesOrderID AS [@SalesOrderID],
         TotalDue AS [@TotalDue],
         OrderDate,
         ShipDate,
         (SELECT ProductID AS [data()]
         FROM Sales.SalesOrderDetail
         WHERE SalesOrderID = OrderHeader.SalesOrderID
         FOR XML PATH('')) AS [ProductIDs]
   FROM Sales.SalesOrderHeader AS OrderHeader
   WHERE CustomerID = Customer.CustomerID
   FOR XML PATH('OrderHeader'), TYPE)
FROM Sales.Customer AS Customer
INNER JOIN Sales.Individual AS Individual
   ON Customer.CustomerID = Individual.CustomerID
INNER JOIN Person.Contact AS Contact
   ON Contact.ContactID = Individual.ContactID
WHERE Customer.CustomerID IN (11000, 11001)
FOR XML PATH ('Customer')

The results are as follows; as you can see, the concatenated contact name becomes an XML comment, and the subquery of Product IDs is transformed into one element:

<Customer CustomerID="11000">
  <!--Jon Yang-->
  <OrderHeader SalesOrderID="43793" TotalDue="3756.9890">
    <OrderDate>2001-07-22T00:00:00</OrderDate>
    <ShipDate>2001-07-29T00:00:00</ShipDate>
    <ProductIDs> 966 934 923 707 881</ProductIDs>
  </OrderHeader>
More...
</Customer>
<Customer CustomerID="11001">
  <!--Eugene Huang-->
  <OrderHeader SalesOrderID="43767" TotalDue="3729.3640">
    <OrderDate>2001-07-18T00:00:00</OrderDate>
    <ShipDate>2001-07-25T00:00:00</ShipDate>
    <ProductIDs> 779 878 870 871 884 712</ProductIDs>
  </OrderHeader>
More...
</Customer>

Specifying a ROOT Element

The ROOT option allows you to add a main, or root, element to your FOR XML output. You can combine this with other FOR XML keywords, as shown here:

USE AdventureWorks
GO
--Root
SELECT Customer.CustomerID,
   OrderDetail.SalesOrderID, OrderDetail.OrderDate
FROM Sales.Customer AS Customer
INNER JOIN Sales.SalesOrderHeader OrderDetail
      ON OrderDetail.customerid=Customer.customerid
WHERE Customer.CustomerID<20
ORDER BY Customer.CustomerID
FOR XML AUTO, ROOT ('Orders')

The output looks like this:

<Orders>
  <Customer CustomerID="1">
    <OrderDetail SalesOrderID="43860" OrderDate="2001-08-01T00:00:00" />
    <OrderDetail SalesOrderID="44501" OrderDate="2001-11-01T00:00:00" />
    <OrderDetail SalesOrderID="45283" OrderDate="2002-02-01T00:00:00" />
    <OrderDetail SalesOrderID="46042" OrderDate="2002-05-01T00:00:00" />
  </Customer>
...more...
</Orders>

The code output here is the same as any FOR XML AUTO output for this query, except that the XML ROOT we specified with the ROOT keyword now surrounds the data. In this example, we used ROOT (‘Orders’), so our output is surrounded with an <Orders> XML element.

Producing an Inline XSD Schema

As mentioned earlier, XML Schema Definition (XSD) provides an enforceable structure for your XML data. When you export data using the FOR XML syntax, you might want to include an inline XML schema for the recipient so that the recipient can enforce the rules on her end as well. Internet Explorer is an example of a recipient that enforces an XML schema if one is provided. When you use the RAW and AUTO modes, you can produce an inline XSD Schema as part of the output, as shown here:

USE AdventureWorks
GO
SELECT Customer.CustomerID,
   OrderDetail.SalesOrderID, OrderDetail.OrderDate
FROM Sales.Customer AS Customer
INNER JOIN Sales.SalesOrderHeader AS OrderDetail
      ON OrderDetail.CustomerID = Customer.CustomerID
WHERE Customer.CustomerID < 20
ORDER BY Customer.CustomerID
FOR XML AUTO, XMLSCHEMA

The output looks like this:

<xsd:schema targetNamespace="urn:schemas-microsoft-
com:sql:SqlRowSet1" xmlns:schema="urn:schemas-microsoft-
com:sql:SqlRowSet1" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sqltypes="http://
schemas.microsoft.com/sqlserver/2004/sqltypes" elementFormDefault="qualified">
  <xsd:import namespace="http://schemas.microsoft.com/sqlserver/2004/
sqltypes" schemaLocation="http://schemas.microsoft.com/sqlserver/2004/sqltypes/
sqltypes.xsd" />
  <xsd:element name="Customer">
    <xsd:complexType>
      <xsd:sequence>
        <xsd:element ref="schema:OrderDetail" minOccurs="0" maxOccurs="unbounded" />
      </xsd:sequence>
      <xsd:attribute name="CustomerID" type="sqltypes:int" use="required" />
    </xsd:complexType>
  </xsd:element>
  <xsd:element name="OrderDetail">
    <xsd:complexType>
      <xsd:attribute name="SalesOrderID" type="sqltypes:int" use="required" />
      <xsd:attribute name="OrderDate" type="sqltypes:datetime" use="required" />
    </xsd:complexType>
  </xsd:element>
</xsd:schema>
<Customer xmlns="urn:schemas-microsoft-com:sql:SqlRowSet1" CustomerID="1">
  <OrderDetail SalesOrderID="43860" OrderDate="2001-08-01T00:00:00" />
  <OrderDetail SalesOrderID="44501" OrderDate="2001-11-01T00:00:00" />
  <OrderDetail SalesOrderID="45283" OrderDate="2002-02-01T00:00:00" />
  <OrderDetail SalesOrderID="46042" OrderDate="2002-05-01T00:00:00" />
</Customer>

					  

SQL Server infers the schema based on the underlying data types of the result set. For example, the SalesOrderID field is set to an int and is a required field (as per the inline Schema based on the properties of the field in the underlying SQL table).

Producing Element-Based XML

Many developers prefer element-based XML over attribute-based XML. Element-based XML presents its data as individual elements, as opposed to individual attributes, for each data point. A welcome enhancement to RAW and AUTO is the ability to specify element-based XML as a result by using the ELEMENTS keyword:

Select Customer.CustomerID, OrderDetail.SalesOrderID,OrderDetail.OrderDate
From Sales.Customer Customer
inner join Sales.SalesOrderHeader OrderDetail
      on OrderDetail.customerid=Customer.customerid
where Customer.CustomerID=1
Order by Customer.CustomerID
For XML AUTO, ELEMENTS

The results look like this:

<Customer>
  <CustomerID>1</CustomerID>
  <OrderDetail>
    <SalesOrderID>43860</SalesOrderID>
    <OrderDate>2001-08-01T00:00:00</OrderDate>
  </OrderDetail>
  <OrderDetail>
    <SalesOrderID>44501</SalesOrderID>
    <OrderDate>2001-11-01T00:00:00</OrderDate>
  </OrderDetail>
  <OrderDetail>
    <SalesOrderID>45283</SalesOrderID>
    <OrderDate>2002-02-01T00:00:00</OrderDate>
  </OrderDetail>
  <OrderDetail>
    <SalesOrderID>46042</SalesOrderID>
    <OrderDate>2002-05-01T00:00:00</OrderDate>
  </OrderDetail>
</Customer>

As you can see, each column of the query becomes an element in the resulting XML, as opposed to an attribute of one row.

The ELEMENTS keyword used in conjunction with the FOR XML statement converts each column from your result set to an individual XML element; using AUTO, it also converts each row from a joined table to a new XML element. Note that element-based XML is more verbose than attribute-based XML but is usually easier to view and work with.