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