Update XML node (in an XML column) in SQL Server 2005 with another column value in the same row

2
votes

I have a temp table with an XML column (XMLColumn) and a VARCHAR column (ABCColumn).

I am trying to replace the value of a particular XML node within XMLColumn with the value that is in ABCColumn of the same row. I need to do this for every row within the temp table. Is this possible to do without a cursor that would hold the replacement value for each fetch?

I have tried doing an update to the temp table using

XmlColumn.modify('replace value of ... with ...')

but I do not have a variable in this case. I need to get the value of a XYZColumn and use it as the replacement value. Every example I have seen either uses a variable or a hardcoded value - not a column value of the same row.

Also, I am using SQL Server 2005. Does anyone have any ideas?

2
sql-serverxmltsql

2 Answers

3
votes

Like This:

UPDATE  YourTable
SET     xml_col.modify(
    'replace value of (/root/val/text())[1] with sql:column("val_col")'
    )

(thanks to Andromar; I just edited his answer and used his SQLFiddle, (which works for this one too.))

3
votes

EDIT: I voted for RBarryYoung's answer, use that instead!

Only way I can think of is a cursor:

declare cur_t cursor for (select id, val_col from YourTable)
declare @id int
declare @val int
open cur_t

fetch cur_t into @id, @val
while @@fetch_status = 0
    begin
    update  YourTable
    set     xml_col.modify(
        'replace value of (/root/val/text())[1] with sql:variable("@val")')
    where   id = @id
    fetch cur_t into @id, @val
    end  

close cur_t
deallocate cur_t

Example at SQL Fiddle.