CODEWITHSUNDEEP
sqlsql-serverxmlt-sqlsql-server-2016
Yván Ecarri
Yván Ecarri

Reputation: 1738

SQL Server XML multi-level query

I have the following xml that I need to query:

declare @xml xml = '<root>
    <level1>
        <property1>Value1</property1>
        <property2>Value2</property2>
        <level2List>
            <level2>Child1</level2>
            <level2>Child2</level2>
        </level2List>
    </level1>
    <level1>
        <property1>Value3</property1>
        <property2>Value4</property2>
        <level2List>
            <level2>Child3</level2>
            <level2>Child4</level2>
        </level2List>
    </level1>
</root>'

And I need the following result:

Property1 Property2 Child
Value1    Value2    Child1
Value1    Value2    Child2
Value3    Value4    Child3
Value3    Value4    Child4

I have this query:

select col.value('Property1','varchar(100)') Property1,
       col.value('Property2','varchar(100)') Property2
from @xml.nodes('//root/level1') as tab(col)

But I can't figure out how to join a parent node with its children. Any idea?

Upvotes: 0

Views: 95

Answers (3)

Salman Arshad
Salman Arshad

Reputation: 272236

You can start searching from the bottom:

DECLARE @xml AS XML = '<root>
    <level1>
        <property1>Value1</property1>
        <property2>Value2</property2>
        <level2List>
            <level2>Child1</level2>
            <level2>Child2</level2>
        </level2List>
    </level1>
    <level1>
        <property1>Value3</property1>
        <property2>Value4</property2>
        <level2List>
            <level2>Child3</level2>
            <level2>Child4</level2>
        </level2List>
    </level1>
</root>';

SELECT
    n.value('../../property1[1]','varchar(100)') property1,
    n.value('../../property2[1]','varchar(100)') property2,
    n.value('.','varchar(100)') Child
FROM @xml.nodes('//level1//level2') AS x(n)

Or by extending your original attempt:

FROM @xml.nodes('//level1') AS n1(l1)
CROSS APPLY l1.nodes('.//level2') AS n2(l2)

Upvotes: 2

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

You can also use APPLY :

SELECT col.value('property1[1]', 'varchar(100)') AS property1,
       col.value('property2[1]', 'varchar(100)') AS property2,
       col1.value('text()[1]','varchar(100)') AS Child
FROM @xml.nodes('root/level1') AS tab(col) OUTER APPLY 
     col.nodes('level2List/level2') tab1(col1);

Upvotes: 1

Paweł Dyl
Paweł Dyl

Reputation: 9143

I would use following:

SELECT
    X.value('(../../property1)[1]', 'varchar(20)') Property1,
    X.value('(../../property2)[1]', 'varchar(20)') Property2,
    X.value('.', 'varchar(20)') Child
FROM @xml.nodes('//level2') N(X)

Upvotes: 2

Related Questions