357
Chapter 14: Using XML Data
14
The XML AUTO clause is also smart enough to generate hierarchical XML based on queries
that use joins:SELECT o.OrderNumber, o.OrderDate, c.Name
FROM Orders o
INNER JOIN Customer c ON o.CustomerID = c.CustomerID
FOR XML AUTO/*
<o OrderNumber="10001" OrderDate="2011-06-15T00:00:00">
<c Name="Scott" />
</o>
<o OrderNumber="10002" OrderDate="2011-06-16T00:00:00">
<c Name="Adam" />
</o>
<o OrderNumber="10003" OrderDate="2011-06-17T00:00:00">
<c Name="Scott" />
</o>
<o OrderNumber="10004" OrderDate="2011-06-18T00:00:00">
<c Name="Adam" />
</o>
*/The AUTO clause works by generating elements for each row in which values are created as
attributes. You can take the previous query and apply the ELEMENTS directive to it to turn
the attributes into elements:SELECT o.OrderNumber, o.OrderDate, c.Name
FROM Orders o
INNER JOIN Customer c ON o.CustomerID = c.CustomerID
FOR XML AUTO, ELEMENTS/*
<o>
<OrderNumber>10001</OrderNumber>
<OrderDate>2011-06-15T00:00:00</OrderDate>
<c>
<Name>Scott</Name>
</c>
</o>
<o>
<OrderNumber>10002</OrderNumber>
<OrderDate>2011-06-16T00:00:00</OrderDate>
<c>
<Name>Adam</Name>
</c>
</o>
<o>c14.indd 357c14.indd 357 7/30/2012 4:49:03 PM7/30/2012 4:49:03 PM
http://www.it-ebooks.info