CODEWITHSUNDEEP
sqlxml
Loser Coder
Loser Coder

Reputation: 2428

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

My question is similar to this one: 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>

Upvotes: 1

Views: 2219

Answers (1)

Andomar
Andomar

Reputation: 238196

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.

Upvotes: 1

Related Questions