xmlsql-server-2005t-sqldmlxml-dml

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

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

Solution

  • 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?