Extract object from xml to SQL Server

Question

I have this query:

DECLARE @XML XML = 
'<det nItem="1">
    <prod>
        <cProd>222</cProd>
    </prod>
</det>
<det nItem="2">
    <prod>
        <cProd>888</cProd>
    </prod>
</det>'

SELECT
    det.value('@nItem', 'varchar(max)') as nItem,
    det_prod.value('(cProd)[1]', 'varchar(max)') as cProd
FROM
    @XML.nodes('/det') AS det(det),
    @XML.nodes('/det/prod') AS det_prod(det_prod)

It returns this result set:

nItem   cProd
-------------
   1    222
   1    888
   2    222
   2    888

but I want this returned like this:

 nItem  cProd
 -------------
   1    222
   2    888

Is there a way to do it without parse each <det> as a single xml?

David Browne - Microsoft · Accepted Answer · 2018-07-13 17:12:47Z

1

You are CROSS JOINing the nodes. Instead use CROSS APPLY to join each det node to its related prod nodes. EG:

DECLARE @XML XML = 
'<det nItem="1">
    <prod>
        <cProd>222</cProd>
    </prod>
</det>
<det nItem="2">
    <prod>
        <cProd>888</cProd>
    </prod>
</det>'

SELECT
    det.value('@nItem', 'varchar(max)') as nItem,
    det_prod.value('(cProd)[1]', 'varchar(max)') as cProd

FROM
    @XML.nodes('/det') AS det(det)
CROSS APPLY
    det.det.nodes('prod') AS det_prod(det_prod)

answered Jul 13, 2018 at 17:12

David Browne - Microsoft

90.8k7 gold badges53 silver badges87 bronze badges

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

Comments

Yogesh Sharma · Accepted Answer · 2018-07-13 17:10:51Z

1

You are doing cross join you need

SELECT
    det.value('@nItem', 'varchar(max)') as nItem,
    det.value('.', 'varchar(max)') as cProd
FROM
    @XML.nodes('/det') AS det(det);

answered Jul 13, 2018 at 17:10

Yogesh Sharma

50.2k5 gold badges31 silver badges53 bronze badges

Collectives™ on Stack Overflow

Extract object from xml to SQL Server

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related