SELECT * FROM XML

This is a 6 year old post which I originally posted in my previous blog site.

May 30 2010 11:49PM

Most people find it very difficult to deal with XML documents in TSQL as there is no way to run a ‘blind’ SELECT * query on an XML document to get a quick view of the content stored in it. A “select TOP N *” query can quickly give you a few records from the table which will give you an idea about the structure of the table and the type of values stored in the columns.  One of the common queries that I run on a table that I am not familiar with is

SELECT TOP 1 * FROM tablename

This query will give me one record that I can review and understand the structure of the table. However, it is really hard to do something similar for an XML document. The “*” operator does not work for XML and hence I can write a query on the XML document only if I know the structure of the XML.

To make this easier, I have come up with a function that can give you a “SELECT * FROM XML” kind of functionality. You can pass an XML document to the function and it will return a tabular representation of the XML data. Here is an example that shows how you can use this function.

declare @x xml
select @x = '
<employees>
    <emp name="jacob"/>
    <emp name="steve">
        <phone>123</phone>
    </emp>
</employees>
'
SELECT * FROM dbo.XMLTable(@x) 

/*
NodeName  NodeType  XPath                        TreeView      Value XmlData
--------- --------- ---------------------------- ------------- ----- -------------
employees Element   employees[1]                 employees     NULL  &amp;lt;employees&amp;gt;..
emp       Element   employees[1]/emp[1]              emp       NULL  &amp;lt;emp name=&amp;quot;..
name      Attribute employees[1]/emp[1]/@name            @name jacob NULL
emp       Element   employees[1]/emp[2]              emp       NULL  &amp;lt;emp name=&amp;quot;..
name      Attribute employees[1]/emp[2]/@name            @name steve NULL
phone     Element   employees[1]/emp[2]/phone[1]         phone 123   &amp;lt;phone&amp;gt;123&amp;lt;..
*/

The ‘XPath’ column may be very helpful as it shows the XPath expression that you can use to retrieve a specific value from the XML document. For example, to retrieve the phone number, you can copy the XPath expression from the above result and directly put it in a query such as:

SELECT @x.value('employees[1]/emp[2]/phone[1]','VARCHAR(20)') AS Phone

/*
Phone
--------------------
123
*/

Here is the complete listing of the function.

I have posted the code to gist so that people playing with this code can extend it and submit revisions.

Next Steps

  1. The function currently does not support namespaces. The next version will add support for namespaces
  2. Let me know your comments and feedback on this function.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s