Justin Trenary Justin Trenary - 9 months ago 57
SQL Question

Parse XML to Table in SQL (Only nodes that have values)

I am trying to find a way to parse a rather large XML file into a table that simply shows the name of the node, and it's value. There are no attributes to worry about in the XML, but there are several nested nodes.

Because there the XML is generated dynamically, there is no way of knowing the exact depth of that nesting. But it is safe to assume only the LAST CHILD NODE of any branch will contain data.

This is my attempt at a solution, but the problem is that when accessing a parent node, the data from every child within gets concatenated as the value.

SET @ProjectXML = '

<Title>This is a Test Title</Title>
<Description>This is a Test Description</Description>
<Name>Test Name</Name>
n.value('local-name(.)','VARCHAR(100)') AS Name,
n.value('.','VARCHAR(MAX)') AS Value

FROM @ProjectXML.nodes('//*') AS ProjectXML(n)

Which results in:

Project 11This is a Test TitleThis is a Test Description1Test NameHiThere
ProjectId 1
OrganizationId 1
Title This is a Test Title
Description This is a Test Description
ProjectType 1Test NameHiThere
Id 1
Name Test Name
Outer HiThere
Inner1 Hi
Inner2 There

Is there a way (using xquery perhaps?) to ignore the parent nodes which contain no data, and only return values for the child nodes which do?

**Bonus points if it is possible to return the Name of each result as a formatted list of it's parent nodes , such as:

Project - ProjectId 1
Project - OrganizationId 1
Project - Title This is a Test Title
Project - Description This is a Test Description
Project - Project Type - Id 1
Project - Project Type - Name Test Name
Project - Project Type - Outer - Inner1 Hi
Project - Project Type - Outer - Inner2 There

Answer Source

Take a peek at http://beyondrelational.com/modules/2/blogs/28/posts/10495/xquery-lab-58-select-from-xml.aspx

Select * from XMLTable(@ProjectXML) where value is not null

Returns more than you may want, but you can tailor to your needs

enter image description here