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 employees NULL &lt;employees&gt;.. emp Element employees/emp emp NULL &lt;emp name=&quot;.. name Attribute employees/emp/@name @name jacob NULL emp Element employees/emp emp NULL &lt;emp name=&quot;.. name Attribute employees/emp/@name @name steve NULL phone Element employees/emp/phone phone 123 &lt;phone&gt;123&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/emp/phone','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.
- The function currently does not support namespaces. The next version will add support for namespaces
- Let me know your comments and feedback on this function.