How to update XML column with data from a subquery

Question

How to update XML column with data from a subquery

EA

Mr or Mrs. 500

Points: 521
More actions
December 22, 2008 at 9:51 am

#92908

I want to update the value of an attribute in an XML column by using the data returned from a subquery. I can't find any examples helping me to solve this. All the examples are using a known value between double brackets. I need to query the new value and fill it in.
I'm having two tables
table1
Nr newNr
1 100
5 23
table 2
Nr XMLData
1
5
I can update the Contract="n" attribute by using
UPDATE table1
SET xml.modify('replace value of (/One/@Contract/text())[1] with "100" ') where Nr=1
But.... I want to update the @Contract with the value of the table2 when joined on the Nr field.
Help is very appreciated!!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 1

You can do an update with inner join. This way you’ll be able to get the value from the lookup table. In your update statement you should use the function sql:column, because without it Xquery will not recognize the column by its name. Bellow is a small demo that does it:

use tempdb

go

--Creating the XML table and inserting some test data

create table XMLtbl (id int primary key, XMLCol xml)

go

insert into XMLtbl (id, XMLCol)

select 1, ' '

union all

select 2, ' '

go

--Creating the lookup table and inserting some data

create table ValTbl (id int primary key, val int not null)

go

insert into ValTbl (id, val)

select 1, 10

union

select 2, 20

go

--Doing an update with inner join, so I'll be able

--to reference the column from the lookup table.

--Since Xquery doesn't work with column's name I have

--to use the function sql:column. Without it the query will

--not work

update XMLtbl

set XMLCol.modify('replace value of (/root/demo/@contract)[1] with sql:column("val")')

from XMLtbl inner join ValTbl on XMLtbl.id = ValTbl.id

go

select * from XMLtbl

go

--Cleanup the mess:-)

drop table XMLtbl

go

drop table ValTbl

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

EA Mr or Mrs. 500 Points: 521 More actions · Answer 2

EA

Mr or Mrs. 500

Points: 521

December 23, 2008 at 12:20 am

#916595

Great, this works fine!

Adi, thank you very much!

Vincy-856784 Ten Centuries Points: 1298 More actions · Answer 3

@adi, you're the man. I was starving to death for this solution and now I am well fed.

Thank you sir!

I needed this query so badly to do the bulk updates from the mobile devices and now I am a happy man....