Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Stairway to XML: Level 8 - Deleting Data from an XML Instance

By Rob Sheldon,

The Series

This article is part of the Stairway Series: Stairway to XML

Although XML is conceptually simple, its use as an equal partner datatype within a relational database, with full searching, validation and manipulation of data, is not intuitive. Now that the industry is more conscious of the use of semi-structured data and data defined by document markup, it is becoming more important than ever for Database Developers and DBAs to become conversant with the technology and appreciative of the cases where XML technologies enhance applications and their development. Robert Sheldon flexes his talent to make the complicated seem simple.

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.

Note

As with previous Levels, we can touch upon only some of the XML DML and XQuery elements in this Level. For a more thorough understanding of XQuery and how it’s implemented in SQL Server, see the MSDN XQuery language reference. For more details about XML DML, see the MSDN article “XML Data Modification Language (XML DML).”

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.

This article is part of the Stairway to XML Stairway

Sign up to our RSS feed and get notified as soon as we publish a new level in the Stairway! Rss

Total article views: 1309 | Views in the last 30 days: 120
 
Related Articles
ARTICLE

Stairway to XML: Level 6 - Inserting Data into an XML Instance

The modify method lets you manipulate XML data using XML DML. It can insert, alter or delete data. ...

ARTICLE

Stairway to XML: Level 7 - Updating Data in an XML Instance

You need to provide the necessary keywords and define the XQuery and value expressions in your XML D...

ARTICLE

Personal Development

Steve Jones thinks that a personal development plan is important and talks today a little about how ...

FORUM

first element in sublist

first element in sublist

FORUM

FOR XML EXPLICIT add attribute to element

How to add an attribute to an element

Tags
stairway series    
xml    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones