Extract data from XML in a Sql server column

Question

I just discovered (as needed) that TSQL has some functions for extracting data from columns containing XML. I have a Sql Server column containing XML data (although the column type is not xml ... it varchar). I have to extract a subset of the data from the column. A simplified entry would look something like this:

declare @Table as table(id char(1), datacolumn xml)

insert into @table
select 'a', '<root><vnode v="5" /><vnode v="8" /></root>'

insert into @table
select 'b', '<root><vnode v="7" /></root>'


id   datacolumn
---  -----------------------------------------
a    <root><vnode v="5" /><node v="8" /></root>
b    <root><vnode v="7" /></root>

So, I would like to find a query that returns something line by line:

id   data
---  ----
a    5
a    8
b    7

I have come to the point that I can get some information ... but I am missing some concepts.

select id, T.c.value('node[1]', 'varchar(100)') mdata
from @table
cross apply datacolumn.nodes('/root') AS T(c)

Problems:

• This will give me a value inside vnode (which is empty), but I need a value for the attribute marked "v".
• Also, since I have [1], I get the first node ... but I want all of them ... but not yet understood the concept for this.

Answer 1

SELECT  id, T.c.value('@v', 'NVARCHAR(MAX)')
FROM    @table
CROSS APPLY
        datacolumn.nodes('/root/vnode') AS T(c)

datacolumn.nodes('/root/vnode') /root/vnode XML.

b b 2.

value() XPath node, .

node, , @ ( , , node)..