XML Data modification language (XML DML) for SQL Server 2005 (go from MSDN)

XML Data modification language (XML DML) for SQL Server 2005 release date: 2006-08-14Update Date: 2006-08-14

Zhang Hongju Microsoft MVP

Apply to: SQL Server 2005,xml

Note: the content in this article requires relevant XML and XQuery knowledge

Contents of this page

	1. Insert
	2. Delete
	3. Replace

As an extension to the XQuery language, XML DML provides greater flexibility for XML data manipulation, rather than just some query operations on XML data. With XML DML, users can insert, update, and delete node content in XML as if they were working on a relational table. XML DML needs to be called through the Modify method of the XML data type.

1. Insert

Inserts are used to insert one or more nodes identified by the Expression1 as child nodes or sibling nodes of the node identified by the Expression2. The syntax format is as follows:

Insert       Expression1 ({as First | as last                 } to | after | before                                    Expression2                )

Expression1 and Expression2

Identifies one or more nodes to be inserted. It can be either a constant XML instance or an XQuery expression. The expression can draw a node, a text node, or a set of ordered nodes, but it cannot solve the root node. If the expression results in a value or a set of values, the values are inserted as a single text node, separating the values with a space. If multiple nodes are specified as constants, the nodes are enclosed in parentheses and separated by commas. However, you cannot insert heterogeneous sequences (such as group elements, attributes, or values). If Expression1 an empty sequence, the insert operation does not occur and no error is returned.

Into

Expression1 identifies a node that is inserted as a child of the node identified by the Expression2. If a node in Expression2 already has one or more child nodes, you must use either as first or as last to specify the desired new node add location.

After

The Expression1 identifies a node that is inserted directly behind it as a sibling of the node identified by the Expression2, and the After keyword cannot be used for the Insert property.

Before

The Before keyword cannot be used to insert an attribute that Expression1 identifies a node directly before it as a sibling of the node identified by the Expression2.

(1) Insert element in document

In the following example, the XML document is first assigned to a variable of the XML type. Then use several Insert XML DML statements to illustrate how to insert element nodes into the document. Note that "[1]" is specified for various path expressions in the example to require that only a single target be returned at a time, ensuring that only a single target node is present. After each insert, the SELECT statement displays the results. The final execution results are shown in 1.

DECLARE @myDoc xml       SET @myDoc = ' <root>           <item id= "1" >           </item>       </root> '       SELECT @myDoc        --Inserts the 1th child node of item without specifying an as first or as Lastset @myDoc. Modify ('       insert <author> Zhang Hongju </ Author>into (/root/item) [1] ') SELECT @myDoc        -Inserts the 2nd child node of item, as first specifies the insertion into the preceding SET @myDoc of the sibling child node       . Modify ('       Insert <title>sql Server 2005 Development Cookbook </title>as first into (/root/item) [1]       Select @myDoc        -Inserts the 2nd item node set @myDoc. Modify ('       insert <item id= ' 2 "></item>into (/root) [1] ')       SELECT @myDoc    -Inserts the 1th child node SET @myDoc into the 2nd item   . What's new in modify ('       insert <title>sql Server 2005 </title>as first into (/root/item) [2] ')       SELECT @myDocGO

Figure 1 Inserting a node into the XML

(2) inserting multiple elements into the document

In the following example, the <title> and <author> elements are inserted into the item node. Elements are separated by commas and enclosed in parentheses.

DECLARE @myDoc xmlset @myDoc = ' <root>           <item id= ' 1 ' >           </item>       </root> '   SELECT @myDoc SET @myDoc. Modify ('       insert (      <title>sql Server 2005 Development </title>,      <author> Zhang Hongju </author>       ) into (/root/item) [1] '); SELECT @myDoc   GO

(3) Inserting attributes into the document

In the following example, multiple properties are inserted into the XML document. Each time the property is inserted, the SELECT statement displays the result, as shown in the final execution of result 2.

