SQL XML DML data modification

Author http://topic.csdn.net/u/20091018/18/03D066B7-C204-400B-9E41-DEB9F97AC58A.html

Some time ago, JJ wrote 18 sentences to get started with sqlserver XML. For more information, see DML data modification. Then, we made a section, which is a dog's tail. At the same time, I also promoted my blog.
1. xml DML data modification language, insert
Http://blog.csdn.net/HEROWANG/archive/2009/10/18/4694009.aspx
2. xml DML data modification language, deletion, modification
Http://blog.csdn.net/HEROWANG/archive/2009/10/18/4694020.aspx

SQL code
2. XML data modification language
insert
expression1 (
{as first | as last} into | after | before
expression2
)
expression1: identifies one or more nodes to be inserted.
expression2: The target ID node.
into: the node identified by expression1 is inserted as the direct descendant (subnode) of the node identified by expression2. If one or more sub-nodes exist in expression2, you must use as first or as last to specify the location for adding new nodes. For example, the sub-list is at the beginning or end of the sub-list. Ignore the as first and as last keywords when inserting attributes.
after: the nodes identified by expression1 are directly inserted after the nodes identified by expression2. The After keyword cannot be used to insert attributes.
before: the node identified by expression1 is directly inserted before the node identified by expression2. The before keyword cannot be used to insert attributes.

Declare @ myxml XML
Set @ myxml = '<? XML version = "1.0" encoding = "gb2312"?>
<Root>
<Item id = "1">
</Item>
</Root>'
Select @ myxml

-- 1. Insert the first child element of item
Set @ myxml. Modify ('insert <course1> SQL server2005 </course1> into (root/item) [1] ')
-- 2. Insert the first child element of the item to the beginning of the child element of the same level.
Set @ myxml. Modify ('insert <sname> Wang yinuo </sname> as first into (root/item) [1] ')
-- 3. Insert the first child element of the item to the end of the child element at the same level.
Set @ myxml. Modify ('insert <Grade> 82 </grade> As last into (root/item) [1] ')
-- 4. Insert the second item Node
Set @ myxml. Modify ('insert <item id = "2" year = "2009"> </item> into (/root) [1] ')
-- Or
Set @ myxml. Modify ('insert <item id = "2" year = "2009"> </item> after (/root/item) [1] ')
-- 5. Insert multiple elements to the second item Node
Set @ myxml. modify ('insert (<sname> Wang Hai </sname>, <course1> SQL server2005 </course1>, <Grade> 75 </grade>) into (/root/item) [2] ')
-- 6. Insert attributes
Set @ myxml. Modify ('insert attribute year {"2009"} into (/root/item) [1] ')
Select @ myxml
-- 7. Insert multiple attributes
Set @ myxml. Modify ('insert (attribute depart {"computer"}, attribute class {"Class 1"}) into (/root/item) [1] ')
-- 8. Insert a text node
Set @ myxml. Modify ('insert text {"into table"} as first into (/root) [1] ')

-- 9. Insert the entire Node
Set @ myxml. Modify ('insert <item id = "3" year = "2009">
<Sname> Wang ruotian </sname>
<Course1> Oracle </course1>
<Grade> 59 </grade> </item>
Into (/root) [1] ')
-- 10. Insert processing commands
Set @ myxml. Modify ('insert <? Program = "instructions.exe"?> Before (/root) [1] ')
Select @ myxml
Ii. delete operations
Declare @ myxml XML
Set @ myxml = '<? Program = "instructions.exe"?>
<Root> orders table -- this is a text node
<Item id = "1" year = "2009" Depart = "computer" class = "Class 1">
<Sname> Wang yinuo </sname>
<Course1> SQL server2005 </course1>
<Grade> 82 </grade>
</Item>
<Item id = "2" year = "2009" class = "Class 1">
<Sname> Wang Hai </sname>
<Course1> SQL server2005 </course1>
<Grade> 75 </grade> </item>
<Item id = "3" year = "2009" class = "Class 1">
<Sname> Wang ruotian </sname>
<Course1> Oracle </course1>
<Grade> 59 </grade>
</Item>
</Root>'

-- 1. Delete All commands
Set @ myxml. Modify ('delete // processing-instruction ()')
Select 'delete all directives '= @ myxml
-- 2. delete a text node
Set @ myxml. Modify ('delete/root/text ()')
Select 'delete text node' = @ myxml
-- 3. Delete the node ID attribute
Set @ myxml. Modify ('delete/root/item [@ ID = 1]/@ class ')
Select 'attributes for deleting node id' = @ myxml
-- 4. Delete the second node attribute
Set @ myxml. Modify ('delete/root/item [2]/@ class ')
Select 'delete second node attribute' = @ myxml
-- 5. delete node attributes
Set @ myxml. Modify ('delete/root/item/@ class ')
Select 'delete node attribute' = @ myxml
-- 6. Delete the element with the node ID
Set @ myxml. Modify ('delete/root/item [@ ID = 1]/grad ')
Select 'element for deleting node id' = @ myxml
-- 7. Delete the second node Element
Set @ myxml. Modify ('delete/root/item [2]/grad ')
Select 'delete the second node element '= @ myxml
-- 8. delete an element
Set @ myxml. Modify ('delete/root/item/grad ')
Select 'delete elemental '= @ myxml
-- 9. Delete the node with the ID
Set @ myxml. Modify ('delete/root/item [@ ID = 2] ')
Select 'delete ID node '= @ myxml
-- 10. Delete the second node.
Set @ myxml. Modify ('delete/root/item [2] ')
Select 'delete ID node '= @ myxml
-- 11. Delete the second node
Set @ myxml. Modify ('delete/root/item ')
Select 'delete node' = @ myxml
3. modification operations
-- 1. Modify the text value
Set @ myxml. Modify ('replace value of (/root/text () [1] with "Class 1 orders table "')
Select @ myxml
-- 2. Modify the element value
Set @ myxml. Modify ('replace value of (/root/item [@ ID = 3]/grade/text () [1] with "60 "')
Select @ myxml
-- 3. Modify the attribute value
Set @ myxml. Modify ('replace value of (/root/item [@ ID = 1]/@ Class) [1] with "Class 2 "')
Select @ myxml