In the last two Levels of the Stairway to XML series, you learned how to use the modify() method (available to the XML data type) to insert and update data in an XML instance. As you saw, the method provides several options that let you control how you manipulate the data.

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

This Level includes several examples that demonstrate how to delete data from both typed and untyped XML instances. If you want to try these examples, 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: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 is similar to what you saw in the last Level. In addition to creating the schema and table, it also 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. I then created the examples in the following section in that same environment. Once you’ve set up this environment on your system, you’ll be ready to try out these examples.

Deleting Data from an XML Instance

You can use the modify() method to delete specific components from an XML instance. Using the method to delete data is for the most part easier than using it to insert or update data. You simply specify the delete keyword, followed by an XQuery expression that identifies the XML component to be deleted. The following syntax shows how to use the modify() method to delete XML data:

db_object.modify('delete xquery_exp')

Notice that, as you saw when inserting and deleting data, the XML DML expression is enclosed in single quotes and parentheses. The XML DML expression itself is very straightforward.

To demonstrate how easy it is to delete data, let’s start by removing an attribute from the untyped XML instance in our ClientInfo table. In Listing 2, the UPDATE statement uses the modify() method to delete data from the Info_untyped column.

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

Listing 2: Deleting an attribute from an untyped XML instance

We begin our XML DML expression by specifying the delete keyword. This is followed by an XQuery path expression that specifies the attribute to be deleted. Notice that the path specifies the first instance of the <Book> element and the rating attribute within that element. The attribute name, which is preceded with the at (@) symbol, follows the element name within the path.

Because the rating attribute within the <Book> element is being specified, the attribute will be removed from the XML instance when you run the UPDATE statement. Listing 3 shows the results returned by the SELECT statement tagged onto the example in Listing 2.

<Person id="1234">
  <FirstName>John</FirstName>
  <LastName>Doe</LastName>
  <FavoriteBooks>
    <!-- Books rated on scale 1-5 -->
    <Book>Slaughterhouse-Five</Book>
  </FavoriteBooks>
</Person>

Listing 3: The <Book> element without the rating attribute in the untyped XML

As the listing shows, the rating attribute is no longer included in the <Book> element. You can see that deleting an attribute from an untyped XML instance is pretty painless. And it’s almost just as easy to delete an attribute from a typed instance. The main difference, of course, is that you must specify the necessary namespace declaration and references. Listing 4 demonstrates how this is done.

