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.
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>