mardi 17 mars 2015

How do I parse this piece of XML?

I have the following piece of XML in my SQL:



DECLARE @xml XML
SET @xml = '<?xml version="1.0" encoding="UTF-8"?>
<BillingAddresses>
<BillingAddress Winner="W1">
<Losers>
<Loser Id="L1" />
<Loser Id="L2" />
<Loser Id="L3" />
</Losers>
</BillingAddress>
<BillingAddress Winner="W10">
<Losers>
<Loser Id="L65" />
</Losers>
</BillingAddress>
</BillingAddresses>'


I'd like to fetch data in the following manner.



Winner | LoserID
W1 L1
W1 L2
W1 L3
W10 L65


I can get to the Winner attribute of BillingAddress:



SELECT Col.value('(@Winner)[1]', 'varchar(30)')
FROM @xml.nodes('/BillingAddresses/BillingAddress') Rev(Col)


but I am at a loss how to get to the Id attribute of the Loser nodes


Aucun commentaire:

Enregistrer un commentaire