7.8. XML Data Manipulation Language

XML DML extends the XQuery language to support data modification in xml data type instances. XML DML adds the insert, delete, and replace value of keywords to the XQuery language.

The examples in the subsections that follow use a table named xmldmlTable. Create this table by executing the following statement:

    USE ProgrammingSqlServer2005

    CREATE TABLE xmldmlTable
    (
      ID int,
      xmlCol xml,
    CONSTRAINT PK_xmldmlTable
      PRIMARY KEY CLUSTERED (ID)
    )

The added XML DML keywords are used to modify xml data type instances as detailed in the following three subsections.

7.8.1. insert

The XML DML insert statement inserts one or more nodes as child nodes or siblings of a specified node in an xml data type instance. The syntax for the insert keyword follows:

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

where:

Expression1

A constant XML instance or an XQuery expression identifying one or more nodes to insert. It cannot resolve to the root (/) node. If multiple nodes are specified by the constant XML instance, they must be enclosed in parentheses and separated by commas.

into

Nodes identified by Expression1 are inserted as child nodes of the node identified by Expression2 .

{ as first | as last }

If the node identified by Expression2 already has one or more child nodes, you must use either the as first or as last keywords to specify the location in which to insert the new child nodeseither at the beginning or at the end of the child list.

The as first and as last keywords are ignored when inserting attributes.

after

Nodes identified by Expression1 are inserted as siblings immediately after the node identified by Expression2. The after keyword cannot be used to insert attributes.

before

Nodes identified by Expression1 are inserted immediately before the node identified by Expression2. The before keyword cannot be used to insert attributes.

Expression2

A constant XML instance or an XQuery expression identifying a single existing node. Nodes identified by Expression1 are inserted relative to this node. The insert fails if Expression2 identifies more than one node.

The following example creates a record in the xmldmlTable with ID = 1 and the xmlCol xml data type column set to a simple XML document. The example then adds a child element named childElement0 as the first child of the root node using an insert XML DML statement.

    INSERT INTO xmldmlTable (ID, xmlCol)
    VALUES (1, '<root><childElement1 value="1"/></root>')

    SELECT xmlCol FROM xmldmlTable WHERE ID = 1

    UPDATE xmldmlTable
    SET xmlCol.modify('insert <childElement0 value="0"/> as first into (/root)[1]')
    WHERE ID = 1

    SELECT xmlCol FROM xmldmlTable WHERE ID = 1

Two result sets are returned, as shown in Figure 7-17.

The first result set shows the xmlCol value before the XML DML insert. The second result set after the insert XML DML command shows the new childelement0 element as the first child of the root element.

Figure 7-17. Results for XML DML insert example

7.8.2. delete

The XML DML delete statement deletes one or more nodes from an xml data type instance. The syntax of the delete keyword is:

    delete Expression

where:

Expression

An XQuery expression specifying one or more nodes to delete. All nodes specified by the XQuery expression and all contained (child) nodes are deleted. The expression cannot be the root (/) node.

The following example creates a record in the xlmdmlTable with ID = 2. It assigns the elements childElement1 and childElement2 to the xlmCol column, then deletes childElement1.

    INSERT INTO xmldmlTable (ID, xmlCol)
    VALUES (2, '<root><childElement1 value="1"/>
      <childElement2 value="2"/></root>')

    SELECT xmlCol FROM xmldmlTable WHERE ID = 2

    UPDATE xmldmlTable
    SET xmlCol.modify('delete (/root/childElement1)')
    WHERE ID = 2

    SELECT xmlCol FROM xmldmlTable WHERE ID = 2

Two result sets are returned, as shown in Figure 7-18.

Figure 7-18. Results for XML DML delete example

The first result set shows the xmlCol value before the XML DML delete. The second result set after the delete XML DML command shows the removal of the childelement1 element from the root element.

7.8.3. replace value of

The XML DML replace value of statement updates the value of a node in an xml data type instance. The syntax of replace value of is:

    replace value of Expression1 with Expression2

where:

Expression1

A constant XML instance or an XQuery expression identifying a single node to update. An error will result if multiple nodes are specified. Expression1 must identify an element with simply typed content, a text node, or an attribute nodespecifying a union type, complex type, processing instruction, document node, or comment node will return an error.

Expression2

The new value of the node. When updating a type xml data type instance, Expression2 must have the same subtype as Expression1.

The following example updates the value attribute for element childElement1:

    INSERT INTO xmldmlTable (ID, xmlCol)
    VALUES (3, '<root><childElement1 value="1"/></root>')

    SELECT xmlCol FROM xmldmlTable WHERE ID = 3

    UPDATE xmldmlTable
    SET xmlCol.modify('replace value of (/root/childElement1/@value)[1] with "100"')
    WHERE ID = 3

    SELECT xmlCol FROM xmldmlTable WHERE ID = 3

Two result sets are returned, as shown in Figure 7-19. The first result set shows the xmlCol value before the XML DML replace value of. The second result set after the replace value of XML DML command shows the value attribute of the childElement1 element changed from 1 to 100.

Figure 7-19. Results for XML DML replace value of example

7.8.4. XML DML Limitations and Restrictions

XML DML cannot be used to insert, delete, or modify the following:

• xmlns, xmlns.*, or xml:base attributes in either typed or untyped xml data type instances.

• xsi:nil or xsi:type attributes in typed xml data type instances.

Additionally, XML DML has the following restriction:

• The xml:base attribute cannot be inserted into either typed or untyped xml data type instances.