Update multiple nodes in a xml column by using one update statment

• pkposani

  SSC-Addicted

  Points: 426

  More actions

  April 5, 2011 at 8:47 am

  #97815

  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

• Steve Jones - SSC Editor

  SSC Guru

  Points: 737535

  More actions

  April 5, 2011 at 9:21 am

  #1307527

  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.

• Maksymilian Mulawa

  Ten Centuries

  Points: 1336

  More actions

  April 7, 2011 at 1:13 pm

  #1308788

  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.

  Cheers, Max

  Check-out free open-source utility Sql Server Replication Explorer[/url]