Reputation: 39
<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
Upvotes: 0
Views: 27
Reputation: 89335
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)
Upvotes: 1