Stairway to XML

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

,

In the last two Levels of the Stairway to XML series, we looked at the methods you can use to retrieve element-specific data from an XML column, variable, or parameter. Those methods include query(), value(), exist(), and nodes(), which each provide a different means for accessing data in an XML instance. At a minimum, when you call one of those methods, you pass in an XQuery expression that defines what data to retrieve from that instance.

In this Level, we look at the modify() method, the only method available to the XML data type that lets you manipulate XML data. Unlike the other XML methods, the modify()method takes an XML Data Modification Language (XML DML) expression as an argument, rather than a regular XQuery expression. XML DML is an extension of the XQuery language that lets you insert, update, and delete XML data. In this Level, we’ll be concerned specifically with how to use the method to insert data. In subsequent Levels, we’ll review how to use the method to modify and delete data.

When you call the modify() method, you must pass in an XML DML expression. The expression is the method’s only argument, as shown in the following syntax:

db_object.modify('xml_dml')

As you can see, you simply append the XML object name with a period and method name, followed by the XML DML expression enclosed in parentheses and single quotes. Not surprisingly, it’s the expression itself where things get a bit more complicated. This Level focuses on how to create various XML DML expressions and provides a number of examples that demonstrate how to insert data into an XML instance.

Note

Many of the elements that make up an XML DML expression use basic XQuery syntax, which itself is a complex language. As with previous Levels, we can touch upon only some of the XQuery elements. 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).”

Setting Up Your Test Environment

If you want to try out the exercises 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 OBJECT_ID('ClientInfoCollection') IS NOT NULL
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" type="xsd:string" minOccurs="0" maxOccurs="5" />
                  </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>
    </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>
    </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

Notice that the code includes an INSERT statement that adds a row of data to the ClientInfo table. That data includes two XML instances, one that targets the untyped column and one that targets the typed column.

If you tried out the examples in the previous two Levels, you’ll find the code in Listing 1 to be slightly different from those Levels. The schema defined in this collection contains a few extra elements. Also note, you should run the examples in the order they’re provided because some of them build on previous ones.

Inserting Data into an XML Instance

To insert data into an XML instance, your XML DML expression must include the keywords and XQuery expressions necessary to indicate the type of data modification operation to perform as well as what and where to add the data, as shown in the following syntax:

