CODEWITHSUNDEEP
sql-serverxmlt-sql
Rajesh Nagda
Rajesh Nagda

Reputation: 131

How to convert xml value to a table

The below code is returning only 6 rows with only 1st value. I want 6 rows with there respective values 

    DECLARE @txml XML 
    SET @txml ='
    <row ch="&#x9;" unicode_value="9" cnt="3" />
    <row ch="&#xA;" unicode_value="10" cnt="5" />
    <row ch="&#xD;" unicode_value="13" cnt="5" />
    <row ch=" " unicode_value="32" cnt="962" />
    <row ch="&amp;" unicode_value="38" cnt="32" />
    <row ch="(" unicode_value="40" cnt="8" />
    '
    SELECT x.value('(/row/@cnt)[1]', 'int') AS cnt
    FROM  @txml.nodes('/row') AS tbl( x )

Upvotes: 0

Views: 86

Answers (1)

MarcinJuraszek
MarcinJuraszek

Reputation: 125630

You're taking first item value because that's how your XPath expression is defined: '(row/@cnt)[1]'

[1] states for first occurrence matching row/@cnt expression, what takes cnt attribute value from the first <row> element.

Change your query to:

SELECT x.value('@cnt', 'int') AS cnt
FROM  @txml.nodes('/row') AS tbl( x )

It will take cnt attribute value for current <row> element, not always the first one.

DEMO

Upvotes: 1

Related Questions