If you’ve got some XML data in a SQL Server column, how can you flatten it out and query it or present it as though it was relational data? It turns out this is quite easy...
Setup
Let’s create a simple table to hold our data;
CREATE TABLE XmlSourceTable
(
RecordId INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
XmlData XML NOT NULL
)
GO
And we’ll define some straightforward XML to import;
xml version="1.0" ?>
<Root>
<Person>
<Name>SimonName>
<Age>20Age>
<Skills>
<Skill>CookingSkill>
<Skill>CleaningSkill>
Skills>
Person>
<Person>
<Name>PeterName>
<Age>21Age>
<Skills>
<Skill>IroningSkill>
Skills>
Person>
Root>
Ages may have been changed to protect the innocent J
Next, we’ll import it into my table using one of the mechanisms SQL Server provides – each XML file will be imported into a single row in the target table.
INSERT INTO XmlSourceTable(XmlData)
SELECT *
FROM OPENROWSET(
BULK 'C:\XmlSource.xml', SINGLE_BLOB)
AS ImportSource
GO
After this, if we do a quick query...
SELECT * FROM XmlSourceTable
... we can see that we get a single row back containing an ID and some XML;
RecordId XmlData
----------- -------
1 Simon20
(1 row(s) affected)
Queries
The simplest way to extract this data is to use the CROSS APPLY keyword, as this executes a function against each row and then adds the returned data to the result set. Combining this with a method that can be called on the XML data type called nodes, we get some great results. A quick query like this; SELECT
pref.value('(Name/text())[1]', 'varchar(50)') as PersonName,
pref.value('(Age/text())[1]', 'int') as PersonAge,
pref.query('Skills') as PersonSkills
FROM
XmlSourceTable CROSS APPLY
XmlData.nodes('/Root/Person') AS People(pref)
GO
... yields a completely different result set to our last query;
PersonName PersonAge PersonSkills
---------- --------- ----------------------
Simon 20 Cooking
Peter 21 Ironing
(2 row(s) affected)
We can see this query has flattened my single row of relational data with embedded hierarchical XML into two rows and columns of relational data. I’ve also included a subset of the XML as a column, just to show I can! Of course, if I wanted to I could modify this to get a list of people and their skills;
SELECT
pref.value('(Name/text())[1]', 'varchar(50)') as PersonName,
sref.value('(text())[1]', 'varchar(50)') as PersonSkill
FROM
XmlSourceTable CROSS APPLY
XmlData.nodes('//Person') AS People(pref) CROSS APPLY
pref.nodes('Skills/Skill') AS Skill(sref)
GO
What is it Doing?
This SQL can be difficult to understand when you first look at it, but it isn’t really that complex. Breaking it down there are three key concepts that we’ll cover below – using my first query above that fetches PersonName, PersonAge, and PersonSkills as an example.
CROSS APPLY
The first concept is the use of CROSS APPLY, which many people haven’t used before. What this is doing is roughly equivalent to the following steps (note: this is my idiots guide to how I think about it, not a description of how the query optimiser does it);
1. Fetch the rows from the XmlSourceTable table.
2. For each row, call the “nodes” function on the XmlData column. This could be some other function too – it needn’t be on XML data. See the docs on APPLY for more info.
4. Add the columns returned by the “nodes” function to the columns in the result set.
5. Continue doing filtering, joining, and column selection as for any other SQL query.
I hope that makes it a little clearer.
nodes() function
The XML data type in SQL Server defines a number of methods that can be called on it. One of these is “nodes” – and what this does is basically select a list of XML nodes that match an XQuery expression. Knowing this, look at the statement in my SQL; XmlData.nodes('/Root/Person') AS People(pref)
This is using the path “/Root/Person” to ensure that all Person nodes that exist under the Root are selected. The result is aliased as a table named People, and each resulting XML node will be output as a separate row, in the “pref” column.
Plenty of alternative syntaxes are available for selecting this list of nodes, and this really is the core of how to flatten out the XML. I’ve also used “//Skill” syntax in my second query, for example, which selects every Skill node found in the XML it is used on. Other XML functions
The last concept is the use of the selector XML functions – in my example I’ve used “value()” and “query()”. Both of these use XQuery expressions to select XML data. Firstly, I’ve used the value() function to extract a specific value from the XML, and convert it to a SQL Server data type;
pref.value('(Name/text())[1]', 'varchar(50)') as PersonName
The “text()” function here retrieves the inner text from within the XML “Name” node. The “[1]” suffix acts as an indexer, and fetches the first result matched. I know there’s only one name node per person, but I need to make sure SQL Server knows this too. Finally, the second argument is the data type it should be converted to. For an Age, I’ve used ‘int’, but here we’re converting to a varchar type.
The “query()” function allows me to return an XML fragment;
pref.query('Skills') as PersonSkills
This returns the XML that matches the “Skills” node, and is found underneath the current Person element held in “pref” (i.e. the search is relative to the contents of pref). This means it returns fragments such as;
Ironing
Conclusion
None of this is all that difficult once you know how – so I hope this has given you a quick start to using XML data in SQL! Don’t forget to read up more generally on the XML data type and XML indexes.