Using FOR XML AUTO, ELEMENTS

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>