Home > Articles > Data > SQL Server

  • Print
  • + Share This
Like this article? We recommend

Like this article? We recommend

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.

  • + Share This
  • 🔖 Save To Your Account