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

Answer 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