SQL Blog of Zach Stagers

About a week ago I posted on FOR XML RAW, this time it’s FOR XML AUTO‘s turn.

The two are actioned via very similar syntax, but operate slightly differently. Auto is probably named “Auto” because it performs a lot of the XML node naming for you. There are also a couple of other differences which I’ll explain in this post.

With FOR XML AUTO, you don’t have the ability to name your row’s like you do in RAW. This is because their names are derived from the aliases given to the tables in your query. For example, having a table named Person, and using FOR XML AUTO against this table would give your rows a name of “Person”. This is the same as using FOR XML RAW('Person'), see below for a literal example of this:

SELECT * FROM Person
FOR XML AUTO

SELECT * FROM Person
FOR XML RAW('Person')

Both of the above examples would return the below XML:

<Person PersonId="1" Name="Zach Stagers" Age="22" />

As with RAW, you can specify your root element in AUTO:

SELECT * FROM Person
FOR XML AUTO, ROOT('People')

The above would return the exact same XML data, but with the specified root element wrapped around it.

<People>
  <Person PersonId="1" Name="Zach Stagers" Age="22" />
</People>

The attributes of the person row can further be split out into their own elements by specifying ELEMENTS, and can also be given a namespace and handle SQL NULL‘s by specifying XSINIL

SELECT * FROM Person
FOR XML AUTO, ROOT('People'), ELEMENTS, XSINIL

The above example would return:

<People xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <Person>
    <PersonId>1</PersonId>
    <Name>Zach Stagers</Name>
    <Age>22</Age>
  </Person>
</People>

There are a couple of other things to remember when using FOR XML AUTO, and that is that the order of your XML elements is down the order in which your columns are specified. For example using the below two queries to return tabular data would give you the same results, but using them with FOR XML AUTO will give you completely different XML:

SELECT Person.Name,
       Orders.Item
FROM Person JOIN
     Orders ON Orders.PersonId = Person.PersonId
FOR XML AUTO, ROOT('PeopleWithOrders')

Returns:

<PeopleWithOrders>
  <Person Name="Zach Stagers">
    <Orders Item="Bike" />
    <Orders Item="Laptop" />
    <Orders Item="Phone" />
  </Person>
</PeopleWithOrders>

SELECT Orders.Item,
       Person.Name
FROM Person JOIN
     Orders ON Orders.PersonId = Person.PersonId
FOR XML AUTO, ROOT('PeopleWithOrders')

The above query, with the columns swapped over will return:

<PeopleWithOrders>
  <Orders Item="Bike">
    <Person Name="Zach Stagers" />
  </Orders>
  <Orders Item="Laptop">
    <Person Name="Zach Stagers" />
  </Orders>
  <Orders Item="Phone">
    <Person Name="Zach Stagers" />
  </Orders>
</PeopleWithOrders>

So, you just need to remember that the order of the columns is what specifies the output of your XML, not the order of the tables in the FROM clause. The names given to the tables, however, specifies the names given to the XML nodes.