How to get list of nested objects from xml with SQL Server?

Question

I want go nested objects from xml objects. Here is sample xml:

<persons>
    <person>
        <person_name>
            <firstName>Jon</firstName>
            <lastName>Johnson</lastName>
        </person_name>
        <person_data>
            <number>1</number>
        </person_data>
        <body_parts>
            <body_part>Head</body_part>
            <body_part>Leg</body_part>
            <body_part>Nose</body_part>
        </body_parts>
    </person>
    <person>
        <person_name>
            <firstName>Kathy</firstName>
            <lastName>Carter</lastName>
        </person_name>
        <person_data>
            <number>2</number>
        </person_data>
        <body_parts>
            <body_part>Head</body_part>
            <body_part>Palm</body_part>
            <body_part>Eye</body_part>
        </body_parts>
    </person>
    <person>
        <person_name>
            <firstName>Bob</firstName>
            <lastName>Burns</lastName>
        </person_name>
        <person_data>
            <number>3</number>
        </person_data>
        <body_parts>
            <body_part>Leg</body_part>
        </body_parts>
    </person>
</persons>

Here is my current code:

DECLARE @xml XML;  
DECLARE @iterator int = 1
SET @xml = '<persons>
    <person>
        <person_name>
            <firstName>Jon</firstName>
            <lastName>Johnson</lastName>
        </person_name>
        <person_data>
            <number>1</number>
        </person_data>
        <body_parts>
            <body_part>Head</body_part>
            <body_part>Leg</body_part>
            <body_part>Nose</body_part>
        </body_parts>
    </person>
    <person>
        <person_name>
            <firstName>Kathy</firstName>
            <lastName>Carter</lastName>
        </person_name>
        <person_data>
            <number>2</number>
        </person_data>
        <body_parts>
            <body_part>Head</body_part>
            <body_part>Palm</body_part>
            <body_part>Eye</body_part>
        </body_parts>
    </person>
    <person>
        <person_name>
            <firstName>Bob</firstName>
            <lastName>Burns</lastName>
        </person_name>
        <person_data>
            <number>3</number>
        </person_data>
        <body_parts>
            <body_part>Leg</body_part>
        </body_parts>
    </person>
</persons>';  

SELECT p.p.value('(./person_name/firstName/text())[1]','varchar(20)') AS firstName,
       p.p.value('(./person_name/lastName/text())[1]','varchar(20)') AS lastName,
       p.p.value('(./body_parts/body_part)[1]','varchar(20)') AS X
FROM @XML.nodes('persons/person') p(p)
WHERE p.p.value('(./person_data/number/text())[1]','int') = 2;

And result:

Kathy   Carter  Head

How can i get all the body_parts?

By the way, you can filter directly in XQuery .nodes('persons/person[person_data[number[text() = "2"]]]') p(p) — Charlieface
– Charlieface, Commented Jun 24, 2021 at 12:38

Thom A · Accepted Answer · 2021-06-24 11:01:18Z

1

As I mentioned in the prior answer's comments, add a reference to the body_part node in the FROM:

SELECT p.p.value('(./person_name/firstName/text())[1]','varchar(20)') AS firstName,
       p.p.value('(./person_name/lastName/text())[1]','varchar(20)') AS lastName,
       bp.bp.value('(./text())[1]','varchar(20)') AS body_part
FROM @XML.nodes('persons/person') p(p)
     CROSS APPLY p.p.nodes('./body_parts/body_part') bp(bp)
WHERE p.p.value('(./person_data/number/text())[1]','int') = 2;

answered Jun 24, 2021 at 11:01

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

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

Collectives™ on Stack Overflow

How to get list of nested objects from xml with SQL Server?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related