Today’s little task was to grab some data from a database and create an XML file from it. So I decided to play around with the XML features in SQL Server (I used 2008 for this).

One for all and all “FOR XML”

The Microsoft documentation.aspx) told me that the “FOR XML” clause was what I’d need for the task. It comes in four different flavours, each with its own unique talents:

• RAW
• AUTO
• EXPLICIT
• PATH

While I won’t delve into each other them, I’m sure the documentation would better explain, I’ll just focus on the ones I found useful. The first was PATH.

Following the PATH

Being described as “a simpler way to mix elements and attributes”, it seemed like a good place to start as any. Tacking it onto the end of a query produce a simple XML structure, a row element per row returned each with child elements per column.

SELECT  id, name
FROM    users
FOR XML PATH

Giving you the XML of…

<row>
  <id>1</id>
  <name>Bob</name>
</row>
<row>
  <id>2</id>
  <name>Jonesy</name>
</row>

Wrapping and attributes

That worked out quite well but it’d be nice to have the “id” element as an attribute on the row tag. I’d also like to wrap a root element around everything.

SELECT  id AS '@id',
        name
FROM    users
FOR XML PATH, ROOT('Rows')

<Rows>
  <row id="1">
    <name>Bob</name>
  </row>
  <row id="2">
    <name>Jonesy</name>
  </row>
</Rows>

When is a row not a row?

What you’d like it to be named something else. You may have a specific XML structure that you have to comply with, so having each item wrapped in a row tag may not be ideal. Thankfully it’s simple to change by just adding the element name to the PATH as a parameter.

SELECT  id AS '@id',
        name
FROM    users
FOR XML PATH('User'), ROOT('Group')

<Group>
  <User id="1">
    <name>Bob</name>
  </User>
  <User id="2">
    <name>Jonesy</name>
  </User>
</Group>

Getting to the root of the problem

(“Yes, I can’t help myself with silly title names… sorry”) We’ve now got a root element for the group of users, but what group is it? It’d be useful to add an XML attribute to the Group tag but from what I could tell, that wasn’t possible via the ROOT option. So how about using a sub-query?

SELECT
  1 AS '@id',
  (SELECT  id AS '@id',
            name
    FROM    users
    FOR XML PATH('User')
  )
FOR XML PATH('Group')

<Group id="1"><User id="1"><name>Bob<...

Wait… what? Well it turns out that when running my first examples in SQL Management Studio Express that it’d happily treat it as an XML datatype. But when using the returned data for anything else, it ends up as a varchar or similar string type. To work around this, I needed to use an additional clause of TYPE on the end of the FOR XML bit.

Example SQL

The following shows an example of creating an XML document. One of the main differences is my use of FOR RAW on the outer query. This allows me to easily set the name space attributes for the root element.

SELECT
  -- Root element attributes
  'http://www.w3.org/2001/XMLSchema-instance' AS 'xmlns:xsi',
  'http://www.w3.org/2001/XMLSchema' AS 'xmlns:xsd',
  (
    SELECT
      -- Creating a default element
      (
        SELECT  0 AS ID,
                '' AS Name
        FOR     XML PATH('Item'),
                ELEMENTS,
                TYPE
      ),
      (
        SELECT  id AS ID,
                title AS Name
        FROM    items
        ORDER   BY id
        FOR     XML PATH('Item'),
                ELEMENTS,
                TYPE
      )
    FOR     XML PATH('Items'),
            TYPE
  )
FOR     XML RAW('Information')

Below is what’s returned

<Information xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <Items>
    <Item>
      <ID>0</ID>
      <Name />
    </Item>
    <Item>
      <ID>1</ID>
      <Name>Item number A</Name>
    </Item>
    <Item>
      <ID>2</ID>
      <Name>Item number B</Name>
    </Item>
    <Item>
      <ID>3</ID>
      <Name>Item number C</Name>
    </Item>
    <Item>
      <ID>4</ID>
      <Name>Item number D</Name>
    </Item>
    <Item>
      <ID>5</ID>
      <Name>Item number E</Name>
    </Item>
  </Items>
</Information>