XML to SQL result set. Multiple nesting levels

Question

Essentially I am trying to transform the XML I have into a flat data structure using SQL.

My XML is in the following format (I've altered the XML into a subset to make it simpler for my example):

<Actions>
    <AddComponent>
        <Action>
            <DataItem>
                <GroupId>1</GroupId>
                <Data>
                    <Id>100</Id>
                    <Value>Value A</Value>
                    <Children>
                        <Data>
                            <Id>200</Id>
                            <Value>Value B</Value>
                            <Children>
                                <Data>
                                    <Id>300</Id>
                                    <Value>Value C1</Value>
                                </Data>
                                <Data>
                                    <Id>301</Id>
                                    <Value>Value C2</Value>
                                    <Children />
                                </Data>
                            </Children>
                        </Data>
                    </Children>
                </Data>
            </DataItem>
            <DataItem>
                <GroupId>2</GroupId>
                <Data>
                    <Id>101</Id>
                    <Value>Value A</Value>
                    <Children>
                        <Data>
                            <Id>200</Id>
                            <Value>Value B</Value>
                            <Children>
                                <Data>
                                    <Id>302</Id>
                                    <Value>Value C3</Value>
                                </Data>
                            </Children>
                        </Data>
                    </Children>
                </Data>
            </DataItem>
        </Action>
    </AddComponent>
</Actions>

The output I am looking for is the following:

+---------+-----+----------+----------+
| GroupId | Id  |  Value   | ParentId |
+---------+-----+----------+----------+
|       1 | 100 | Value A  | NULL     |
|       1 | 200 | Value B  | 100      |
|       1 | 300 | Value C1 | 200      |
|       1 | 301 | Value C2 | 200      |
|       2 | 101 | Value A  | NULL     |
|       2 | 200 | Value B  | 101      |
|       2 | 302 | Value C3 | 200      |
+---------+-----+----------+----------+

I am not sure on the best method of recursively going through the 'Children' elements. As the number of children could be ad infinitum.

check this : http://stackoverflow.com/questions/14712864/how-to-query-values-from-xml-nodes — Deep, Jan 07 '15 at 09:23

Mikael Eriksson · Accepted Answer · 2015-01-07T14:05:30.347

select DI.X.value('(GroupId/text())[1]', 'int') as GroupId,
       D.X.value('(Id/text())[1]', 'int') as Id,
       D.X.value('(Value/text())[1]', 'nvarchar(50)') as Value,
       D.X.value('(../../Id/text())[1]', 'int') as ParentID
from @XML.nodes('/Actions/AddComponent/Action/DataItem') as DI(X)
  cross apply DI.X.nodes('.//Data') as D(X)

.//Data will give you all the Data nodes recursively.

Note: Using the parent axis (../../Id/text())[1] to get the ParentID could be a performance killer for you. Try it out with your data to see if it performance is acceptable.

Update:

Doing the parent axis in a nodes() call looks like it will give you a much better query plan.

select DI.X.value('(GroupId/text())[1]', 'int') as GroupId,
       D.X.value('(Id/text())[1]', 'int') as Id,
       D.X.value('(Value/text())[1]', 'nvarchar(50)') as Value,
       P.X.value('text()[1]', 'int') as ParentID
from @XML.nodes('/Actions/AddComponent/Action/DataItem') as DI(X)
  cross apply DI.X.nodes('.//Data') as D(X)
  outer apply D.X.nodes('../../Id') as P(X)

Deep · Answer 2 · 2015-01-07T11:24:06.900

Check this query if it can help :

DECLARE @XML XML = '<Actions><AddComponent><Action><DataItem><GroupId>1</GroupId><Data><Id>100</Id><Value>Value A</Value><Children><Data><Id>200</Id><Value>Value B</Value><Children><Data><Id>300</Id><Value>Value C1</Value></Data><Data><Id>301</Id><Value>Value C2</Value><Children /></Data></Children></Data></Children></Data></DataItem><DataItem><GroupId>2</GroupId><Data><Id>101</Id><Value>Value A</Value><Children><Data><Id>200</Id><Value>Value B</Value><Children><Data><Id>302</Id><Value>Value C3</Value></Data></Children></Data></Children></Data></DataItem></Action></AddComponent></Actions>';

;WITH cte
     AS (SELECT c.value('(GroupId)[1]', 'varchar(30)')    groupid,
                c.value('(Data/Id)[1]', 'varchar(30)')    id,
                c.value('(Data/Value)[1]', 'varchar(30)') value,
                c.query('Data/Children')                  AS childdata,
                Cast(NULL AS VARCHAR(30))                 AS parentId
         FROM   (SELECT @xml) temp(x)
                CROSS apply x.nodes('//Actions/AddComponent/Action/DataItem') tabl(c)
         UNION ALL
         SELECT groupid,
                c.value('(Id)[1]', 'varchar(30)')    id,
                c.value('(Value)[1]', 'varchar(30)') value,
                c.query('Children')                  AS childdata,
                Cast(cte.id AS VARCHAR(30))          AS parentId
         FROM   cte
                CROSS apply childdata.nodes('/Children/Data') tabl(c))
SELECT groupid,
       id,
       value,
       parentId
FROM   cte
ORDER  BY groupid,id

As you can see in my CTE, I first extracted all the parent nodes data and their child XML then recursively get all child data from child XML.

XML to SQL result set. Multiple nesting levels

2 Answers2