DECLARE @myDoc xml       SET @myDoc = ' <root>           <item id= ' 1 ' >        <title>ajax combat </title>        <author> Zhang Hongju </author>    </item>       </root> '   SELECT @myDoc SET @myDoc. Modify ('       Insert attribute ShipTo {"Guangzhou"}into (/root/item[@ID = 1]) [1] '); SELECT @myDoc        --Specify the value to insert the property shipdate by a SQL variable           DECLARE @ShipDate char (one)           SET @ShipDate = ' 2006-01-23z '           SET @myDoc. Modify ('           Insert attribute shipdate {sql:variable ("@ShipDate") cast as xs:date?}           Into (/root/item[@ID =1]) [1] ');          SELECT @myDoc            -Inserts multiple attributes, separated by commas, and contains the           SET @myDoc in parentheses. Modify ('           insert (                    attribute postcode {") 253020 "},                   attribute Weight {" 1.5 "}                   )           

Figure 2 inserting attributes into XML

(4) Inserting a comment node

In the following example, the comment node is inserted after the <title> element in the item node with ID 2.

DECLARE @myDoc xml       SET @myDoc = ' <root>           <item id= ' 1 ' >        <title>ajax combat </title>        <author> Zhang Hongju </author>    </item>    <item id= "2" >        <title>asp. NET combat </title>        <author> Luguizhang </author>    </item> </root> '    SET @myDoc. Modify ( '           Insert <!--comments--           

The contents of the XML after inserting the comment are as follows:

<root>   <item id= "1" >        <title>ajax actual combat </title>        <author> Zhang Hongju </author >   </item>   <item id= "2" >        <title>asp. NET combat </title>        <!--comments--        <author> Luguizhang </author>   </item></root>

(5) Inserting data using CDATA sections

When you insert text that contains XML characters that are not valid (such as "<" or ">"), you can use CDATA sections to insert data. Refer to the following example:

DECLARE @myDoc xml       SET @myDoc = ' <root>           <item id= ' 1 ' >        <title>ajax combat </title>        <author> Zhang Hongju </author>    </item>    <item id= "2" >        <title>asp. NET combat </title>        <author> Luguizhang </author>    </item> </root> '    SET @myDoc. Modify ( ‘           

The XML invalid characters in the inserted section are converted to entities, such as "<" saved as &lt;. The following inserts a CDATA section after the contents of the XML document:

<root>   <item id= "1" >        <title>ajax </title>        <author> Zhang Hongju </author>   </item>   <item id= "2" >        <title>asp. NET combat </title>        <author> Luguizhang </author>        <desc> &lt; Shipping Methods &gt; Home &lt; Price & gt; </desc> not received   </item></root>

(6) Inserting text nodes

To insert a file into XML, you need to construct the text using the text function, referring to the following example:

DECLARE @myDoc xml       SET @myDoc = ' <root>           <item id= ' 1 ' >        <title>ajax combat </title>        <author> Zhang Hongju </author>    </item></root> '    SET @myDoc. Modify (' Insert text{' Order List "} as first into (/root) [1] '); SELECT @myDocGO

The resulting XML results are as follows:

<root> order list <item id= "1" ><title>ajax actual combat </title><author> Zhang Hongju </author></item ></root>

(7) Inserting a node into a typed XML column

In the following example, you first create a schema collection and set up a table that uses that schema collection. After you insert a schema-constrained XML into a table by using a Transact-SQL INSERT statement, insert an item node into the XML with XML DML insert.

--Creating an XML schema collection create XML Schema COLLECTION myschemasasn ' <?xml Version = "1.0"? ><xsd:schema targetnamespace= "http ://schemas.mybook.com/customerschemas "xmlns:xsd=" Http://www.w3.org/2001/XMLSchema "> <xsd:element name=" Customer "> <xsd:complexType> <xsd:sequence> <xsd:element maxoccurs=" UnB                            ounded "Name=" item "> <xsd:complexType> <xsd:sequence> <xsd:element name= "CustomerName" type= "xsd:string"/> <xsd:element name                            = "Address" type= "xsd:string"/> <xsd:element name= "Phone" type= "xsd:string"/>                        <xsd:element name= "Contact" type= "xsd:string"/> </xsd:sequence>                <xsd:attribute name= "ID" type= "Xsd:int"/> </xsd:complexType>          </xsd:element>  </xsd:sequence> </xsd:complexType> </xsd:element></xsd:schema> '; GO-Creates a table containing XML data type columns CREATE TABLE mycustomer (CustomerID int IDENTITY PRIMARY KEY, customeritem XML (Myschemas)); GO-inserts XML into the table that should conform to the definition of the Http://schemas.mybook.com/customerschemas namespace schema insert into mycustomervalues (N ' <c: Customer xmlns:c= "Http://schemas.mybook.com/customerschemas" > <item id= "1" > <customername> Northern Bookstore &lt ;/customername> <address> 22nd Zhi Chun Road, Haidian District, Beijing </address> <phone>2222222</phone> &lt ;contact> Mr. Liu </contact> </item></C:customer> '); --Insert another item node into XML with XML DML insert update MyCustomer SET customeritem.modify (' Declare namespace cs= ' http:// Schemas.mybook.com/customerschemas "; Insert (<item id= "2" > <customername> East View Building </customername> <address> Changchun 99th Chaoyang Street, &LT;/ADDR Ess> <phone>1111111</phone> <contact> Miss Sun &LT;/CONTACT&GT </item>) into (/cs:customer) [1] ') WHERE customerid=1; SELECT Customeritem from MyCustomer; GO

After executing the SELECT query above, you can see the XML content in Customeritem as follows:

<c:customer xmlns:c= "Http://schemas.mybook.com/customerschemas" >   <item id= "1" >        < customername> North Bookstore </customername>        <address> No. 22nd, Zhi Chun Road, Haidian District, Beijing </address>        <phone> 2222222</phone>        <contact> Mr. Liu </contact>   </item>   <item id= "2" >        < Customername> building </customername>        <address> Changchun Chaoyang Street, 99th </address>        <phone> 1111111</phone>        <contact> Miss Sun </contact>   

Back to top of page

2. Delete

Delete Deletes the node for the XML instance. The syntax format is as follows:

Delete Expression

expression is the XQuery expression of the node to be deleted. Deletes all nodes selected by the expression, as well as all nodes or values in the selected node. An expression cannot be a root (/) node. If an expression returns an empty sequence, no deletion is made and no error is returned.

The following example shows how to remove directives, comments, attributes, elements, and nodes from untyped XML variables. The XML is displayed after each deletion, as shown in result 3.

DECLARE @myDoc xmlset @myDoc = ' <? Instructions For=thewc.exe?><root>    <!--here is the comment--    <item id= "1" shipto= "Guangzhou" > here is the text        <title>ajax combat </title>        <author> Zhang Hongju </author>    </item>    <item id= "2" >        <title>asp. NET combat </title>        <author> Luguizhang </author>    </item></root> ' SELECT @myDoc--delete Comment SET @ Mydoc.modify (' delete/root/comment () ') SELECT @myDoc--Delete all instruction set @myDoc. Modify (' Delete//processing-instruction () ') Select @myDoc-Delete the text node set @myDoc in item with ID 1. Modify (' delete/root/item[@ID =1]/text () ') Select @myDoc--Delete a property set @myDoc . Modify (' delete/root/item[@ID =1]/@ShipTo ') SELECT @myDoc--Deletes an element set @myDoc. Modify (' delete/root/item[@ID =2]/author ') Select @myDoc-Delete the item node set @myDoc with ID 2. Modify (' delete/root/item[@ID =2] ') Select @myDocGO

Figure 3 Removing annotations, directives, attributes, elements, and nodes from untyped XML variables

The following statement shows a method for deleting a node from a typed XML, where the MyCustomer is created earlier in the section "Inserting a node into a typed XML column".

UPDATE mycustomerset customeritem.modify (' Declare namespace cs= "Http://schemas.mybook.com/customerschemas"; Delete/ cs:customer/item[@ID = 2] '); SELECT Customeritem from MyCustomer; GO

Back to top of page

3. Replace

Replace is used to update the values in the document. The syntax format is as follows:

Replace value of       Expression1 with      Expression2

Expression1

Identifies the node whose value is to be updated. It must identify only one single node. If XML is typed, the type of the node must be a single element, a text node, or an attribute node with simple type content (list or atomic type), not a union type, a complex type, a processing instruction, a document node, or an annotation node. Otherwise, an error is returned.

Expression2

Identifies the new value of the node. In modifying a typed XML instance, Expression2 and Expression1 must be of the same type.

The following example shows how to update the text and property values of an element in XML. Each time you make a change, the xml,4 is shown.

DECLARE @myDoc xmlset @myDoc = ' <root>    <item id= ' 1 ' >        <title>ajax combat </title>        < author> Zhang Hongju </author>    </item>    <item id= "2" >        <title>asp. NET combat </title>        <author> Luguizhang </author>    

Figure 4 Changing the text and attribute values of an element in XML

(GO) SQL Server 2005 XML data Modification language (XML DML) (go from MSDN)