CODEWITHSUNDEEP
sqlsql-serverxml
tostringtheory
tostringtheory

Reputation: 877

SQL XML Parsing Query for Element Hierarchy

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.

Upvotes: 1

Views: 342

Answers (1)

podiluska
podiluska

Reputation: 51494

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

Upvotes: 2

Related Questions