CODEWITHSUNDEEP
xmlsql-server-2008
Timofey
Timofey

Reputation: 2518

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

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?

Upvotes: 4

Views: 6825

Answers (1)

marc_s
marc_s

Reputation: 755321

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.

Upvotes: 6

Related Questions