SQL Server 2008: Modify xml replacing an attribute value with a value of a column

Question

I have a table:

declare @Table table (XmlPart xml, Cnt int)

the XmlPart is of the following form:

<Group count="0" />

I would like to modify XmlPart by substituting it with value of Cnt column. That's what I try:

update @Table
set XmlPart.modify('replace value of (/Group/@count)[1] with sql:column(Cnt)')

But the parser doesn't understand me..

Is it possible to substitute an attribute (or node value) of an xml with table column?

Answer 1

You almost got it right :-)

update @Table
set XmlPart.modify('replace value of (/Group/@count)[1] with sql:column("Cnt")')

You need to put the column name in sql:column into double quotes...

See the MSDN Docs on sql:column XQuery function.