how to get values inside an xml column, when it's of type nvarchar.

Question

My question is similar to this one: http://stackoverflow.com/questions/3061804/choose-a-xml-node-in-sql-server-based-on-max-value-of-a-child-element except that my column is NOT of type XML, it's of type nvarchar(max).

I want to extract the XML node values from a column that looks like this:

<Data>
<el1>1234</el1>
<el2>Something</el2>
</Data>

How can I extract the values '1234' and 'Something' ?

doing a convert and using the col.nodes is not working. CONVERT(XML, table1.col1).value('(/Data/el1)[1]','int') as 'xcol1',

After that, I would like to do a compare value of el1 (1234) with another column, and update update el1 as is. Right now I'm trying to just rebuild the XML when passing the update: ie

Update table set col1 ='<Data><el1>'+@col2+'</el1><el2>???</el2>

Answer 1

You've got to tell SQL Server the number of the node you're after, like:

(/Data/el1)[1]
           ^^^

Full example:

declare @t table (id int, col1 varchar(max))
insert @t values (1, '<Data><el1>1234</el1><el2>Something</el2></Data>')

select  CAST(col1 as xml).value('(/Data/el1)[1]', 'int')
from    @t

--> 
1234

SQL Server provides a modify function to change XML columns. But I think you can only use it on columns with the xml type. Here's an example:

declare @q table (id int, col1 xml)
insert @q values (1, '<Data><el1>1234</el1><el2>Something</el2></Data>')

update  @q
set     col1.modify('replace value of (/Data/el1/text())[1] with "5678"')

select  *
from    @q

-->
<Data><el1>5678</el1><el2>Something</el2></Data>

At the end of the day, SQL Server's XML support makes simple things very hard. If you value maintainability, you're better off processing XML on the client side.