CODEWITHSUNDEEP
sql-serverxml
Kliver Max
Kliver Max

Reputation: 5299

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

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?

Upvotes: 0

Views: 254

Answers (1)

Thom A
Thom A

Reputation: 96004

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;

Upvotes: 1

Related Questions