CODEWITHSUNDEEP
xmlsql-server-2008
punter
punter

Reputation: 460

Returning multiple rows for XML Column using Cross apply

I have a table tblTransI with the following data in a column of type XML called TransI_xmlDetails:

<DeliveryDetails>
  <Sender>
    <Name>Test Data</Name>
  </Sender>
  <Receivers>
    <Receiver id="1">
      <GiftVouchers>
        <Voucher>ABCD</Voucher>
        <Voucher>2345</Voucher>
      </GiftVouchers>
    </Receiver>
    <Receiver id="2">
      <GiftVouchers>
        <Voucher>1234</Voucher>
      </GiftVouchers>
    </Receiver>
    <Receiver id="3">
      <GiftVouchers>
        <Voucher>6789</Voucher>
      </GiftVouchers>
    </Receiver>
    <Receiver id="4">
      <GiftVouchers>
        <Voucher>WXYZ</Voucher>
      </GiftVouchers>
    </Receiver>
  </Receivers>
</DeliveryDetails>

I would like to create a query that returns multiple rows - one for each Voucher and I've tried the following query:

SELECT c.query('data(Voucher)') AS Id
FROM tblTransI
        CROSS APPLY TransI_xmlDetails.nodes('/DeliveryDetails/Receivers/Receiver/GiftVouchers') x(c)
WHERE TransI_xmlDetails.exist('/DeliveryDetails/Receivers/Receiver/GiftVouchers/Voucher') = 1

and it return following output:-

ABCD 2345
1234
6789
WXYZ

but I want output as :-

ABCD 
2345
1234
6789
WXYZ

how can I achieve this ?

Upvotes: 0

Views: 462

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

Try it like this: Paste this into an empty query window and transfer the idea into your code:

DECLARE @xml XML=
'<DeliveryDetails>
  <Sender>
    <Name>Test Data</Name>
  </Sender>
  <Receivers>
    <Receiver id="1">
      <GiftVouchers>
        <Voucher>ABCD</Voucher>
        <Voucher>2345</Voucher>
      </GiftVouchers>
    </Receiver>
    <Receiver id="2">
      <GiftVouchers>
        <Voucher>1234</Voucher>
      </GiftVouchers>
    </Receiver>
    <Receiver id="3">
      <GiftVouchers>
        <Voucher>6789</Voucher>
      </GiftVouchers>
    </Receiver>
    <Receiver id="4">
      <GiftVouchers>
        <Voucher>WXYZ</Voucher>
      </GiftVouchers>
    </Receiver>
  </Receivers>
</DeliveryDetails>';
SELECT c.value('.','varchar(max)') AS Id
FROM @xml.nodes('/DeliveryDetails/Receivers/Receiver/GiftVouchers/Voucher') x(c)

EDIT:

It shoudl suffice to go one level deeper in your nodes() and change the SELECT to value() instead of query():

Try it without your WHERE, the CROSS APPLY should do this implicitly.

SELECT c.value('.','varchar(max)') AS Id
FROM tblTransI
        CROSS APPLY TransI_xmlDetails.nodes('/DeliveryDetails/Receivers/Receiver/GiftVouchers/Voucher') x(c)
WHERE TransI_xmlDetails.exist('/DeliveryDetails/Receivers/Receiver/GiftVouchers/Voucher') = 1

Upvotes: 1

Related Questions