December 22, 2008 at 9:51 am
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!!
December 22, 2008 at 1:56 pm
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/
December 23, 2008 at 12:20 am
Great, this works fine!
Adi, thank you very much!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply