The last Level of the Stairway to XML series introduced you to the modify() method, which is available to the XML data type for manipulating data. The Level showed you how to use the method to insert data into an XML instance. As the examples demonstrated, the method provides several options that let you control how you add the data.

In this Level, you’ll learn how to use the modify() method to update data in an XML instance. As is the case when inserting data, the method takes an XML Data Modification Language (XML DML) expression as an argument when updating the data. XML DML is an extension of the XQuery language that lets you insert, update, and delete data in an XML instance.

To try out the examples in this Level, you’ll first need to run the Transact-SQL code shown in Listing 1. The code creates the ClientDB database, adds the ClientInfoCollection XML schema collection to the database, and then creates and populates the ClientInfo table.

USE master;
GO
IF DB_ID('ClientDB') IS NOT NULL
DROP DATABASE ClientDB;
GO
CREATE DATABASE ClientDB;
GO
USE ClientDB;
GO
IF EXISTS(
  SELECT * FROM sys.xml_schema_collections 
  WHERE name = 'ClientInfoCollection')
DROP XML SCHEMA COLLECTION ClientInfoCollection;
GO
CREATE XML SCHEMA COLLECTION ClientInfoCollection AS 
'<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
xmlns="urn:ClientInfoNamespace" 
targetNamespace="urn:ClientInfoNamespace" 
elementFormDefault="qualified">
  <xsd:element name="People">
    <xsd:complexType>
      <xsd:sequence>
        <xsd:element name="Person" minOccurs="1" maxOccurs="unbounded">
          <xsd:complexType>
            <xsd:sequence>
              <xsd:element name="FirstName" type="xsd:string" minOccurs="1" maxOccurs="1" />
              <xsd:element name="LastName" type="xsd:string" minOccurs="1" maxOccurs="1" />
              <xsd:element name="FavoriteBooks" minOccurs="0" maxOccurs="1">
                <xsd:complexType>
                  <xsd:sequence>
                    <xsd:element name="Book" minOccurs="0" maxOccurs="5">
                      <xsd:complexType>
                        <xsd:simpleContent>
                          <xsd:extension base="xsd:string">
                            <xsd:attribute name="rating" type="xsd:decimal" />
                            <xsd:attribute name="recommend" type="xsd:string" />
                          </xsd:extension>
                        </xsd:simpleContent>
                      </xsd:complexType>
                    </xsd:element>
                  </xsd:sequence>
                </xsd:complexType>
              </xsd:element>
            </xsd:sequence>
            <xsd:attribute name="id" type="xsd:integer" use="required"/>
          </xsd:complexType>
        </xsd:element>
      </xsd:sequence>
    </xsd:complexType>
  </xsd:element>
</xsd:schema>';
GO
IF OBJECT_ID('ClientInfo') IS NOT NULL
DROP TABLE ClientInfo;
GO
CREATE TABLE ClientInfo
(
  ClientID INT PRIMARY KEY IDENTITY,
  Info_untyped XML,
  Info_typed XML(ClientInfoCollection)
);
INSERT INTO ClientInfo (Info_untyped, Info_typed)
VALUES
(
  '<?xml version="1.0" encoding="UTF-8"?>
  <People>
    <Person id="1234">
      <FirstName>John</FirstName>
      <LastName>Doe</LastName>
      <FavoriteBooks>
        <!-- Books rated on scale 1-5 -->
        <Book rating="5">Slaughterhouse-Five</Book>
      </FavoriteBooks>
    </Person>
    <Person id="5678">
      <FirstName>Jane</FirstName>
      <LastName>Doe</LastName>
    </Person>
  </People>',
  '<?xml version="1.0" encoding="UTF-8"?>
  <People xmlns="urn:ClientInfoNamespace">
    <Person id="1234">
      <FirstName>John</FirstName>
      <LastName>Doe</LastName>
      <FavoriteBooks>
        <!-- Books rated on scale 1-5 -->
        <Book rating="5">Slaughterhouse-Five</Book>
      </FavoriteBooks>
    </Person>
    <Person id="5678">
      <FirstName>Jane</FirstName>
      <LastName>Doe</LastName>
    </Person>
  </People>'
);

