Share via

Flattening XML Data in SQL Server

  • Article

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>Simon</Name>

            <Age>20</Age>

            <Skills>

            <Skill>Cooking</Skill>

                  <Skill>Cleaning</Skill>

            </Skills>

      </Person>

      <Person>

            <Name>Peter</Name>

            <Age>21</Age>

            <Skills>

                  <Skill>Ironing</Skill>

            </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 <Root><Person><Name>Simon</Name><Age>20</Age... (snip)

(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 <Skills><Skill>Cooking</Ski... (snip)

Peter 21 <Skills><Skill>Ironing</Ski... (snip)

(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.

3. Duplicate the XmlSourceTable row once for every row returned by the table valued function “nodes”.

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;

<Skills><Skill>Ironing</Skill></Skills>

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.

Comments

  • Anonymous
    April 24, 2009
    PingBack from http://asp-net-hosting.simplynetdev.com/flattening-xml-data-in-sql-server/

  • Anonymous
    April 24, 2009
    Excellent ...this is what i was looking for...Thanx a lot ...gr8 job

  • Anonymous
    May 04, 2009
    This could not have come to my attention at a better time!  PERFECT - THANK YOU!!!

  • Anonymous
    May 04, 2009
    @ Gaurav, Doug No problem, really pleased it is useful. Thanks for the feedback! Simon

  • Anonymous
    May 15, 2009
    If you’ve got some XML data in a SQL Server column, how can you flatten it out and query it or present

  • Anonymous
    June 16, 2009
    Something is wrong here - the result set of the second query is: Simon Cooking Simon Cleaning Simon Ironing Peter Cooking Peter Cleaning Peter Ironing but the xml says Peter only does the Ironing and Simon only does the Cooking and Cleaning

  • Anonymous
    June 16, 2009
    To fix the second query, use this nested SQL: SELECT  PersonName,  sref.value('(text())[1]', 'varchar(50)') as PersonSkill FROM ( SELECT  pref.value('(Name/text())[1]', 'varchar(50)') as PersonName,  pref.query('Skills') as PersonSkills FROM    XmlSourceTable  CROSS APPLY XmlData.nodes('//Person') AS People(pref) ) a    CROSS APPLY PersonSkills.nodes('//Skill') AS Skill(sref)

  • Anonymous
    June 16, 2009
    @ David, Well spotted! You should win a prize! This was a classic simplification-for-a-blog-post mistake... and it comes down to the fact that "//Skill" matches any skill, which of course is not what we want. Instead, it should be as follows; 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 For comparison, the old version that doesn't work is below (I've corrected my post): 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('//Skill') AS Skill(sref)       GO Hope that helps - let me know if fixes your issue. Simon

  • Anonymous
    September 24, 2009
    So just to confirm if you want a Cross Apply to "Correctly" associate the child data then you should use the syntax of parent.node('ChildTag) c(child) child.node('NextChild') nc(nextChild) etc...

  • Anonymous
    September 27, 2009
    @ Andrew, that looks about right to me - although note it is "nodes" not "node" (i.e. plural). Simon

  • Anonymous
    October 27, 2009
    Thanks a lot. I saw light at the end of the tunnel after 2-3 days

  • Anonymous
    February 06, 2010
    Simon, How would I extract <Finding Default="Same" RetrievalLimit="Hours" LimitToVisit="Yes" RetrieveLatest="Never" RetrievalNo="12">

  • Anonymous
    February 07, 2010
    @ Dcip, the above should get you started if you also realise that you can use the 'value' function to get attributes, for example; pref.value('./@PersonID','int') ... would get a PersonID attribute if my XML had entries like this; <Person PersonID="5"><Name>... etc Hope that helps. Simon

  • Anonymous
    March 18, 2010
    The comment has been removed

  • Anonymous
    March 18, 2010
    How would i extrac the below? <soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"><soap:Body><UpdateCustomer xmlns="RMCS.Oasis.Integration.Web"><userCredentials>CRMUser</userCredentials><customerDetail><User>CRMUser</User><RMCSID>400001171815</RMCSID><Title>Mr</Title><Surname>MOREKI MISS L E</Surname><PromotionalOfferPhone>false</PromotionalOfferPhone><PromotionalOfferSMS>false</PromotionalOfferSMS><PromotionalOfferEmail>false</PromotionalOfferEmail><Language>English</Language><Alternative>0742552878</Alternative><Cell>0742874752</Cell></customerDetail></UpdateCustomer></soap:Body></soap:Envelope>

  • Anonymous
    September 06, 2010
    This looks great but the datatype of my XML field has been set to "text" so none of this works.... Any suggestions on how this could be done?

  • Anonymous
    September 12, 2010
    @ Tommy, I think you're pretty much stuck unless you can convert the text to the XML data type, sorry! Simon

  • Anonymous
    October 04, 2010
    I get this error: Column or parameter #2: Cannot find data type XML Any suggestions?

  • Anonymous
    October 05, 2010
    @ Mike, are you using an older version of SQL Server? Apart from that, I stumped I'm afraid. Simon

  • Anonymous
    October 20, 2010
    Tommy, i am pretty certain you can CAST the column to XML.

  • Anonymous
    February 17, 2011
    Excellent article and sample. I searched a lot of the articles and samples about query xml data. Most of samples weren't work. But your's sample work very well and explianed clearly. Thank you so much.

  • Anonymous
    February 17, 2011
    @ Haw, thanks - and it's my pleasure! Simon

  • Anonymous
    August 26, 2011
    Nice ! Good reading for Flattening XML in SQL

  • Anonymous
    February 21, 2012
    This is excellent stuff .. which I understood real quick.. thanks again for the good work !!!!

  • Anonymous
    June 12, 2012
    You saved my day. Thank you so much for this.

  • Anonymous
    July 15, 2012
    Simon, Thanks for the nice, simple example of how to shred multip levels of "Element Based" XML. --Jeff Moden

  • Anonymous
    October 17, 2012
    no example how to do where clause on xml field

  • Anonymous
    February 04, 2013
    Not sure if anyone is still reading, but I am curious how you would sum child nodes, in this example how could you sum the ages of the two entries? Thanks!

  • Anonymous
    October 17, 2013
    here i give a example for XML in SQL create xml schema collection cricketschemacollection AS N'<xsd:schema xmlns:xsd="www.w3.org/.../XMLschema"> <xsd:element name="MatchDetails"> <xsd:complexType> <xsd::complexContent> <xsd:restiriction base="xsd:anyType"> <xsd:sequences> <xsd:element name="Team" minOccurs="0" maxOccurs="unbounded"> <xsd:complexType> <xsd::complexContent> <xsd:restiriction base="xsd:anyType"> <xsd:sequences/> <xsd:attribute name="country"type="xsd:string"/> <xsd:attribute name="score"type="xsd:string"/> </xsd:restiriction> </xsd::complexContent> </xsd:complexType> </xsd:element> </xsd:sequences> </xsd:restiriction> </xsd::complexContent> </xsd:complexType> </xsd:element> </xsd:schema>'

  • Anonymous
    December 09, 2013
    Better late than never. This very well written article got me started with extracting XML columns. Thanks, LA Guy :)

  • Anonymous
    March 24, 2014
    Perfect!! Just what i was looking for.Well could you post some more complex examples on xml.I am trying to query xml of dtsx package to get information about the tables/SP/Queries used in it and as dtsx package is a little bit complex , an example would help me to achieve the result what i seek Also would like if you could post selecting nodes with particular conditions/value and XML in where clause

  • Anonymous
    July 14, 2014
    Can we avoid using CROSS APPLY and instead use JOIN

  • Anonymous
    August 11, 2014
    Excellent Work. I have cleared my all the confusion . Thanks A lot.