SQLUNINTERRUPTED

Continuing with the series on XML, today we will talk about the FOR XML AUTO clause which can be used with relation database queries to produce XML output. For a complete list of related topics, please refer here.

As mentioned above, we can use the FOR XML AUTO clause in the T-SQL queries to produce XML output. FOR XML has different flavors which can be used to produced desired results. Examples for SQL 2000 use the pubs database, while those for SQL 2005 and the SQL 2008 examples are created over the AdventureWorks database which can be downloaded online.

FOR XML AUTO Mode

When we use the AUTO mode, we get an XML representation of each row in the T-SQL result set.  When using AUTO mode, SQL server tries to create a nested XML output, in case of multi-table joins. For every table, which has at lease one column from the table in the select list an element is added, correspondingly, the column for that table become the element attributes.

The hierarchy (nesting of the elements) in the result set is based on the order of tables identified by the columns specified in the SELECT clause; therefore, the order in which column names are specified in the SELECT clause is significant. The tables are identified and nested in the order in which the column names are listed in the SELECT clause. The first, leftmost table identified forms the top element in the resulting XML document. The second leftmost table (identified by columns in the SELECT statement) forms a sub-element within the top element, and so on.

The basic syntax of the FOR XML AUTO mode, is similar to the RAW mode.

SELECT <col1, col2,….>
From <table>
FOR XML AUTO, <directives,..>

SELECT Customers.CustomerID, Orders.OrderID, Customers.ContactName
FROM Customers, Orders
WHERE Customers.CustomerID = Orders.CustomerID
FOR XML AUTO

<<<<<<<< Snippet output >>>>>>>>

<Customers CustomerID="ALFKI" ContactName="Maria Anders">
  <Orders OrderID="10643"/>
  <Orders OrderID="10692"/>
  <Orders OrderID="10702"/>
  <Orders OrderID="10835"/>
  <Orders OrderID="10952"/>
  <Orders OrderID="11011"/>
</Customers>

<<<<<<<< Snippet output >>>>>>>>

When the Element, directive is supplied, the column values are converted to Child Elements for the Table Element as mentioned.

SELECT Customers.CustomerID, Orders.OrderID, Customers.ContactName
FROM Customers, Orders
WHERE Customers.CustomerID = Orders.CustomerID
FOR XML AUTO, ELEMENTS

<Customers>
    <CustomerID>ALFKI</CustomerID>
    <ContactName>Maria Anders</ContactName>
    <Orders>
        <OrderID>10643</OrderID>
    </Orders>
    <Orders>
        <OrderID>10692</OrderID>
    </Orders>
    …
</Customer>

With SQL 2005 similar enhancements were made to the XML AUTO mode. We can use the other directives with AUTO mode, like we do for RAW mode as described in my post.