Home Select statement on XML not returning desired output
Reply: 2

Select statement on XML not returning desired output

Dattatray Raj
1#
Dattatray Raj Published in 2017-11-14 06:59:31Z
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.

gotqn
2#
gotqn Reply to 2017-11-14 07:04:37Z

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.

Yogesh Sharma
3#
Yogesh Sharma Reply to 2017-11-14 07:06:05Z

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
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.311352 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO