Microsoft® SQL Server™ 2000 Unleashed, Second Edition

Using FOR XML AUTO, ELEMENTS

By specifying the syntax FOR XML AUTO, ELEMENTS, SQL Server 2000 will render all selected columns as child elements (instead of attributes) of parent elements named after the table to which they belong. This is known as element-centric mapping.

In Listing 41.4, every Customers element has three child elements that correspond to the columns selected from Customers, and one Orders child element for every matching row in Orders. Each Orders element in turn has one child element corresponding to OrderID.

Listing 41.4. FOR XML AUTO, ELEMENTS Produces Element-Centric Mapping of Rowsets into XML

SELECT Customers.CustomerID CID, CompanyName CO, ContactName CN, OrderID FROM Customers JOIN Orders on Orders.CustomerID = Customers.CustomerID ...