Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted May 21, 2010

SQL Server and the XML Data Type : Data Manipulation

By Deanna Dicken

The introduction of the xml data type, with its own set of methods for processing xml data, made it possible for SQL Server developers to create columns and variables of the type xml. Deanna Dicken examines the modify() method, which provides for data manipulation of the XML data stored in the xml data type via XML DML statements.

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



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM