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.

har07 · Accepted Answer · 2016-03-29 04:26:58Z

2

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.

answered Mar 29, 2016 at 4:26

har07

89.5k12 gold badges87 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Hari Prasad · Accepted Answer · 2016-03-29 06:00:01Z

1

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"')

edited Mar 29, 2016 at 6:00

answered Mar 29, 2016 at 4:10

Hari Prasad

17k4 gold badges23 silver badges35 bronze badges

2 Comments

har07 Over a year ago

Agreed about case-sensitivity. But the XQuery will trigger 'syntax-error' because /[some_expression] is not valid expression. Furthermore, productID/@Value references value attribute of productID element, there is no such attribute in the OP's XML.

Hari Prasad Over a year ago

@har07 yeah.. I thought it was a value, fixed it now.

Collectives™ on Stack Overflow

How to query data in XML column in SQL Server

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related