Using 'FOR XML AUTO'

For greater control over the names and structure of your XML, use AUTO mode. AUTO mode facilitates the naming of XML elements with something other than "row." Element names correspond to the name or alias of the selected tables.

AUTO mode creates a structured view of data that mirrors the relationships of selected tables by nesting XML elements selected from multiple tables. A nested or child element is one that is enclosed by another element, known as its parent element. Listing 41.3 shows an example of element nesting.

Listing 41.3 FOR XML AUTO Enables Nesting of XML Elements Based on Relationships Expressed in a Join

SELECT Customer.CustomerID CID, CompanyName CO, ContactName CN, OrderID
FROM Customers Customer
JOIN Orders on Orders.CustomerID = Customer.CustomerID
WHERE Customer.CustomerID = 'ANTON'
FOR XML AUTO

go

XML_F52E2B61-18A1-11d1-B105-00805F49916B  
------------------------------------------------------------------
<Customer CID="ANTON" CO="Antonio Moreno Taquería" CN="Antonio Moreno">
        <Orders OrderID="10365"/><Orders OrderID="10507"/>
        <Orders OrderID="10535"/><Orders OrderID="10573"/>
        <Orders OrderID="10677"/><Orders OrderID="10682"/>
        <Orders OrderID="10856"/>
</Customer>

The neat thing about this default behavior is that the XML structure accurately reflects the relationship between the two entities: A Customer places many Orders. This query can be extended to join Employees to Orders, EmployeeTerritories to Employees, and so on, producing a more complex XML document that still accurately reflects the database relationships.

This is only true, however, if you list the selected columns from left to right as if traversing the table hierarchy from parent table to child table. Customers place Orders, for example, so you would list all the columns from Customers before those of the Orders table. Employees are assigned to Orders?list its columns after those of Orders, and so on. What makes AUTO mode flexible in its XML results is that you can alter the nesting of XML elements by rearranging the order of columns in the SELECT list. If you change the example in Listing 41.3 to specify OrderID first in the SELECT list, every Orders element in the results will contain a Customer child element identifying the customer for that order.