Extracting data from XML in Sql Server column

Question

I've just discovered (by necessity) that TSQL has some functionality to extract data from columns that contain XML. I've got a Sql Server column that contains XML data (though the column type isn't xml...it's varchar). I'm supposed 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'd like to come up with a query that would return something along the lines of:

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

I've gotten to the point where I'm able to get some information...but I'm missing some concepts.

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

Problems:

1. This will get me the value inside the vnode (which is empty), but I need the value for the attribute marked "v".
2. Also, because I've got [1] in there, I'm getting the first node...but I want all of them...but haven't groked the concept for that yet.

Answer 1

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

The datacolumn.nodes('/root/vnode') returns all instances of /root/vnode for each XML.

There are 2 of those in a and 1 in b.

value() returns the value of an XPath expression relative to the node returned by the previous step.

Since you want to return the value of an attribute of the node in question, you need just prepend the attribute name with a @ (which tells that you want a child attribute, not a child node).