Extracting data from XML in Sql Server column

t-sql, xml

Solution

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).

Problem

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: - This will get me the value inside the vnode (which is empty), but I need the value for the attribute marked "v". - 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.

Original source