Transforms Rows Into XML Elements: FOR XML AUTO

This post we'll explore the XML AUTO mode.

FOR XML AUTO
    By default:
        No nesting
        Use attribute-centric presentation.
        Element/row tag names = schema.table or the table alias.
        It's not allowed to change the row/element tag names by specifying an argument for AUTO mode.
        Note it's only allowed to specify an argument to RAW or PATH mode of FOR XML to change the element/row tag name.
                Example:
                SELECT custid
                              ,contactname
                FROM Sales.Customers
                FOR XML RAW('Customers');

By adding the ELEMENT directive, the xml result is nested and formatted using element-centric presentation.
WHEN joining multiple table:
     Use ORDER BY clause to group children elements under one parent element.
    The columns order in the SELECT and ORDER BY clause will influence the nesting of the XML result. I will include some examples for this on the next post.

Below are some examples.

--default presentation when no directive is specified
SELECT  custid
       ,contactname
FROM    Sales.Customers 
WHERE   custid <= 2
FOR XML AUTO;

Result: attribute-centric presentation, no nesting, row/element tag name = schema.table

<Sales.Customers custid="1" contactname="Allen, Michael" />
<Sales.Customers custid="2" contactname="Hassall, Mark" />

--using table alias
SELECT  c.custid
       ,c.contactname
FROM    Sales.Customers AS c
WHERE   c.custid <= 2
FOR XML AUTO;

Result: In this example, the row tag names = the table alias.

<c custid="1" contactname="Allen, Michael" /> 
<c custid="2" contactname="Hassall, Mark" />

-- with ELEMENTS directive
SELECT  custid  
       ,contactname 
FROM    Sales.Customers 
WHERE   custid <= 2
FOR XML AUTO, ELEMENTS;

Result: By adding the ELEMENT directive, the XML result is nested and formatted using element-centric presentation.

<Sales.Customers>
  <custid>1</custid>
  <contactname>Allen, Michael</contactname>
</Sales.Customers>
<Sales.Customers>
  <custid>2</custid>
  <contactname>Hassall, Mark</contactname>
</Sales.Customers>

-- with ELEMENTS and ROOT directive
SELECT  custid  
       ,contactname 
FROM    Sales.Customers 
WHERE   custid <= 2
FOR XML AUTO, ELEMENTS, ROOT('Customers');

Result: we can add the ROOT directive to make the XML result well-formed.

<Customers>
  <Sales.Customers>
    <custid>1</custid>
    <contactname>Allen, Michael</contactname>
  </Sales.Customers>
  <Sales.Customers>
    <custid>2</custid>
    <contactname>Hassall, Mark</contactname>
  </Sales.Customers>
</Customers>

--changing the row/element name by specifying an argument for AUTO.
SELECT  custid
       ,contactname
FROM    Sales.Customers 
WHERE   custid <= 2
FOR XML AUTO('Customer'), ELEMENTS;

Result: This query fails. For AUTO mode, it's not allowed to change the row/element tag names by specifying an argument for AUTO mode. Note row/element tag name is only allowed with RAW or PATH mode of FOR XML.


Next post I will provide some example output a query result to XML when joining multiple tables.

Reference:
http://technet.microsoft.com/en-us/library/ms188273.aspx
MS 70-461 Training Kit