CODEWITHSUNDEEP
sqlsql-serverxmlsql-server-2008
Zeaous
Zeaous

Reputation: 13

Parse XML with multilevel nesting in SQL

I'm trying to Parse some XML having multiple nesting levels in SQL. The problem I hit into here is how to write my query generic enough for it to Parse the XML below without having to hard-code the node path e:g 

EXEC sp_xml_preparedocument @handle OUTPUT, @xml

INSERT #Root(ID)
SELECT *
FROM OPENXML(@handle, '/Root')
WITH (ID VARCHAR(100))

INSERT #ConditionSet(Operator)
SELECT *
FROM OPENXML(@handle, '/Root/ConditionSet')
WITH (Operator VARCHAR(100))

INSERT #ConditionSet(Operator)
SELECT *
FROM OPENXML(@handle, '/Root/ConditionSet/ConditionSet')
WITH (Operator VARCHAR(100))

INSERT #ConditionSet(Operator)
SELECT *
FROM OPENXML(@handle, '/Root/ConditionSet/ConditionSet/ConditionSet')
WITH (Operator VARCHAR(100))

Is there a better way to Parse the following XML in SQL and represent all data in tabular form?

<?xml version="1.0"?>
-<Root ID="414141" Source="AudienceBuilder">
  -<ConditionSet Operator="I">
    -<Condition Operator="E" ID="74373">
      -<Relationship ID="56756">
        <Relationship ID="67868"/>
      </Relationship>
      -<Value>
        <![CDATA[ABC]]>
      </Value>
    </Condition>
    -<ConditionSet Operator="O">
      -<Condition Operator="E" ID="6566">
        -<Relationship ID="7658">
          <Relationship ID="6547"/>
        </Relationship>
        -<Value>
          <![CDATA[DEF]]>
        </Value>
      </Condition>
      -<Condition Operator="E" ID="96967">
        -<Relationship ID="3884">
          <Relationship ID="9954"/>
        </Relationship>
        -<Value>
          <![CDATA[GHI]]>
        </Value>
      </Condition>
      -<ConditionSet Operator="A">
        -<Condition Operator="E" ID="31654">
          -<Relationship ID="57894">
            <Relationship ID="8532"/>
          </Relationship>
          -<Value>
            <![CDATA[JKL]]>
          </Value>
        </Condition>
        -<Condition Operator="E" ID="65636">
          -<Relationship ID="843">
            <Relationship ID="7473"/>
          </Relationship>
          -<Value>
            <![CDATA[MNO]]>
          </Value>
        </Condition>
      </ConditionSet>
    </ConditionSet>
  </ConditionSet>
</Root>

Any inputs/recommendations are highly appreciated :)

Thank you

Zeaous

Upvotes: 0

Views: 3151

Answers (1)

marc_s
marc_s

Reputation: 755361

Assuming you have your XML in a SQL Server variable called @XML, you can use the native XQuery support in SQL Server 2005 and newer to do this much more elegantly and efficiently:

DECLARE @XML XML = '...(your XML here).....' 

SELECT
    RootID = @xml.value('(/Root/@ID)[1]', 'int'),
    ConditionSetOperator = XC.value('@Operator', 'varchar(50)'),
    ConditionID =  XC2.value('@ID', 'int'),
    ConditionOperator = XC2.value('@Operator', 'varchar(50)')
FROM 
    @Xml.nodes('//ConditionSet') AS XT(XC)
CROSS APPLY
    xc.nodes('Condition') AS XT2(XC2)

This gives me an output of

enter image description here

With XQuery operators like .nodes() or .value(), you can easily "shred" an XML document into relational data and store that as needed.

The first call to @xml.nodes('//ConditionSet') will get a "pseudo" table for each matching node - so each <ConditionSet> node will be returned in the "pseudo" table XT as column XC, and then I can easily grab attributes (or XML elements) from that XML fragment using XQuery methods like .value().

Or I can even grab the list of sub-nodes <Condition> for each of those <ConditionSet> nodes - using the CROSS APPLY with a second call to .nodes()

Upvotes: 1

Related Questions