CODEWITHSUNDEEP
c#sqlsql-serverxml
Madhav
Madhav

Reputation: 31

How to query data in XML column in SQL Server

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.

Upvotes: 3

Views: 229

Answers (2)

Hari Prasad
Hari Prasad

Reputation: 16956

First thing first, XML is case-sensitive and so are the XQuery expressions.

Now you could do this.

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

Upvotes: 1

har07
har07

Reputation: 89285

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.

Upvotes: 2

Related Questions