db_object.modify(

'insert xquery_exp1

[as first | as last] into | after | before

xquery_exp2 ')

As you can see, when we break apart the XML DML expression, our method call becomes much more complex. Notice that the expression includes several individual elements:

  • The insert keyword indicates that this is an insert operation.
  • The xquery_exp1 placeholder is an XQuery expression that defines one or more XML components to be inserted into the XML data.
  • The following directional keywords identify where in the targeted node (as defined by xquery_exp2) to insert the data:
    • [as first | as last] into: The data is inserted as one or more child nodes to the targeted node. If child nodes already exist, you must also specify the as first or as last keywords. If you specify as first, the new data is added before the existing child nodes. If you specify as last, the new data is added after the existing child nodes.
    • after: Data is inserted as siblings to the targeted node, directly after that node.
    • before: Data is inserted as siblings to the targeted node, directly before that node.
  • The xquery_exp2 placeholder is an XQuery expression that defines the XML node that is the target of the data to be inserted.

Once you understand how the pieces fit together, the XML DML expression is fairly straightforward. And the best way to gain that understanding is to see these expressions in action. So let’s get started.

Listing 2 shows an UPDATE statement that includes the modify() method, which I use to insert the <FavoriteBooks> element into the first instance of the <Person> element, as identified by the id attribute value 1234.

UPDATE ClientInfo
SET Info_untyped.modify(
  'insert <FavoriteBooks />
   as last into
   (/People/Person[@id=1234])[1] ')
WHERE ClientID = 1;
SELECT Info_untyped.query(
  '/People/Person[@id=1234]')
FROM ClientInfo
WHERE ClientID = 1;

Listing 2: Adding an element to an XML fragment

The first thing worth pointing out is that I’m using the modify() method as part of the SET clause of an UPDATE statement. When you use the modify() method, you must do so within a data modification structure such as a SET clause.

As for the XML DML expression itself, I start with the insert keyword, followed by the expression that identifies the data to be inserted, in this case, the <FavoriteBooks> element. Notice that I use the shorthand notation (/>) to specify the closing element, rather than specifying <FavoriteBooks></FavoriteBooks>. However, you can take either approach.

Next, I include the as last into keywords to specify that the new element should be added to the end of the child elements of the target node.

The final expression, (/People/Person[@id=1234])[1], is the target node. In this case, that node is the first instance of the <Person> element. Notice that I add [1] to the end of the expression. The modify() method requires that the expression return a single target node. Adding a bracketed value in this way ensures that only one value is returned, in this case, the first one. Even if only one node would be returned (as is the case here), you must still specify the [1].

That’s all there is to my XML DML expression, except that I’ve also enclosed it in parentheses and single quotes. I then tagged a SELECT statement onto the example to verify the operation. Listing 3 shows the XML fragment returned by that statement. As you can see, the <FavoriteBooks> node has been added as a child node to the <Person> element, after the existing child elements.

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

Listing 3: XML fragment with new element

You probably noticed that the example shown in Listing 2 modifies data in the untyped XML column (Info_untyped). However, I can achieve similar results in the typed columns. In Listing 4, I modify the XML DML expression to include the namespace reference. As you saw with the XQuery expressions used for the other XML methods, the XML DML expression is divided into two sections, separated by a semi-colon. The first section is the namespace declaration.

UPDATE ClientInfo
SET Info_typed.modify(
  'declare namespace ns="urn:ClientInfoNamespace";
   insert <ns:FavoriteBooks />
   as last into
   (/ns:People/ns:Person[@id=1234])[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 4: Adding an element to a typed XML column

The second section of the XML DML expression is similar to the previous example, except that I precede each referenced node with the namespace alias and a colon (ns:). Everything else is the same. Listing 5 shows the results returned by the SELECT statement. As you can see, the <FavoriteBooks> element has been added to the typed XML instance.

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

Listing 5: XML fragment with new element

Now let’s look at another example. In Listing 6, I add the <Book> element as a child to the <FavoriteBooks> element. This time, however, I include an element value, Slaughterhouse-Five.

UPDATE ClientInfo
SET Info_untyped.modify(
  'insert <Book>Slaughterhouse-Five</Book>
   into
   (/People/Person[@id=1234]/FavoriteBooks)[1] ')
WHERE ClientID = 1;
SELECT Info_untyped.query(
  '/People/Person[@id=1234]')
FROM ClientInfo
WHERE ClientID = 1;

Listing 6: Adding a book to favorite books

Notice that, after the first expression, I include the into keyword, but not the as first or as last keywords. Because the <FavoriteBooks> node currently contains no child elements, I do not need either of these options. Listing 7 shows the results returned by the SELECT statement in this example. As you can see, the <Book> node has been added as a child element to the <FavoriteBooks> node.

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

Listing 7: XML fragment with a new book listed

Again, we can do the same thing for the typed column. As Listing 8 shows, I need only add the necessary namespace declaration and references. That includes preceding each node with the namespace alias and colon (ns:), even if it is a closing node.

UPDATE ClientInfo
SET Info_typed.modify(
  'declare namespace ns="urn:ClientInfoNamespace";
   insert <ns:Book>Slaughterhouse-Five</ns:Book>
   into
   (/ns:People/ns:Person[@id=1234]/ns:FavoriteBooks)[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: Adding a book to the XML in a typed column

Not surprisingly, the SELECT statement returns an XML instance that includes the <Book> element, 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>
    <ns:Book>Slaughterhouse-Five</ns:Book>
  </ns:FavoriteBooks>
</ns:Person>

Listing 9: XML fragment with new book

Now let’s look at how to add an attribute to an existing element. To do so, you must specify the attribute keyword, attribute name, and attribute value after the insert keyword. For example, the UPDATE statement in Listing 10 creates an attribute named rating and sets its value to 5.

UPDATE ClientInfo
SET Info_untyped.modify(
  'insert attribute rating {"5"}
   into
   (/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: Adding an attribute to an XML element

Notice that I enclose the attribute value in curly brackets and double quotes and that I specify the into keyword without the as first or as last option. Because we’re not concerned with child elements in this case, the optional keywords aren’t necessary.

The final expression in the XML DML expression identifies the target node, which in this case is the <Book> element. This is the element that will receive the new attribute. Listing 11 shows the results returned by the SELECT statement. As you would expect, the <Book> element now includes the rating attribute and its associated value of 5.

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

Listing 11: XML element with the new attribute

Now suppose we want to add another book to our list of books. One way we can do this is to use the into keyword along with one of the optional values to specify where to place the new element. However, another approach is to instead use the after keyword, which inserts the node as a sibling element after the specified node. That means your target node must be specific enough to identify where the new element should be inserted. For example, in Listing 12, I add a second <Book> element after the first one. To do so, my target expression specifically references that first <Book> node.

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

Listing 12: Inserting a second book in the XML fragment

By taking this approach, I do not have to be concerned with the optional keywords as first or as last. The after keyword is enough. The key is to make sure my second expression properly targets the instance of <Book> that I want my new element to follow, which I do by using the [1] to indicate that the first instance should be used. As expected, the SELECT statement returns the results shown in Listing 13. Notice that the second <Book> element has been added in the expected location.

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

Listing 13: XML fragment with the second book

Up to this point, we’ve added only one node to our target element in each of the examples. However, you can specify multiple nodes in a single XML DML expression. For example, in the UPDATE statement shown in Listing 14, I insert two instances of the <Book> element into the target node.

UPDATE ClientInfo
SET Info_untyped.modify(
  'insert (
     <Book>Mrs Dalloway</Book>,
     <Book>One Hundred Years of Solitude</Book>)
   after
   (/People/Person[@id=1234]/FavoriteBooks/Book)[2] ')
WHERE ClientID = 1;
SELECT Info_untyped.query(
  '/People/Person[@id=1234]')
FROM ClientInfo
WHERE ClientID = 1;

Listing 14: Inserting two books at once into the XML element

As you can see, I enclose the two <Book> elements in parentheses and separate them with a comma. The rest of the XML DML expression is just like the preceding example, except for the target element. In this case, I use [2] to specify that the new books should follow the second <Book> instance, rather than the first. Listing 15 shows the results returned by the SELECT statement. As expected, the <FavoriteBooks> element now contains four child elements.

<Person id="1234">
  <FirstName>John</FirstName>
  <LastName>Doe</LastName>
  <FavoriteBooks>
    <Book rating="5">Slaughterhouse-Five</Book>
    <Book>Beloved</Book>
    <Book>Mrs Dalloway</Book>
    <Book>One Hundred Years of Solitude</Book>
  </FavoriteBooks>
</Person>

Listing 15: XML element with the two additional books

Now let’s look at the example shown in Listing 16. This time, I insert a comment as a child to the <FavoriteBooks> element, but before all the <Book> elements. To do so, I specify the insert keyword followed by an XQuery expression, as I do in the other examples. However, the expression in this case is the comment, which is denoted by the opening comment tag (<!--) tag and the closing tag (-->).

UPDATE ClientInfo
SET Info_untyped.modify(
  'insert <!-- Books rated on scale 1-5 -->
   before
   (/People/Person[@id=1234]/FavoriteBooks/Book)[1] ')
WHERE ClientID = 1;
SELECT Info_untyped.query(
  '/People/Person[@id=1234]')
FROM ClientInfo
WHERE ClientID = 1;

Listing 16: Inserting a comment in an XML fragment

After the first expression, I specify the before keyword, followed by the node that I want to precede with the comment, which in this case is the first <Book> element. Listing 17 shows the results now returned by the SELECT statement.

<Person id="1234">
  <FirstName>John</FirstName>
  <LastName>Doe</LastName>
  <FavoriteBooks>
    <!-- Books rated on scale 1-5 -->
    <Book rating="5">Slaughterhouse-Five</Book>
    <Book>Beloved</Book>
    <Book>Mrs Dalloway</Book>
    <Book>One Hundred Years of Solitude</Book>
  </FavoriteBooks>
</Person>

Listing 17: XML fragment with the new comment

As the results indicate, the comment has been added as a child to the <FavoriteBooks> element, before all the <Book> elements.

Conclusion

In this Level, you leaned about the many ways you can use the modify() method to insert a node into an XML instance. As you’ve seen, you can add a node as a child element of the targeted node or as a sibling element to that node. You can also specify where the new element should be located among the other elements. In addition, the modify() method lets you add attributes and comments to your XML instance, as well as adding new elements. In the next level, you’ll learn how to use the method to modify element and attribute values in your XML instance.

This article is part of the parent stairway Stairway to XML

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

Share

Join the discussion and add your comment

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

Related content

Stairway to XML

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

in order to use the modify() method to delete data from typed and untyped XML instances, 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. Robert makes it seem simple, as usual.

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

2012-11-05

3,324 reads

Stairway to XML

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 DML expression in order to use the modify() method to update element and attribute values in either typed or untyped XML instances in an XML column. Robert Sheldon explains how.

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

2012-10-29

5,539 reads