SQL Server and the XML Data Type : Data Manipulation

Introduction

With the introduction of the xml data type in SQL Server, it is
possible to create columns and variables of the type xml. The xml data type
comes with its own set of methods for processing xml data. The method we will
examine in this article is modify(). This method provides for data manipulation
of the XML data stored in the xml data type via XML DML statements.

XML DML

The xml data type methods utilize XQuery expressions to specify
the portion of the XML on which to operate. In the case of the modify() method,
extensions to XQuery were necessary to provide the data manipulation
capabilities. The extensions are insert, delete, and replace value of, which provide
for insertion, deletion, and modification of the XML value respectively. The
modify method is used with the SET statement or the SET clause of the UPDATE
statement due to the fact that it modifies the value of the column or variable.

The examples in the sections below will demonstrate manipulation
of an xml variable and therefore the SET statement is utilized. However, the
same type of syntax would be used if you were updating a column and thus needed
to use the SET clause of the UPDATE statement.

insert

To insert nodes into xml data types, the insert XML DML statement
is used. The syntax that follows is from books on line.

insert 
   Expression1 (
        {as first | as last} into | after | before
                     Expression2
        )

Expression1 represents the XML to be inserted. Next, the
positioning is specified relative to expression2, which is essentially a
landmark within the existing XML to help identify where expression1 should be
placed.

Let’s look at an example. In the SQL below an xml variable is
declared and filled with some XML. The XML represents a Workout element that
contains one or more BodyPart elements, which has attributes, and one or more
Exercise elements. In the first SET statement, the XML is altered by inserting
a new Exercise element before the existing Exercise element represented in the
XQuery expression. The second SET demonstrates inserting a new attribute into
the BodyPart element. You can see the modified XML in the results section.

DECLARE @myDoc xml
SET @myDoc = '<Workout>
  <BodyPart Location="Chest">Exercises in order.
    <Exercise>Flies</Exercise>
  </BodyPart>
</Workout>'
-- insert an exercise
SET @myDoc.modify('
  insert <Exercise>Bench Press</Exercise>
  before (/Workout/BodyPart/Exercise)[1]')
-- insert an attribute
SET @myDoc.modify('
  insert attribute Reps {"15"}
  into (/Workout/BodyPart[@Location="Chest"])[1]')
SELECT @myD

Result:

<Workout>
  <BodyPart 
     Location="Chest“ Reps="15“>
     Exercises in order.
    <Exercise>Bench Press</Exercise>
    <Exercise>Flies</Exercise>
  </BodyPart>
</Workout>

delete

The delete XML DML statement is used to remove a node from an xml
data type. Its syntax is simply "delete expression", where expression
is the XQuery expression representing the node to be removed.

In this example, similar XML is used to that in the previous
example. The BodyPart node has two Exercise child nodes. In the first modify(),
we will use delete to remove the first Exercise node as defined by the XQuery
expression "/Workout/BodyPart/Exercise[1]". The next modify() removes
the Reps attribute by specifying the XQuery expression
"/Workout/BodyPart/@Reps". In the results, you can see the new XML no
longer contains the Reps attribute or the first Exercise element.

DECLARE @myDoc xml
SET @myDoc = '<Workout>
 <BodyPart Location="Chest" 
      Reps="15" >Exercises in order.
  <Exercise>First exercise goes here</Exercise>
  <Exercise>Second exercise goes here</Exercise>
 </BodyPart>
</Workout>'
-- delete the first exercise
SET @myDoc.modify('delete /Workout/BodyPart/Exercise[1] ')
-- delete an attribute value
SET @myDoc.modify('delete /Workout/BodyPart/@Reps ')
SELECT @myDoc

Result:

<Workout>
  <BodyPart Location="Chest“>
  Exercises in order.
  <Exercise>Second exercise goes here</Exercise>
  </BodyPart>
</Workout>

replace value of

An update to a value within an xml variable or column is
accomplished using the replace value of XML DML statement of the modify() method.
The statement takes two expressions, as you can see below in the syntax from
books online. The first is the XQuery expression for the node whose value is to
be replaced. The second expression is for the new value of that node.

replace value of
	Expression1
with
	Expression2

There are several restrictions on the two expressions. The first
expression must:

• identify a single node
• be a simple type if the
  XML is typed
• return a single element
  that has simply typed content , an attribute node, or a text node
• not be a union or a
  complex type, a processing instruction, a document or comment node

The second expression must:

• be the same type or a
  subtype of the original value if the XML is typed
• be able to be atomized
  if the XML is untyped

Using the same XML as above, let’s look at an example of using
replace value to change the values of an element and an attribute. The first
modify() replaces the value of the first Exercise element. The value is changed
from "First exercise goes here" to "Bench Press". The
second modify() replaces the value of the Reps attribute from 15 to 10. The
final XML is displayed in the results.

DECLARE @myDoc xml
SET @myDoc = '<Workout>
  <BodyPart Location="Chest" 
      Reps="15" >Exercises in order.
    <Exercise>First exercise goes here</Exercise>
    <Exercise>Second exercise goes here</Exercise>
  </BodyPart>
</Workout>'
-- update text in the first exercise
SET @myDoc.modify('
  replace value of (/Workout/BodyPart/Exercise[1]/text())[1]
  with     "Bench Press" ')
SELECT @myDoc
-- update attribute value
SET @myDoc.modify('
  replace value of (/Workout/BodyPart/@Reps)[1]
  with     "10" ')
SELECT @myDoc

Result:

<Workout>
  <BodyPart Location="Chest" Reps="10">Exercises in order.
    <Exercise>Bench Press</Exercise>
    <Exercise>Second exercise goes here</Exercise>
  </BodyPart>
</Workout>

Conclusion

The XML DML statements, insert, delete, and replace value of, are
extensions of XQuery specifically added to allow for the missing capability of
manipulating XML data in the xml data type. The xml data type method modify()
is utilized to invoke these statements. XML data stored in variables can be
modified using these XML DML statements as part of the SET statement whereas
XML data in columns are changed by including these statements in the SET clause
of the UPDATE statement.

For More Information

Books Online XML (Transact-SQL)

Books Online XML DML

Books Online XML Data Type Methods

Books Online Insert (XML DML)

Books Online Delete (XML DML)

Books Online Replace value of (XML DML)

»


See All Articles by Columnist
Deanna Dicken