Modifying XML values

Determining the property syntax when modifying XML values in SQL Server can be time consuming if you don’t work with XML regularly. SQL Server includes a very flexible XML subsystem, called XML_DML, or XML Data Manipulation Language. XML_DML can be used to easily and effectively update XML values in an xml-typed column or variable. This question on dba.stackexchange.com asked about using the .modify function to change the value of an element, which in turn prompted this post.

Monkeys in a Tavern, probably not modifying XML

Monkeys in a Tavern, probably not modifying XML. By David Teniers (1610-1690)

From the Microsoft Documentation, .modify:

Modifies the contents of an XML document. Use this method to modify the content of an xml type variable or column. This method takes an XML DML statement to insert, update, or delete nodes from XML data. The modify() method of the xml data type can only be used in the SET clause of an UPDATE statement.

Syntax

The general syntax for the .modify function is:

Example code

Let’s explore the use of .modify with a couple of simple examples.

First, we’ll create a table with an xml column:

Simple, single-row example

Now we’ll populate a single row, then call .modify on that row to change “some value” to “some other value”.

The output looks like:

╔════════════╦═════════════════════════════════════════════════════════════╗
║ xmldata_id ║                              x                              ║
╠════════════╬═════════════════════════════════════════════════════════════╣
║          1 ║ <xml><element><item>some other value</item></element></xml> ║
╚════════════╩═════════════════════════════════════════════════════════════╝

Modify a single value amongst multiple values

What if we want to modify one value in an array of multiple values nested inside a single element? In the following data, we have two <item>’s inside a single <element>, but we only want to modify the 2nd <item>:

some value 2 has been changed to some value 3:

╔════════════╦══════════════════════════════════════════════════════════════════════════════════════╗
║ xmldata_id ║                                          x                                           ║
╠════════════╬══════════════════════════════════════════════════════════════════════════════════════╣
║          1 ║ <xml><element><item>some other value</item></element></xml>                          ║
║          2 ║ <xml><element><item>some value 1</item><item>some value 3</item></element></xml>     ║
╚════════════╩══════════════════════════════════════════════════════════════════════════════════════╝

In the XML_DML code, you can see I’ve added [2] into this code:

The index into the 2nd <item> value is [2] – XML ordinals are 1’s-based. i.e.: the first value is [1], not [0].

Namespaces!

If the xml includes a namespace, the update doesn’t seem to work:

╔════════════╦══════════════════════════════════════════════════════════════════════════════════════╗
║ xmldata_id ║                                          x                                           ║
╠════════════╬══════════════════════════════════════════════════════════════════════════════════════╣
║          1 ║ <xml><element><item>some other value</item></element></xml>                          ║
║          2 ║ <xml><element><item>some value 1</item><item>some value 3</item></element></xml>     ║
║          3 ║ <ns1:xml xmlns:ns1="MyNameSpace1"><ns1:element><ns1:item>some value 4</ns1:item>     ║
║            ║   <ns1:item>some value 5</ns1:item></ns1:element></ns1:xml>                          ║
╚════════════╩══════════════════════════════════════════════════════════════════════════════════════╝

Notice in the above output some value 5 has not been changed to some value 6, as requested by the .modify function. The problem is, we didn’t define the correct namespace in the .modify function definition. No error is generated; it looks like the update succeeded, but nothing has been modified.

This shows one way of correctly denoting which namespace we are updating:

some value 5 has been changed to some value 6:

╔════════════╦══════════════════════════════════════════════════════════════════════════════════════╗
║ xmldata_id ║                                          x                                           ║
╠════════════╬══════════════════════════════════════════════════════════════════════════════════════╣
║          1 ║ <xml><element><item>some other value</item></element></xml>                          ║
║          2 ║ <xml><element><item>some value 1</item><item>some value 3</item></element></xml>     ║
║          3 ║ <ns1:xml xmlns:ns1="MyNameSpace1"><ns1:element><ns1:item>some value 4</ns1:item>     ║
║            ║   <ns1:item>some value 6</ns1:item></ns1:element></ns1:xml>                          ║
╚════════════╩══════════════════════════════════════════════════════════════════════════════════════╝

Alternately, you can use with WITH XMLNAMESPACES clause in the query:

some value 6 has now been changed to xyz:

╔════════════╦══════════════════════════════════════════════════════════════════════════════════════╗
║ xmldata_id ║                                          x                                           ║
╠════════════╬══════════════════════════════════════════════════════════════════════════════════════╣
║          1 ║ <xml><element><item>some other value</item></element></xml>                          ║
║          2 ║ <xml><element><item>some value 1</item><item>some value 3</item></element></xml>     ║
║          3 ║ <ns1:xml xmlns:ns1="MyNameSpace1"><ns1:element><ns1:item>some value 4</ns1:item>     ║
║            ║   <ns1:item>xyz</ns1:item></ns1:element></ns1:xml>                                   ║
╚════════════╩══════════════════════════════════════════════════════════════════════════════════════╝

Both methods work equally well for updating values in specific namespaces.

Multiple namespaces

some value 10 has been replaced with 123:

╔════════════╦══════════════════════════════════════════════════════════════════════════════════╗
║ xmldata_id ║                                        x                                         ║
╠════════════╬══════════════════════════════════════════════════════════════════════════════════╣
║          1 ║ <xml><element><item>some other value</item></element></xml>                      ║
║          2 ║ <xml><element><item>some value 1</item><item>some value 3</item></element></xml> ║
║          3 ║ <ns1:xml xmlns:ns1="MyNameSpace1"><ns1:element>                                  ║
║            ║   <ns1:item>some value 4</ns1:item>                                              ║
║            ║   <ns1:item>xyz</ns1:item></ns1:element></ns1:xml>                               ║
║          4 ║ <ns1:xml xmlns:ns1="MyNameSpace1">                                               ║
║            ║   <ns1:element><ns1:item>some value 7</ns1:item>                                 ║
║            ║   <ns1:item>some value 8</ns1:item>                                              ║
║            ║ </ns1:element></ns1:xml>                                                         ║
║            ║ <ns2:xml xmlns:ns2="MyNameSpace2">                                               ║
║            ║   <ns2:element><ns2:item>some value 9</ns2:item>                                 ║
║            ║   <ns2:item>123</ns2:item></ns2:element>                                         ║
║            ║ </ns2:xml>                                                                       ║
╚════════════╩══════════════════════════════════════════════════════════════════════════════════╝

If you have any questions about this post, please leave a comment below.

Check out the rest of our posts on T-SQL!

Ads by Google, Paying the Rent: