Wednesday, November 11, 2009

SQL Server XML - Replacing the value of an attribute with a column from the same row

This will hopefully save someone the 45 minutes that it took me to figure it out. I wanted to replace the value of an attribute within an XML column with the value of a column on the same row.

UPDATE myTable
SET xmlColumn.modify('replace value of (/x/y/@a)[1] with sql:column("columnName")')

When I tried wrapping the call to sql:column with braces (ex. {sql:column("columnName")}, I received error 2224 - An expression was expected.

When I tried enclosing the whole thing in double-quotes (ex. "sql:column("columnName")", I received error 2370 - No more tokens expected at the end of the XQuery expression. Found 'columnName'

No comments:

Post a Comment