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

Question

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?

RBarryYoung · Accepted Answer · 2012-04-22 20:39:46Z

5

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

edited Apr 22, 2012 at 20:39

answered Apr 22, 2012 at 20:09

RBarryYoung

57k15 gold badges99 silver badges142 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

RBarryYoung Over a year ago

I just wrote and ran it on SQL 2005, so yes.

Jaden Over a year ago

Awesome. For some reason, I was thinking that sql:column (and its other variations) were a 2008 thing.

RBarryYoung Over a year ago

AFAIK, they've been in SQL 2005 all along.

Jaden Over a year ago

Well, you learn something new (and in this case very useful) every day. Thanks so much!

Andomar · Accepted Answer · 2012-04-22 20:29:24Z

3

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.

edited Apr 22, 2012 at 20:29

answered Apr 22, 2012 at 19:54

Andomar

239k55 gold badges387 silver badges412 bronze badges

1 Comment

Jaden Over a year ago

This is basically what I was doing before I decided to stop and ask if there was a better way.

Collectives™ on Stack Overflow

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

2 Answers 2

4 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related