April 5, 2011 at 8:47 am
Hi All,
I'm trying to update mutiple nodes in a xml column by using one update statment..Below is the sample code that I'm using..
UPDATE Table_name
SET Column_name.modify('replace value of (/UmAuthDto/Assignment/QueueId/text())[1] with sql:variable("@NewValue")')
WHERE AuthId = @AuthId
UPDATE Table_name
SET Column_name.modify('replace value of (/UmAuthDto/Assignment/QueueName/text())[1] with sql:variable("@NewValue")')
WHERE AuthId = @AuthId
I'm trying to combine the above two updates in to single update..like this and getting error..
UPDATE Table_name
SET Column_name.modify('replace value of (/UmAuthDto/Assignment/QueueId/text())[1] with sql:variable("@NewValue")') ,
SET Column_name.modify('replace value of (/UmAuthDto/Assignment/QueueName/text())[1] with sql:variable("@NewValue")')
WHERE AuthId = @AuthId
Please help me out in combining the two updates in to one update...also If I write two updates instead of one update will it effect my performance..
Thanks,
Pavan Posani
April 5, 2011 at 9:21 am
I found this, from Bob Beauchamin, who I think is very knowledgeable on stuff like this: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=70053
Basically the XML updates are singletons, so you need two update statements as this isn't really an UPDATE in the sense that T-SQL does set based UPDATES.
Performance is likely worse than if there were one statement, but there doesn't appear to be a way around this.
April 7, 2011 at 1:13 pm
You could approach it from slightly different angle, instead of having two UPDATEs, you could have 1xSELECT and 1xUPDATE
DECLARE @temp XML
SET @temp = (SELECT Column_name FORM Table_name WHERE AuthId = @AuthId)
SET @temp.modify('replace value of (/UmAuthDto/Assignment/QueueId/text())[1] with sql:variable("@NewValue")')
SET @temp.modify('replace value of (/UmAuthDto/Assignment/QueueName/text())[1] with sql:variable("@NewValue")')
UPDATE Table_name
SET Column_name = @temp
WHERE AuthId = @AuthId
Not sure if this helps.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply