Updating xml in sql server

Rated 3.81/5 based on 658 customer reviews

The modify() method of the xml data type can only be used in the SET clause of an UPDATE statement.

Because the modify() method requires a string in the XML Data Manipulation Language (DML), the samples for modify() are contained in the topics that describe the XML DML statements.

element SELECT x.query(' /Root/Product Description') FROM T -- update the Product Name attribute value UPDATE T SET x.modify(' replace value of (/Root/Product Description/@Product Name)[1] with "New Road Bike" ') -- verify the update SELECT x.query(' /Root/Product Description') FROM T This example replaces values in a manufacturing instructions document stored in a typed XML column.

In the example, you first create a table (T) with a typed XML column in the Adventure Works database.

My end goal is to modify the XML in the Order XML column IF the Item_Number of the node contains COTRGUID (like '%COTRGUID') AND the Price Level=NONFREE.

If that condition is met I want to change the Price Level column to equal FREE.

What is the easiest option to replace a single entry in an XML column?

Expression1 identifies that the Labor Hours attribute from the first work center is to be updated.

Expression2 uses an if expression to determine the new value of the Labor Hours attribute.

') go select Instructions from T Note the use of cast when replacing Lot Size value.

This is required when the value must be of a specific type.

Leave a Reply