FOR XML AUTO
The AUTO option of the FOR XML clause simply treats the table name as a parent, and each column in the table becomes a child of the table-name tag. For instance, the following query retrieves the first two rows in the customers table from Northwind database in XML format:
SELECT TOP 2 * FROM employees FOR XML AUTO
Results:
<customers CustomerID="ALFKI" CompanyName="Alfreds Futterkiste" ContactName="Maria Anders" ContactTitle="Sales Representative" Address="Obere Str. 57" City="Berlin" PostalCode="12209" Country="Germany" Phone="030-0074321" Fax="030-0076545"/> <customers CustomerID="ANATR" CompanyName="Ana Trujillo Emparedados y helados" ContactName="Ana Trujillo" ContactTitle="Owner" Address="Avda. de la Constitución 2222" City="México D.F." PostalCode="05021" Country="Mexico" Phone="(5) 555-4729" Fax="(5) 555-3745"/>
By default, the AUTO mode treats the columns as attributes, but we can easily override this setting by appending the ELEMENTS clause, as follows:
SELECT TOP 2 * FROM customers FOR XML AUTO, ELEMENTS
Results:
<customers> <CustomerID>ALFKI</CustomerID> <CompanyName>Alfreds Futterkiste</CompanyName> <ContactName>Maria Anders</ContactName> <ContactTitle>Sales Representative</ContactTitle> <Address>Obere Str. 57</Address> <City>Berlin</City> <PostalCode>12209</PostalCode> <Country>Germany</Country> <Phone>030-0074321</Phone> <Fax>030-0076545</Fax> </customers> <customers> <CustomerID>ANATR</CustomerID> <CompanyName>Ana Trujillo Emparedados y helados</CompanyName> <ContactName>Ana Trujillo</ContactName> <ContactTitle>Owner</ContactTitle> <Address>Avda. de la Constitución 2222</Address> <City>México D.F.</City> <PostalCode>05021</PostalCode> <Country>Mexico</Country> <Phone>(5) 555-4729</Phone> <Fax>(5) 555-3745</Fax> </customers>
At this point, you might be asking what happens if you have multitable queries with XML AUTO. The answer is simple: You get a hierarchy of XML elements that depends on the order in which table columns are specified in the query. For instance, if I select a couple of rows from the customers and orders tables, the XML hierarchy returned depends on the order of columns specified in the query. Let's look at an example to make things easier to grasp:
SELECT TOP 2 Orders.OrderDate, Orders.ShipCity, Customers.CompanyName, Customers.Country FROM orders INNER JOIN customers ON orders.customerid = customers.customerid FOR XML AUTO
Results:
<orders OrderDate="1996-07-04T00:00:00" ShipCity="Reims"> <customers CompanyName="Vins et alcools Chevalier" Country="France"/> </orders> <orders OrderDate="1996-07-05T00:00:00" ShipCity="Münster"> <customers CompanyName="Toms Spezialitäten" Country="Germany"/> </orders>
Notice that because I specified columns selected from the orders table, the XML returned has <orders> as the parent tag and <customers> as the child. Another interesting fact is that Transact-SQL is not case-sensitive, whereas XML is. Because I specified table names in lowercase (within the JOIN statement), the elements are also in lowercase.
Now let's see what happens if we switch the order in which the columns are specified and capitalize the table names:
SELECT TOP 2 Customers.CompanyName, Customers.Country, Orders.OrderDate, Orders.ShipCity FROM Orders INNER JOIN Customers ON orders.customerid = customers.customerid FOR XML AUTO
Results:
<Customers CompanyName="Vins et alcools Chevalier" Country="France"> <Orders OrderDate="1996-07-04T00:00:00" ShipCity="Reims"/> </Customers> <Customers CompanyName="Toms Spezialitäten" Country="Germany"> <Orders OrderDate="1996-07-05T00:00:00" ShipCity="Münster"/> </Customers>
The same query with a slight change returns a completely different hierarchy: <Customers> is now the parent and <Orders> is the child.