SQL XML Parsing Query for Element Hierarchy

Question

I am attempting to write a SQL Query that will take in an XML object of undefined schema (YAY!) and transform it to a two column table of ElementName, Value columns. I was able to get a simple query down after some time (I am not a SQL person by any means).

DECLARE @strXml XML
SET @strXml = '<xml>
  <FirstName>TEST</FirstName>
  <LastName>PERSON</LastName>
  <DOB>1/1/2000</DOB>
  <TestObject>
    <SomeProperty>CHECKED</SomeProperty>
    <EmbeddedObject>
        <SomeOtherProperty>NOT CHECKED</SomeOtherProperty>
    </EmbeddedObject>
  </TestObject>
</xml>'

DECLARE @XmlMappings TABLE
(        
    NodeName VARCHAR(64),
    Value VARCHAR(128)
) 
INSERT INTO @XmlMappings
SELECT doc.col.value('fn:local-name(.)[1]', 'varchar(64)') AS ElementName,
       doc.col.value('.', 'varchar(128)') AS Value
FROM @strXml.nodes('/xml/*') doc(Col)
SELECT * FROM @XmlMappings

This query can handle the simple condition of the specified XML with only the first level elements. However elements such as TestObject and EmbeddedObject end up flattened. What I am looking for is to get some type of mapping like

ElementName                                 | Value
=====================================================
FirstName                                   | TEST
LastName                                    | PERSON
DOB                                         | 1/1/2000
TestObject.SomeProperty                     | CHECKED
TestObject.EmbeddedObject.SomeOtherProperty | NOT CHECKED

The hard part for me is the hierarchical structure with the . operator. I don't care if it is some other delimiter than . that gets output, it is more of just getting the output done, and I don't know enough about XML in SQL to be able to know even what to query.

Please note that I can also not use OPENXML since this is looking to be deployed on SQL Azure which does not support that feature at this time.

podiluska · Accepted Answer · 2014-05-13 09:06:32Z

2

With a CTE and cross apply

;with cte as
(
    select
        convert(varchar(100), x.n.value('fn:local-name(.)','varchar(100)') ) as path,
        convert(varchar(100), x.n.value('fn:local-name(.)','varchar(100)') ) AS name,
        x.n.query('*') AS children,
        x.n.value('.','varchar(1000)') as value
    from @strxml.nodes('/xml/*') AS x(n)
    union all
    select
        convert(varchar(100), x.path + '.' + c.n.value('fn:local-name(.)','varchar(100)') ),
        convert(varchar(100), c.n.value('fn:local-name(.)','varchar(100)') ) ,
        c.n.query('*'),
        c.n.value('.','varchar(1000)')
    from cte x
        cross apply x.children.nodes('*') AS c(n)
)

select path, value from cte where datalength(children) = 5

answered May 13, 2014 at 9:06

podiluska

51.6k7 gold badges102 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

tostringtheory Over a year ago

WOW! Works perfectly. I've been trying to look at it to figure out exactly how it works, and think I have most of it down now. My only question is why the filter on datalength(children) = exactly 5? What does that signify? I've proven that it still works for further nested object and such, so no issue, just really intrigued.

podiluska Over a year ago

@tostringtheory It's filtering out elements of the results set that have children. Not sure why the field size would be 5 and not 0, but that's the way it is :)

Collectives™ on Stack Overflow

SQL XML Parsing Query for Element Hierarchy

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related