Geek asks and answers

Refresh XML node (in an XML column) in SQL Server 2005 with a different column value in the same row

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

I am trying to replace the value of a specific XML node inside with a XMLColumnvalue that is on ABCColumnthe same line. I need to do this for every row in the temp table. Is it possible to do without a cursor that preserves the replacement value for each sample?

I tried to update in 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 XYZColumnand use it as a replacement value. In every example I saw, a variable or hard-coded value is used, not a column value of the same row.

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

+2
source share
2 answers

Like this:

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

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

+3
source

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

The only way I can think of is with the 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 in SQL Fiddle.

+3
source

More articles:


All Articles