SELECT * FROM XML

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

1.
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  <employees>..
emp       Element   employees[1]/emp[1]              emp       NULL  <emp name="..
name      Attribute employees[1]/emp[1]/@name            @name jacob NULL
emp       Element   employees[1]/emp[2]              emp       NULL  <emp name="..
name      Attribute employees[1]/emp[2]/@name            @name steve NULL
phone     Element   employees[1]/emp[2]/phone[1]         phone 123   <phone>123<..
*/

Read more: Beyond Relational

Posted via email from jasper22's posterous