Querying SQL Server XML data

Today I was working with data stored using SQL Servers XML datatype. I had to find a way of dealing with the XML data like you would standard relational table data. Here’s how to do it.

Sample table and data

Firstly, here is a basic table to store the data:

CREATE TABLE DummyTable
(
    DummyTableID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    DummyXML XML NOT NULL
)
GO

and some dummy XML data, which can be inserted using a standard INSERT statement:

<catalog>
    <artist>
        <name>Bob Dylan</name>
        <album>Blonde on Blonde</album>
        <members>
            <member>Bob Dylan</member>
        </members>
    </artist>
    <artist>
        <name>Sigur Ros</name>
        <album>Takk...</album>
        <members>
            <member>Jónsi</member>
            <member>Goggi</member>
            <member>Kjarri</member>
            <member>Orri</member>
        </members>
    </artist>
</catalog>

Querying the Data

If you do a simple select statement like:

SELECT * FROM DummyTable

you’ll see that 1 row is returned and the XML data is displayed as a single string.

We can use the following query to get a unique row for each artist:

SELECT
    a.value('name[1]', 'varchar(15)') AS ArtistName
,   a.value('album[1]', 'varchar(20)') AS AlbumName
,   a.query('members') AS Members
FROM
    DummyTable
    CROSS APPLY DummyXML.nodes('/catalog/artist') AS Artists(a)
GO

Which will give 1 row per artist but the Members column will be returned as an XML string.

||ArtistName||AlbumName||Members|| | – | — | — | |Bob Dylan | Blonde on Blonde | Bob Dylan| |Sigur Ros | Takk… | JónsiGoggi… | You can then further break down the members XML to get a unique row containing each MemberName by adding a further CROSS APPLY function in the FROM statement:

SELECT
  a.value('name[1]', 'varchar(15)') AS ArtistName
, a.value('album[1]', 'varchar(20)') AS AlbumName
, m.value('(text())[1]', 'varchar(15)') AS MemberName
FROM  
  DummyTable 
  CROSS APPLY DummyXML.nodes('/catalog/artist') AS Artists(a)
  CROSS APPLY a.nodes('members/member') AS Members(m)
GO

which will give the following results:

Key points

We can see how this works by firstly breaking down the FROM statement:

1. FROM DummyTable Select all rows from the DummyTable table.

2. CROSS APPLY DummyXML.nodes(‘/catalog/artist’) AS Artists(a) This uses the CROSS APPLY function. This invokes a function for each row returned and appends it to the result set. So, for the above I use the nodes() function on the DummyXML column to map the specified XML nodes to a new row.

3. CROSS APPLY a.nodes(‘members/member’) AS Members(m) This further filters the returned XML to get a new row for each member.

The rest of the work is done in the SELECT statement:

1. SELECT a.value(‘name[1]’, ‘varchar(15)’) AS ArtistName , a.value(‘album[1]’, ‘varchar(20)’) AS AlbumName This uses the XML datatypes value() method against the return ‘a’ column. Using XQuery I specify that I want the first result (‘[1]’) from the name node (‘name’), casting it as a varchar(15).

2. , a.query(‘members’) AS Members This uses the XML datatypes query() method to return untyped XML from the members node (‘members’).

3. , m.value(‘(text())[1]’, ‘varchar(15)’) AS MemberName This final statement again uses the value() method to filter the returned members node. Since there are no child nodes I use the (text()) function to get the returned nodes inner text because there are no child nodes.

Extra Notes

There are many different ways to structure the XQuery passed into the various functions, not just what I’ve shown above. The CROSS APPLY function isn’t only applied to XML data, it can, for example, be used to get hierarchical data from a flat table structure. You can do all of the usual filtering on the SELECT results, e.g. ISNULL(), CASE statements etc.