Parse the XML in SQL Server

Question

<ROOT>
    <arn>arn001</arn>
    <arn>arn002</arn>
</ROOT>

Tried the following code though

SELECT
ARN.value('(//arn/text())[1]','VARCHAR(100)') AS arns --TAG
FROM
@xml.nodes('/ROOT')AS TEMPTABLE(ARN)

It returns only first value

What do you want it to return?

Leon Bambrick
– Leon Bambrick

2015-04-27 10:31:16 +00:00
Commented Apr 27, 2015 at 10:31 — Leon Bambrick
– Leon Bambrick, Commented Apr 27, 2015 at 10:31

har07 · Accepted Answer · 2015-04-27 10:35:35Z

1

Try this way :

declare @xml xml = '<ROOT>
        <arn>arn001</arn>
        <arn>arn002</arn>
    </ROOT>'

SELECT
X.value('.','VARCHAR(100)') AS arns 
FROM
@xml.nodes('/ROOT/arn')AS T(X)

SQL Fiddle

answered Apr 27, 2015 at 10:35

har07

89.5k12 gold badges87 silver badges143 bronze badges

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

Collectives™ on Stack Overflow

Parse the XML in SQL Server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related