Listing 1: Setting up the test environment for the examples in this Level

The code in Listing 1 includes an INSERT statement that adds a row of data to the ClientInfo table. The row contains two XML instances, one that targets the untyped column and one that targets the typed column. Otherwise, the elements, attributes, and values that make up each instance are the same.

I created the code in Listing 1 on a local instance of SQL Server 2012, and then created the following examples in the same environment. Once you’ve set up this environment on your system, you’ll be ready to try out these examples.

Updating Data in an XML Instance

You can use the modify() method to update specific element and attribute values in an XML instance. When the method is used in this way, the XML DML expression must include the replace value of keywords and the with keyword, along with two expressions, as shown in the following syntax:

db_object.modify(

'replace value of xquery_exp

with value_exp')

The first expression, xquery_exp, is an XQuery expression that defines the target element or attribute whose value will be modified. The second expression is a literal value or expression that defines the new value to be inserted into the target element or attribute. Together the keywords and expressions must be enclosed in single quotes and parentheses.

Let’s look at an example that demonstrates how the method works to modify data. In Listing 2, the UPDATE statement uses the method to change the name of the book listed in the Info_untyped column.

UPDATE ClientInfo
SET Info_untyped.modify(
  'replace value of
   (/People/Person[@id=1234]/FavoriteBooks/Book/text())[1]
   with "The Catcher in the Rye" ')
WHERE ClientID = 1;
SELECT Info_untyped.query(
  '/People/Person[@id=1234]')
FROM ClientInfo
WHERE ClientID = 1;

Listing 2: Updating an element value in an untyped XML instance

The first thing to note is that the XML DML expressions begins with the replace value of keywords, followed by an XQuery expression that specifies the first <Book> child element for the person with an id attribute value of 1234. As you saw in other examples of the modify() method, the XQuery expression in this case must return a scalar value. For this example, [1] is used to indicate that the first instance of the <Book> element be returned. Even if there is only one instance of an element, as in this situation, the numerical qualifier must still be specified.

Notice also the text() function appended to the end of the path expression. The function returns only the element value, as oppose to the metadata that defines it. For an element in an untyped column, you must specify this function (or some comparable expression) so that your path specifically targets that value. If the function is not specified, SQL Server returns an error.

The next component of the XML DML expression is the with keyword, followed by the value expression, which in this case is the literal value The Catcher in the Rye. Notice that you must enclose literal values in double quotes. When you run the UPDATE statement, this value replaces the existing value (Slaughterhouse-Five). The SELECT statement appended onto Listing 2 confirms that this is the case, as shown in the results in Listing 3. As you can see, the <Book> element now includes the new title.

<Person id="1234">
  <FirstName>John</FirstName>
  <LastName>Doe</LastName>
  <FavoriteBooks>
    <!-- Books rated on scale 1-5 -->
    <Book rating="5">The Catcher in the Rye</Book>
  </FavoriteBooks>
</Person>

Listing 3: The updated element value in the untyped XML instance

The process for updating an element value in a typed column is similar to that of an untyped column. As to be expected, you must specify the namespace information, as shown in Listing 4. As you’ve seen with other XQuery and XML DML expressions, the expression is divided into two parts, separated by a semi-colon. The first part declares the namespace and assigns an alias to that namespace. The second part is similar to what you specify for an untyped column, except that you include the namespace alias in your element references and you do not use the text() function in your XQuery expression.

UPDATE ClientInfo
SET Info_typed.modify(
  'declare namespace ns="urn:ClientInfoNamespace";
   replace value of
   (/ns:People/ns:Person[@id=1234]/ns:FavoriteBooks/ns:Book)[1]
   with "The Catcher in the Rye" ')
WHERE ClientID = 1;
SELECT Info_typed.query(
  'declare namespace ns="urn:ClientInfoNamespace";
  /ns:People/ns:Person[@id=1234]')
FROM ClientInfo
WHERE ClientID = 1;

Listing 4: Updating an element value in a typed XML instance

SQL Server does not support the use of the text() function for typed columns. If you use it, SQL Server will return an error. This, of course, is opposite from what happens with untyped columns, so when you use the modify() method to update element values, you need to be aware of this difference.

Otherwise, there are no other surprises when working with typed columns. As long as you declare your namespace correctly and specify the alias reference (in this case, ns:), you should have no problem, and your SELECT statement should return results similar to those shown in Listing 5. As you can see, the book title has been updated to the new value.

<ns:Person xmlns:ns="urn:ClientInfoNamespace" id="1234">
  <ns:FirstName>John</ns:FirstName>
  <ns:LastName>Doe</ns:LastName>
  <ns:FavoriteBooks>
    <!-- Books rated on scale 1-5 -->
    <ns:Book rating="5">The Catcher in the Rye</ns:Book>
  </ns:FavoriteBooks>
</ns:Person>

Listing 5: The updated element value in the typed XML instance

You can just as easily update an attribute value as you can an element value. In your XQuery expression, specify a path that targets the specific attribute. For example, the XQuery expression in Listing 6 targets the <Book> element’s rating attribute. Notice that you simply append the name of attribute—along with the at (@) symbol—onto the path expression.

UPDATE ClientInfo
SET Info_untyped.modify(
  'replace value of
   (/People/Person[@id=1234]/FavoriteBooks/Book/@rating)[1]
   with "4.5" ')
WHERE ClientID = 1;
SELECT Info_untyped.query(
  '/People/Person[@id=1234]')
FROM ClientInfo
WHERE ClientID = 1;

Listing 6: Updating an attribute value in an untyped XML instance

After you’ve identified the target attribute, you can then specify a value expression that provides a new value for that attribute. In this case, the new value is 4.5, which is confirmed in the results returned by the SELECT statement (shown in Listing 7). As you can see, the new value has been assigned to the attribute.

<Person id="1234">
  <FirstName>John</FirstName>
  <LastName>Doe</LastName>
  <FavoriteBooks>
    <!-- Books rated on scale 1-5 -->
    <Book rating="4.5">The Catcher in the Rye</Book>
  </FavoriteBooks>
</Person>

Listing 7: The updated attribute value in the untyped XML instance

As to be expected, the process of updating an attribute column in a typed column is similar to an untyped column, except for having to provide the namespace information. Listing 8 shows the UPDATE statement needed to update the rating attribute in the Info_typed column. Notice that the XML DML expression includes the namespace declaration and uses the namespace alias in all the element references. Otherwise, the basic components are the same.

UPDATE ClientInfo
SET Info_typed.modify(
  'declare namespace ns="urn:ClientInfoNamespace";
   replace value of
   (/ns:People/ns:Person[@id=1234]/ns:FavoriteBooks/ns:Book/@rating)[1]
   with 4.5 ')
WHERE ClientID = 1;
SELECT Info_typed.query(
  'declare namespace ns="urn:ClientInfoNamespace";
   /ns:People/ns:Person[@id=1234]')
FROM ClientInfo
WHERE ClientID = 1;

Listing 8: Updating an attribute value in a typed XML instance

Once again, if we run the SELECT statement appended to the listing, we’ll find that the attribute value has been updated to 4.5, as shown in Listing 9.

<ns:Person xmlns:ns="urn:ClientInfoNamespace" id="1234">
  <ns:FirstName>John</ns:FirstName>
  <ns:LastName>Doe</ns:LastName>
  <ns:FavoriteBooks>
    <!-- Books rated on scale 1-5 -->
    <ns:Book rating="4.5">The Catcher in the Rye</ns:Book>
  </ns:FavoriteBooks>
</ns:Person>

Listing 9: The updated attribute value in the typed XML instance

In the examples we’ve looked at so far, our value expression has been a literal value enclosed in double quotes. However, that expression can be far more complex. In the example shown in Listing 10, the value expression in the second UPDATE statement is an if…then…else expression that sets the value of the recommend attribute based on the value of the rating attribute.

UPDATE ClientInfo
SET Info_untyped.modify(
  'insert attribute recommend {"true/false"}
   into
   (/People/Person[@id=1234]/FavoriteBooks/Book)[1] ')
WHERE ClientID = 1;
UPDATE ClientInfo
SET Info_untyped.modify(
  'replace value of
   (/People/Person[@id=1234]/FavoriteBooks/Book/@recommend)[1]
   with (
     if (/People/Person[@id=1234]/FavoriteBooks/Book[1]/@rating > 4)
     then "true"
     else "false") ')
WHERE ClientID = 1;
SELECT Info_untyped.query(
  '/People/Person[@id=1234]')
FROM ClientInfo
WHERE ClientID = 1;

Listing 10: Using conditional logic to update an attribute value in an untyped XML instance

The first UPDATE statement adds the recommend attribute to the <Book> element and sets its initial value to true/false. The second UPDATE statement then modifies the attribute’s value. The beginning of the XML DML expression in that statement is similar to what you’ve seen in previous examples. After the replace value of keywords, an XQuery expression identifies the target attribute, recommend. This expression is then followed by the with keyword. Everything after that keyword, enclosed in parentheses, is the value expression.

The value expression begins with the if clause, which specifies that the rating attribute must have a value greater than 4 in order for the clause’s condition to evaluate to true. If the condition does evaluate to true, the value of the recommend attribute is set to true, as specified in the then clause. Otherwise, the recommend value is set to false, as specified in the else clause. In other words, the rating attribute must have a value greater than 4 in order for the recommend attribute is set to true, otherwise the attribute is set to false.

Because the rating attribute currently has a value of 4.5, the recommend attribute will be set to true when you run the UPDATE statement. You can verify these changes by viewing the results of the SELECT statement, which are shown in Listing 11. As you can see, the recommend attribute has been added to the <Book> element and the attribute’s value has been set to true.

<Person id="1234">
  <FirstName>John</FirstName>
  <LastName>Doe</LastName>
  <FavoriteBooks>
    <!-- Books rated on scale 1-5 -->
    <Book rating="4.5" recommend="true">The Catcher in the Rye</Book>
  </FavoriteBooks>
</Person>

Listing 11: The updated attribute value in the untyped XML instance

You can achieve the same results for the typed column by providing the expected namespace information, as shown in Listing 12. Notice that the namespace is declared and referenced throughout. That includes the if clause in the value expression of the second UPDATE statement.

UPDATE ClientInfo
SET Info_typed.modify(
  'declare namespace ns="urn:ClientInfoNamespace";
   insert attribute recommend {"true/false"}
   into
   (/ns:People/ns:Person[@id=1234]/ns:FavoriteBooks/ns:Book)[1] ')
WHERE ClientID = 1;
UPDATE ClientInfo
SET Info_typed.modify(
  'declare namespace ns="urn:ClientInfoNamespace";
   replace value of
   (/ns:People/ns:Person[@id=1234]/ns:FavoriteBooks/ns:Book/@recommend)[1]
   with (
     if (/ns:People/ns:Person[@id=1234]/ns:FavoriteBooks/ns:Book[1]/@rating > 4)
 then "true"
 else "false") ')
WHERE ClientID = 1;
SELECT Info_typed.query(
  'declare namespace ns="urn:ClientInfoNamespace";
   /ns:People/ns:Person[@id=1234]')
FROM ClientInfo
WHERE ClientID = 1;

Listing 12: Using conditional logic to update an attribute value in a typed XML instance

Once again, if you run the SELECT statement appended to the example, your results will reflect the new attribute and its updated value, as shown in Listing 13.

<ns:Person xmlns:ns="urn:ClientInfoNamespace" id="1234">
  <ns:FirstName>John</ns:FirstName>
  <ns:LastName>Doe</ns:LastName>
  <ns:FavoriteBooks>
    <!-- Books rated on scale 1-5 -->
    <ns:Book rating="4.5" recommend="true">The Catcher in the Rye</ns:Book>
  </ns:FavoriteBooks>
</ns:Person>

Listing 13: The updated attribute value in the typed XML instance

As before, you can see that the recommend attribute has been added to the <Book> element and the attribute’s value has been set to true.

Conclusion

Using the modify() method to update data in an XML column requires that you provide the necessary keywords and define the XQuery and value expressions in your XML DML expression. You can use this approach to update both element and attribute values in either typed or untyped XML instances. When updating data in a typed column, you must provide the necessary namespace information, just like you saw it done in previous Levels. In the next Level, you’ll learn how to use the modify() method to delete elements and attributes from an XML instance.