Select statement on XML not returning desired output

Question

DECLARE @barcode XML

            SET @barcode = '<BCODES><BCODE>a</BCODE><BCODE>b</BCODE><BCODE>c</BCODE><BCODE>d</BCODE></BCODES>'

            --INSERT INTO #barcode
            SELECT      v.x.value('BCODE[1]','VARCHAR(15)') AS Barcodes
            FROM        @barcode.nodes('BCODES') v(x)

Result-->

Barcodes

a

I'm unable to get b,c and d in the output.

To clarify: you expect SELECT to return a, b, c, d but only a is returned? — stealththeninja
– stealththeninja, Commented Nov 14, 2017 at 7:21

gotqn · Accepted Answer · 2017-11-14 07:04:37Z

1

Just do this:

SELECT v.x.value('.','VARCHAR(15)') AS Barcodes
FROM @barcode.nodes('BCODES/BCODE') v(x)

The idea is first to get all BCODE nodes and then to extract its values.

answered Nov 14, 2017 at 7:04

gotqn

44k47 gold badges167 silver badges257 bronze badges

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

Comments

Yogesh Sharma · Accepted Answer · 2017-11-14 07:06:05Z

0

You could try :

DECLARE @barcode XML;
SET @barcode = '<BCODES><BCODE>a</BCODE><BCODE>b</BCODE><BCODE>c</BCODE><BCODE>d</BCODE></BCODES>';

            --INSERT INTO #barcode

SELECT barcode = n.v.value('.[1]', 'NVARCHAR(128)')
FROM @barcode.nodes('BCODES/BCODE') AS n(v);

Result :

barcode 
a
b
c
d

answered Nov 14, 2017 at 7:06

Yogesh Sharma

50.2k5 gold badges31 silver badges53 bronze badges

Collectives™ on Stack Overflow

Select statement on XML not returning desired output

Barcodes

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Barcodes

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related