UPDATE ClientInfo
SET Info_typed.modify(
  'declare namespace ns="urn:ClientInfoNamespace";
   delete
   /ns:People/ns:Person[@id=1234]/ns:FavoriteBooks/ns:Book[1]/@rating ')
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: Deleting an attribute from a typed XML instance

If you tried examples in previous Levels that access typed XML instances, there should be no surprises here. You divide your XML DML expression into two parts, separated by a semi-colon. In the first part, you declare you namespace and assign an alias to that namespace. In this case, the alias is ns. You then use that alias, along with a colon, in the element references in the second part of your XML DML expression. However, as you can see, you don’t have to include the namespace reference for your attribute. Listing 5 shows the results now returned by the SELECT statement.

<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>Slaughterhouse-Five</ns:Book>
  </ns:FavoriteBooks>
</ns:Person>

Listing 5: The <Book> element without the rating attribute in the typed XML

Notice that the rating attribute has been removed from the <Book> element. Also notice that, whether working with typed or untyped columns, when you remove a component such as an attribute, you’re also removing any data values associated with that component.

In addition to removing attributes, you can remove components such as comments from an XML instance. To do so, you tag the comment() function onto the XQuery expression that identifies the element containing the comment. For example, in Listing 6, I use the comment() function to remove the comment from the <FavoriteBooks> element.

UPDATE ClientInfo
SET Info_untyped.modify(
  'delete
   /People/Person[@id=1234]/FavoriteBooks/comment()[1] ')
WHERE ClientID = 1;
SELECT Info_untyped.query(
  '/People/Person[@id=1234]')
FROM ClientInfo
WHERE ClientID = 1;

Listing 6: Removing a comment from an untyped column

Because an element can contain multiple comments, you should add to the end of your XQuery expression a numerical reference that points to the comment that should be deleted. In this case, I use [1] to designate that the first comment should be deleted. (There is only one comment in the XML instance.) The SELECT statement now returns the results shown in Listing 7.

<Person id="1234">
  <FirstName>John</FirstName>
  <LastName>Doe</LastName>
  <FavoriteBooks>
    <Book>Slaughterhouse-Five</Book>
  </FavoriteBooks>
</Person>

Listing 7: The <FavoriteBooks> element without the comment

You can, of course, just as easily delete a comment from a typed XML instance, as long as you include the proper namespace declaration and references, as shown in Listing 8.

UPDATE ClientInfo
SET Info_typed.modify(
  'declare namespace ns="urn:ClientInfoNamespace";
   delete
   /ns:People/ns:Person[@id=1234]/ns:FavoriteBooks/comment()[1] ')
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: Removing a comment from a typed column

Not surprisingly, the XML DML expression is divided into two parts. The first part is the declaration, and the second part contains the delete keyword and XQuery expression, with the proper namespace references included. Listing 9 shows the results the SELECT statement now returns.

<ns:Person xmlns:ns="urn:ClientInfoNamespace" id="1234">
  <ns:FirstName>John</ns:FirstName>
  <ns:LastName>Doe</ns:LastName>
  <ns:FavoriteBooks>
    <ns:Book>Slaughterhouse-Five</ns:Book>
  </ns:FavoriteBooks>
</ns:Person>

Listing 9: The <FavoriteBooks> element without the comment

Now let’s look at how to remove an element from an XML instance. To do so, you’re XQuery expression must identify the element that should be deleted, as shown in Listing 9.

UPDATE ClientInfo
SET Info_untyped.modify(
  'delete
   /People/Person[@id=1234]/FavoriteBooks/Book[1] ')
WHERE ClientID = 1;
SELECT Info_untyped.query(
  '/People/Person[@id=1234]')
FROM ClientInfo
WHERE ClientID = 1;

Listing 10: Removing an element from an untyped column

Notice that, as with the previous examples, the XML DML expression starts with the delete keyword, following by the XQuery expression. As that expression shows, we’re removing the first instance of the <Book> child element within the <FavoriteBooks> element. Listing 11 shows the results returned by the SELECT statement.

<Person id="1234">
  <FirstName>John</FirstName>
  <LastName>Doe</LastName>
  <FavoriteBooks />
</Person>

Listing 11: The <FavoriteBooks> element without the <Book> child element

As you would expect, the <Book> element has been removed, and the <FavoriteBooks> element no longer contains child elements. If you want to remove the same element from the typed column, you simply include the necessary namespace declaration and references, as shown in Listing 12.

UPDATE ClientInfo
SET Info_typed.modify(
  'declare namespace ns="urn:ClientInfoNamespace";
   delete
   /ns:People/ns:Person[@id=1234]/ns:FavoriteBooks/ns:Book[1] ')
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: Removing an element from a typed column

Again, be sure to include the namespace alias and colon when referencing the elements within your XQuery expression. The SELECT statement now returns the results shown in Listing 13.

<ns:Person xmlns:ns="urn:ClientInfoNamespace" id="1234">
  <ns:FirstName>John</ns:FirstName>
  <ns:LastName>Doe</ns:LastName>
  <ns:FavoriteBooks />
</ns:Person>

Listing 13: The <FavoriteBooks> element without the <Book> child element

As you saw with the untyped column, the <Book> element has been removed and the <FavoriteBooks> element no longer contains any child elements.

Conclusion

This Level explained how to use the modify() method to delete data from typed and untyped XML instances. As the Level demonstrated, you must pass an XML DML expression as an argument to the method. That expression must include the delete keyword, along with an XQuery expression that defines the XML component to be deleted.

Up to this point in the Stairway to XML series, our discussions about XML have generally centered around the standard ways XML is implemented in SQL Server, primarily as columns or variables configured with the XML data type. However, XML can also play a role when working with such objects as views, functions, defaults, computed columns, and check constraints. As we progress through this series, you’ll learn how to take what we’ve covered up till now and apply that information to other objects in a SQL Server database.