362
Part III: Advanced T-SQL Data Types and Querying Techniques
/*
<Orders>
<Order OrderNumber="10001">
<Customer Name="Scott" />
<LineItems>
<Item ItemNo="V001" Qty="1" />
</LineItems>
</Order>
<Order OrderNumber="10002">
<Customer Name="Adam" />
<LineItems>
<Item ItemNo="A017" Qty="1" />
</LineItems>
</Order>
<Order OrderNumber="10001">
<Customer Name="Scott" />
<LineItems>
<Item ItemNo="V001" Qty="5" />
</LineItems>
</Order>
<Order OrderNumber="10002">
<Customer Name="Adam" />
<LineItems>
<Item ItemNo="P002" Qty="2" />
</LineItems> </Order>
</Orders>
/*
To produce these same results using the EXPLICIT directive would have taken three times
the amount of code. The power of the PATH directive comes from the ability to generate deep
hierarchies based on simply the column name. For example, the column name LineItems/
Item/@Qty creates an Item element with a Qty attribute under the LineItems element.The PATH directive also supports a number of special characters to produce different XML
formatting requirements. For example, you can create elements that have a text value as
well as attributes simply by naming a column with an asterisk (*):SELECT CustomerID AS '@customerid', OrderNumber AS '*'
FROM Orders
FOR XML PATH('Order'), ROOT('Orders')/*
<Orders>
<Order customerid="1">10001</Order>
<Order customerid="2">10002</Order>
<Order customerid="1">10003</Order>
<Order customerid="2">10004</Order>
</Orders>
*/c14.indd 362c14.indd 362 7/30/2012 4:49:03 PM7/30/2012 4:49:03 PM
http://www.it-ebooks.info