SQL 301-FOR XML AUTO

In my previous XML article, I covered FOR XML RAW.  It’s useful, if limited.  This time I’d like to cover XML AUTO.  It’s still limited, but if you’re looking to model simply hierarchical data, AUTO may be the option for you.  If you’re looking for more details, I’d suggest checking out the books online article here.

We’re still going to use the Adventureworks database in my SQL 2008R2 instance, but we’re going to work with a new query.  This one shows us a simple hierarchical relationship between Customers, and their orders.

SELECT 
   customer.CustomerID, 
   OrderHeader.CustomerID,
   OrderHeader.SalesOrderID, 
   OrderHeader.Status,
   customer.CustomerType
FROM Sales.Customer customer
INNER JOIN Sales.SalesOrderHeader OrderHeader
   ON customer.CustomerID = OrderHeader.CustomerID
WHERE
   customer.CustomerID in (1,2)
ORDER BY 
   customer.CustomerID
FOR XML AUTO
--Once you click the link in ssms, you will see:
<customer CustomerID="1" CustomerType="S">
 <OrderHeader CustomerID="1" SalesOrderID="43860" Status="5" />
 <OrderHeader CustomerID="1" SalesOrderID="44501" Status="5" />
 <OrderHeader CustomerID="1" SalesOrderID="45283" Status="5" />
 <OrderHeader CustomerID="1" SalesOrderID="46042" Status="5" />
</customer>
<customer CustomerID="2" CustomerType="S">
 <OrderHeader CustomerID="2" SalesOrderID="46976" Status="5" />
 <OrderHeader CustomerID="2" SalesOrderID="47997" Status="5" />
 <OrderHeader CustomerID="2" SalesOrderID="49054" Status="5" />
 <OrderHeader CustomerID="2" SalesOrderID="50216" Status="5" />
 <OrderHeader CustomerID="2" SalesOrderID="51728" Status="5" />
 <OrderHeader CustomerID="2" SalesOrderID="57044" Status="5" />
 <OrderHeader CustomerID="2" SalesOrderID="63198" Status="5" />
 <OrderHeader CustomerID="2" SalesOrderID="69488" Status="5" />
</customer>

Study the results.  You will find that each table that has at least one column in the FROM clause has been returned as an element in the XML.  In our case it used the aliases, customer and OrderHeader.

The values we asked for in our select clause have become attributes.  We asked for customerID and CustomerType from the customer table, so both of our customer elements have  the attributes customerID and CustomerType.  We also asked for the customerID, SalesOrderID, and Status from the OrderHeader table, so OrderHeader has those attributes.

Now, just like when we use XML RAW, if we want to change the attributes to elements, all we do is add ELEMENTS to our query.

SELECT 
   customer.CustomerID, 
   OrderHeader.CustomerID,
   OrderHeader.SalesOrderID, 
   OrderHeader.Status,
   customer.CustomerType
FROM Sales.Customer customer
INNER JOIN Sales.SalesOrderHeader OrderHeader
   ON customer.CustomerID = OrderHeader.CustomerID
WHERE
   customer.CustomerID in (1,2)
ORDER BY 
   customer.CustomerID
FOR XML AUTO, ELEMENTS
--I'm only going to show the results for CustomerID 1
--since this xml is much bigger.
<customer>
  <CustomerID>1</CustomerID>
  <CustomerType>S</CustomerType>
  <OrderHeader>
    <CustomerID>1</CustomerID>
    <SalesOrderID>43860</SalesOrderID>
    <Status>5</Status>
 </OrderHeader>
 <OrderHeader>
    <CustomerID>1</CustomerID>
    <SalesOrderID>44501</SalesOrderID>
    <Status>5</Status>
 </OrderHeader>
 <OrderHeader>
    <CustomerID>1</CustomerID>
    <SalesOrderID>45283</SalesOrderID>
    <Status>5</Status>
 </OrderHeader>
    <OrderHeader>
    <CustomerID>1</CustomerID>
    <SalesOrderID>46042</SalesOrderID>
    <Status>5</Status>
 </OrderHeader>
</customer> 

The way the XML is constructed is based on the order of the tables in the SELECT clause.  In our case, the customer.CustomerID column was the first column in our SELECT clause, as a result, it’s the first element listed.  While the next item in my select is from the OrderHeader table, those are skipped until the CustomerType column is included, since it’s part of the customer table, and that was the first table listed in our SELECT.

After the first table listed has been processed, it goes to the second table listed, and so on, until all columns are included in the resulting xml, by order of tables aliased.

You can still add the ROOT(‘name’) keyword to your query if you want to wrap your results into a single element.

also, like XML RAW, if you want to include binary data, don’t forget the BINARY BASE64 option.

When using XML AUTO, you’re not going to have a great deal of flexibility in the structure of your XML, at least not without rewriting the query.  For more Flexibility you’ll want to look into XML PATH or XML EXPLICIT.

Play around with some queries and XML AUTO.  Try and break it.  Learning where the exceptions are will help you identify when you should and when you shouldn’t use XML AUTO.

If you have any questions, send them in… I’m here to help you learn more about SQL!