Updating XML using attribute to identify value in SQL server

Question

I have a XML structure in a XML column on a SQL Server table as follows:

<Customizations>
 <Customization name="OtherValue">
  <value>Test</value>
 </Customization>
 . . . .
 <Customization name="Year">
  <value>2009</value>
 </Customization>
</Customizations>

and I'm looking to update the value of Customization with the element with the attribute Year. Been looking at this for a while and best attempt is:

UPDATE TestTable
SET XmlColumn.modify(
'
    replace value of (/Customizations/Customization/@name[.="Year"]/value)[1] 
    with ( "2010" )
')

Can someone help point out where I'm going wrong?

Answer 1

You need to use a slightly different XPath expression:

UPDATE TestTable
SET 
   XmlColumn.modify('replace value of (/Customizations/Customization[@name="Year"]/value/text())[1] with "2010"')
WHERE .......

Specifically, you need to specify the /value/text() so that the inner text of the <value> node gets replaced.