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.
DECLARE @ProjectXML XML
SET @ProjectXML = '
<Title>This is a Test Title</Title>
<Description>This is a Test Description</Description>
n.value('local-name(.)','VARCHAR(100)') AS Name,
n.value('.','VARCHAR(MAX)') AS Value
FROM @ProjectXML.nodes('//*') AS ProjectXML(n)
Project 11This is a Test TitleThis is a Test Description1Test NameHiThere
Title This is a Test Title
Description This is a Test Description
ProjectType 1Test NameHiThere
Name Test Name
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
Select * from XMLTable(@ProjectXML) where value is not null
Returns more than you may want, but you can tailor to your needs