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
WHERE Customers.CustomerID = 'ALFKI'
AND Orders.OrderID = 10643
FOR XML AUTO, ELEMENTS

go

XML_F52E2B61-18A1-11d1-B105-00805F49916B
----------------------------------------
<Customers>
       <CID>ALFKI</CID>
       <CO>Alfreds Futterkiste</CO>
       <CN>Maria Anders</CN>
       <Orders>
               <OrderID>10643</OrderID>
       </Orders>
</Customers>