The new Xml.modify () method is added to SQL Server, xml.modify (insert), xml.modify (delete), xml.modify (replace) to insert, delete, and modify the XML.
This article takes the following XML as an example to illustrate three types of DML:
Copy Code code as follows:
DECLARE @XMLVar XML;
SET @XMLVar = '
<catalog>
<book category= "ITPro" >
<title>windows Step by step</title>
<author>bill zack</author>
<price>49.99</price>
</book>
<book category= "Developer" >
<title>developing ADO .net</title>
<author>andrew brust</author>
<price>39.93</price>
</book>
<book category= "ITPro" >
<title>windows Cluster server</title>
<author>stephen forte</author>
<price>59.99</price>
</book>
</catalog>
1.XML. Introduction to Modify (Insert) statement
A. Inserting an element into a specified position using the as First,at last,before,after four parameters
Copy Code code as follows:
Set @XMLVar. Modify (
' Insert <first name= ' at '/> as ' (/catalog[1]/book[1] ')
Set @XMLVar. Modify (
' Insert <last name= ' at last '/> as last into (/catalog[1]/book[1) ')
Set @XMLVar. Modify (
' Insert <before name= ' before '/> before (/catalog[1]/book[1]/author[1]) '
Set @XMLVar. Modify (
' Insert <after name= ' after "/> (/catalog[1]/book[1]/author[1)")
SELECT @XMLVar. Query ('/catalog[1]/book[1] ');
The result set is:
Copy Code code as follows:
1: <book category= "ITPro" >
2: <first name= "at the"/>
3: <title>windows Step by step</title>
4: <before name= "before"/>
5: <author>bill zack</author>
6: <after name= "after"/>
7: <price>49.99</price>
8: <last name= "at last"/>
9: </book>
B. Inserting multiple elements into a document
Copy Code code as follows:
--Method One: Insert with variable
DECLARE @newFeatures XML;
SET @newFeatures = N '; <first>one element</first> <second>second element</second> '
SET @XMLVar. Modify (')
Insert Sql:variable ("@newFeatures")
Into (/catalog[1]/book[1]) '
--Method Two: Direct insertion
Set @XMLVar. Modify (')
Insert (<first>one element</first>,<second>second element</second>)
Into (/catalog[1]/book[1]/author[1]) '
SELECT @XMLVar. Query ('/catalog[1]/book[1] ');
The result set is:
Copy Code code as follows:
1: <book category= "ITPro" >
2: <title>windows Step by step</title>
3: <author>bill Zack
4: <first>one element</first>
5: <second>second element</second>
6: </author>
7: <price>49.99</price>
8: <first>one element</first>
9: <second>second element</second>
: </book>
C. Inserting a property into a document
Copy Code code as follows:
--Using variables to insert
declare @var nvarchar (10) = ' variable insertion '
Set @XMLVar. Modify (
' Insert (attribute var {sql:variable ("@var")}))
Into (/catalog[1]/book[1]) '
--Insert Directly
Set @XMLVar. Modify (
' Insert (attribute name {' Insert directly '})
Into (/catalog[1]/book[1]/title[1]) '
--Multi-value insertion
Set @XMLVar. Modify (
' Insert (attribute Id {' multi-valued insert 1 '},attribute name {' Multivalued insert 2} ')
Into (/catalog[1]/book[1]/author[1]) '
SELECT @XMLVar. Query ('/catalog[1]/book[1] ');
The result set is:
Copy Code code as follows:
1: <book category= "ITPro" var= "variable Insert" >
2: <title name= "direct insert" >windows step by step</title>
3: <author id= "Multi-value Insert 1" name= "multi-value Insert 2" >bill zack</author>
4: <price>49.99</price>
5: </book>
D. Inserting a text node
Copy Code code as follows:
Set @XMLVar. Modify (
' Insert text{' at the ' at a '} as a
Into (/catalog[1]/book[1]) '
SELECT @XMLVar. Query ('/catalog[1]/book[1] ');
The result set is:
Copy Code code as follows:
1: <book category= "ITPro" >
2:at
3: <title>windows Step by step</title>
4: <author>bill zack</author>
5: <price>49.99</price>
6: </book>
Note: Insert this article the same specific as First,as last,before,after four options, you can refer to the use of a method
E. Inserting annotation nodes
Copy Code code as follows:
Set @XMLVar. Modify (
N ' Insert <!--insert Comment-->
Before (/catalog[1]/book[1]/title[1])
SELECT @XMLVar. Query ('/catalog[1]/book[1] ');
The result set is:
1: <book category= "ITPro" >
2: <!--insert Comment-->
3: <title>windows Step by step</title>
4: <author>bill zack</author>
5: <price>49.99</price>
6: </book>
Note Inserting the annotation node is also specific as the as First,as last,before,after four options, you can refer to the use method in a
F. Insert processing instructions
Copy Code code as follows:
Set @XMLVar. Modify (
' Insert;? Program "Instructions.exe"?>
Before (/catalog[1]/book[1]/title[1])
SELECT @XMLVar. Query ('/catalog[1]/book[1] ');
The result set is:
1: <book category= "ITPro" >
2:. Program "Instructions.exe"?>
3: <title>windows Step by step</title>
4: <author>bill zack</author>
5: <price>49.99</price>
6: </book>
Note that the Insert processing instruction is also specific as the as First,as last,before,after four options, you can refer to the use method in a
G. Inserting according to the IF condition statement
Copy Code code as follows:
Set @XMLVar. Modify (
' Insert
if (/catalog[1]/book[1]/title[2]) then
Text{"This are a 1 step"}
Else (text{"This are a 2 step"})
Into (/catalog[1]/book[1]/price[1])
SELECT @XMLVar. Query ('/catalog[1]/book[1] ');
The result set is:
1: <book category= "ITPro" >
2: <title>windows Step by step</title>
3: <author>bill zack</author>
4: <price>49.99this is a 2 step</price>
5: </book>
2.XML. Introduction to Modify (DELETE) statement
Copy Code code as follows:
--Delete attribute
Set @XMLVar. Modify (' delete/catalog[1]/book[1]/@category ')
--Delete node
Set @XMLVar. Modify (' delete/catalog[1]/book[1]/title[1] ')
--Delete Content
Set @XMLVar. Modify (' Delete/catalog[1]/book[1]/author[1]/text () ')
--Delete all
Set @XMLVar. Modify (' delete/catalog[1]/book[2] ')
SELECT @XMLVar. Query ('/catalog[1] ');
The result set is:
Copy Code code as follows:
1: <catalog>
2: <book>
3: <author/>
4: <price>49.99</price>
5: </book>
6: <book category= "ITPro" >
7: <title>windows Cluster server</title>
8: <author>stephen forte</author>
9: <price>59.99</price>
: </book>
One: </catalog>
3.XML. Introduction to Modify (replace) statement
Copy Code code as follows:
--Replace attribute
Set @XMLVar. Modify (N ' replace value of (/catalog[1]/book[1]/@category)
With ("Replace property")
--Replace content
Set @XMLVar. Modify (N ' replace value of (/catalog[1]/book[1]/author[1]/text () [1])
With ("Replace content")
--Conditional substitution
Set @XMLVar. Modify (N ' replace value of (/catalog[1]/book[2]/@category)
With
if (count (/catalog[1]/book) >4) Then
"Conditional substitution 1"
Else
"Conditional substitution 2")
SELECT @XMLVar. Query ('/catalog[1] ');
[Code]
The result set is:
[Code]
1: <catalog>
2: <book category= "Replace Properties" >
3: <title>windows Step by step</title>
4: <author> Replace content </author>
5: <price>49.99</price>
6: </book>
7: <book category= "condition replacement 2" >
8: <title>developing ADO .net</title>
9: <author>andrew brust</author>
: <price>39.93</price>
One: </book>
<book category= "ITPro" >
<title>windows Cluster server</title>
: <author>stephen forte</author>
: <price>59.99</price>
: </book>
: </catalog>
