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('
x
ml
_
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>' );
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_exp
1
[
as
first | as last
] into | after | before
xquery
_exp
2
')
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_exp
2
) 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 theas first
oras last
keywords. If you specifyas first
, the new data is added before the existing child nodes. If you specifyas 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_exp
2
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;
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>
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;
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>
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;
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>
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;
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>
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;
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>
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;
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>
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;
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>
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;
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>
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.