361
Chapter 14: Using XML Data
14
<Name>Adam</Name>
</Order>
</Orders>
/*Explicit
The EXPLICIT directive of the FOR XML clause is the most powerful of the directives and
provides the most control over the resulting structure of the XML. It is, however, the most
diffi cult to fi gure out and understand simply because it is so complicated.The EXPLICIT directive requires two columns in each row: Tag and Parent. The resulting
data should be structured in a way that represents a hierarchical relationship between the
Tag rows and the Parent rows.Because of the complexity of the EXPLICIT directive, SQL Server 2005 introduced the
PATHdirective, which provides the same control over resulting XML without the headache
of the syntax. Thus, you can bypass the EXPLICIT directive and go straight to the PATH
directive. If you would like more information using the EXPLICIT directive, you can fi nd
information at http://msdn.microsoft.com/en-us/library/ms189068.aspx.Path
The PATH directive was introduced in SQL Server 2005 as an alternative over the EXPLICIT
directive. The PATH directive is just as powerful as the EXPLICIT directive, but it is as
simple to use as the AUTO and RAW directives.On the surface, the PATH directive may not seem powerful, but it shines and shows its
supremacy when dealing with more complex XML structures, such as in working with a
multilevel hierarchy.The following is an example of how easy the PATH directive is used with several levels of
hierarchy.SELECT
o.OrderNumber AS '@OrderNumber',
c.Name AS 'Customer/@Name',
i.ItemNumber AS 'LineItems/Item/@ItemNo',
od.Quantity AS 'LineItems/Item/@Qty'
FROM Orders o
INNER JOIN Customer c on o.customerID = c.customerid
INNER JOIN OrderDetail od on od.OrderID = o.OrderID
INNER JOIN Item i on i.ItemID = od.ItemID
FOR XML PATH('Order'), ROOT('Orders')You can see in the query how easy it is to specify the values are attributes while including
multiple joins and the resulting XML.c14.indd 361c14.indd 361 7/30/2012 4:49:03 PM7/30/2012 4:49:03 PM
http://www.it-ebooks.info