How to query data in XML column in SQL Server

Question

I have data like this in an xml column:

<product>
   <productID>1</productID>
   <productname>tea</productname>
</product>
<product>
   <productID>2</productID>
   <productname>coffee</productname>
</product>

I want to change the value of productname to green tea where productID = 2.

I am using:

UPDATE [dbo].ProductDocs
SET ProductDoc.modify('replace value of (/Product/ProductName)[2] with "NewName"')

But here it will always change the value in second product. Please tell me how to query with productID.

Answer 1

Use predicate expression to filter product element by productID value like so :

UPDATE [dbo].ProductDocs
SET ProductDoc.modify('
    replace value of (/product[productID=2]/productname/text())[1] with "NewName"
')

Also notice that, as mentioned in the other answer, XML and XPath/XQuery are case-sensitive.