TSQL 2005, XML DML - Update Two Values at once?

Question

Is there any way to combine these two replace values with 1 update statement?

UPDATE dbo.MyTable
SET MyXmlColumn.modify('replace value of (/node/@att1)[1] with "1"')
WHERE id = 1

UPDATE dbo.MyTable
SET MyXmlColumn.modify('replace value of (/node/@att2)[1] with "2"')
WHERE id = 1

http://msdn.microsoft.com/en-US/library/ms190675(v=SQL.90).aspx

Answer 1

I dont think you're in luck, Thx.

I tried several syntactical variants with no joy. For example, the obvious:

SET MyXmlColumn.modify('...'), MyXmlColumn.modify('...')

yields:

The column name 'MyXmlColumn' is specified more than once in the SET clause. A column cannot be assigned more than one value in the same SET clause. Modify the SET clause to make sure that a column is updated only once. If the SET clause updates columns of a view, then the column name 'MyXmlColumn' may appear twice in the view definition.

But there's nothing helpful for this error message in the XML DML space.

The bottom line is that Expression1 & Expression2 must be single elements.

Even the most thorough discussions end up looping through it...

http://blogs.msdn.com/b/denisruc/archive/2005/09/19/471562.aspx

Sorry. :(

PS: If you can stand the hackiness, casting to VARCHAR(MAX) then doing a normal REPLACE() should do the trick. See: Can I do a find/replace in t-sql?

Answer 2

No, as stated in the documentation, replace value of can only operate on a single node at one time. As far as I know, there isn't a workaround for this. You'll need to use 2 update statements.

Answer 3

If you just have a couple of values you want to change you could use this approach I found at sqlservercentral. It uses a select statement to get the data manipulates it multiple times and uses a single update statement to change the table data.

Not perfect but it might be good enough in some use cases. Basically it is a simplified version of the looping solution for a fixed number of values to change.

DECLARE @temp XML
SET @temp = (SELECT Column_name FROM 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