Microsoft® SQL Server® 2012 Bible

356

Part III: Advanced T-SQL Data Types and Querying Techniques

directives to use, it doesn’t provide a lot of control over the resulting structure of the XML

output.

The key to the AUTO directive is in its name, in that it “automatically” names the elements

and hierarchies based on table and column names, any aliases used, and joins in the query.

For example, the following code illustrates a simple FOR XML AUTO clause:

SELECT CustomerID, OrderNumber, OrderDate

FROM Orders

FOR XML AUTO

The results show that the element name is taken from the table in which the data comes from.

<Orders CustomerID="1" OrderNumber="10001" OrderDate="2011-06-15T00:00:00" />

<Orders CustomerID="2" OrderNumber="10002" OrderDate="2011-06-16T00:00:00" />

<Orders CustomerID="1" OrderNumber="10003" OrderDate="2011-06-17T00:00:00" />

<Orders CustomerID="2" OrderNumber="10004" OrderDate="2011-06-18T00:00:00" />

You can change the name of the element by aliasing the table name in the query:

SELECT CustomerID, OrderNumber, OrderDate

FROM Orders o

FOR XML AUTO

/*

<o CustomerID="1" OrderNumber="10001" OrderDate="2011-06-15T00:00:00" />

<o CustomerID="2" OrderNumber="10002" OrderDate="2011-06-16T00:00:00" />

<o CustomerID="1" OrderNumber="10003" OrderDate="2011-06-17T00:00:00" />

<o CustomerID="2" OrderNumber="10004" OrderDate="2011-06-18T00:00:00" />

*/

The two previous examples produced XML fragments, not documents. A valid XML document

can have only a single top-level node. You can add that by using the ROOT directive and

specifying the name of the element:

SELECT CustomerID, OrderNumber, OrderDate

FROM Orders

FOR XML AUTO, ROOT('TodaysOrders')

/*

<TodaysOrders>

<Orders CustomerID="1" OrderNumber="10001" OrderDate="2011-06-15T00:00:00" />

<Orders CustomerID="2" OrderNumber="10002" OrderDate="2011-06-16T00:00:00" />

<Orders CustomerID="1" OrderNumber="10003" OrderDate="2011-06-17T00:00:00" />

<Orders CustomerID="2" OrderNumber="10004" OrderDate="2011-06-18T00:00:00" />

</TodaysOrders>

*/

c14.indd 356c14.indd 356 7/30/2012 4:49:02 PM7/30/2012 4:49:02 PM

http://www.it-ebooks.info