Modifying XML values

2019-05-15 · by Hannah Vernon · in xml

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. 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:

xml_value.modify ('
    replace value of (/xml/element/item/text())[1]
    with "some other value"
    ')

xml_value.modify ('

replace value of (/xml/element/item/text())[1]

with "some other value"

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:

IF OBJECT_ID(N'tempdb..#xmldata', N'U') IS NOT NULL
DROP TABLE #xmldata;
CREATE TABLE #xmldata
(
    xmldata_id int NOT NULL
        PRIMARY KEY CLUSTERED
    , x xml NOT NULL
) ON [PRIMARY];

IF OBJECT_ID(N'tempdb..#xmldata', N'U') IS NOT NULL

DROP TABLE #xmldata;

CREATE TABLE #xmldata

(

xmldata_id int NOT NULL

PRIMARY KEY CLUSTERED

, x xml NOT NULL

) ON [PRIMARY];

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”.

INSERT INTO #xmldata (xmldata_id, x)
VALUES (1, CONVERT(xml, N'
<xml>
    <element>
        <item>some value</item>
    </element>
</xml>
'))

UPDATE #xmldata 
SET x.modify ('
    replace value of (/xml/element/item/text())[1]
    with "some other value"
    ')
WHERE xmldata_id = 1;

SELECT *
FROM #xmldata;

INSERT INTO #xmldata (xmldata_id, x)

VALUES (1, CONVERT(xml, N'

<xml>

<item>some value</item>

</element>

</xml>

'))

UPDATE #xmldata

SET x.modify ('

replace value of (/xml/element/item/text())[1]

with "some other value"

WHERE xmldata_id = 1;

SELECT *

FROM #xmldata;

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>:

INSERT INTO #xmldata (xmldata_id, x)
VALUES (2, CONVERT(xml, N'
<xml>
    <element>
        <item>some value 1</item>
        <item>some value 2</item>
    </element>
</xml>
'))

UPDATE #xmldata 
SET x.modify ('
    replace value of (/xml/element/item[2]/text())[1]
    with "some value 3"
    ')
WHERE xmldata_id = 2;

SELECT *
FROM #xmldata;

INSERT INTO #xmldata (xmldata_id, x)

VALUES (2, CONVERT(xml, N'

<xml>

<item>some value 1</item>

<item>some value 2</item>

</element>

</xml>

'))

UPDATE #xmldata

SET x.modify ('

replace value of (/xml/element/item[2]/text())[1]

with "some value 3"

WHERE xmldata_id = 2;

SELECT *

FROM #xmldata;

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:

replace value of (/xml/element/item[2]/text())[1]

1	replace value of (/xml/element/item[2]/text())[1]

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:

INSERT INTO #xmldata (xmldata_id, x)
VALUES (3, CONVERT(xml, N'
<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>
'))

UPDATE #xmldata 
SET x.modify ('
    replace value of (/xml/element/item[2]/text())[1]
    with "some value 6"
    ')
WHERE xmldata_id = 3;

SELECT *
FROM #xmldata;

INSERT INTO #xmldata (xmldata_id, x)

VALUES (3, CONVERT(xml, N'

<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>

'))

UPDATE #xmldata

SET x.modify ('

replace value of (/xml/element/item[2]/text())[1]

with "some value 6"

WHERE xmldata_id = 3;

SELECT *

FROM #xmldata;

╔════════════╦══════════════════════════════════════════════════════════════════════════════════════╗
║ 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:

UPDATE #xmldata 
SET x.modify ('
    declare namespace ns1="MyNameSpace1";
    replace value of (/ns1:xml/ns1:element/ns1:item[2]/text())[1]
    with "some value 6"
    ')
WHERE xmldata_id = 3;

SELECT *
FROM #xmldata;

UPDATE #xmldata

SET x.modify ('

declare namespace ns1="MyNameSpace1";

replace value of (/ns1:xml/ns1:element/ns1:item[2]/text())[1]

with "some value 6"

WHERE xmldata_id = 3;

SELECT *

FROM #xmldata;

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:

;WITH XMLNAMESPACES ('MyNameSpace1' AS ns1)
UPDATE #xmldata 
SET x.modify ('
    replace value of (/ns1:xml/ns1:element/ns1:item[2]/text())[1]
    with "xyz"
    ')
WHERE xmldata_id = 3;

SELECT *
FROM #xmldata;

;WITH XMLNAMESPACES ('MyNameSpace1' AS ns1)

UPDATE #xmldata

SET x.modify ('

replace value of (/ns1:xml/ns1:element/ns1:item[2]/text())[1]

with "xyz"

WHERE xmldata_id = 3;

SELECT *

FROM #xmldata;

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

INSERT INTO #xmldata (xmldata_id, x)
VALUES (4, CONVERT(xml, N'
<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>some value 10</ns2:item>
    </ns2:element>
</ns2:xml>
'))

;WITH XMLNAMESPACES ('MyNameSpace2' AS ns2)
UPDATE #xmldata 
SET x.modify ('
    replace value of (ns2:xml/ns2:element/ns2:item[2]/text())[1]
    with "123"
    ')
WHERE xmldata_id = 4;

SELECT *
FROM #xmldata;

INSERT INTO #xmldata (xmldata_id, x)

VALUES (4, CONVERT(xml, N'

<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>some value 10</ns2:item>

</ns2:element>

</ns2:xml>

'))

;WITH XMLNAMESPACES ('MyNameSpace2' AS ns2)

UPDATE #xmldata

SET x.modify ('

replace value of (ns2:xml/ns2:element/ns2:item[2]/text())[1]

with "123"

WHERE xmldata_id = 4;

SELECT *

FROM #xmldata;

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!

Modifying XML values

Syntax

Example code

Simple, single-row example

Modify a single value amongst multiple values

Namespaces!

Multiple namespaces

Related

One Response

Leave a ReplyCancel reply

Search

Categories

Pages

Meta