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:
Declare
@XMLVar XML = '
<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
Set
@XMLVar. Modify
(
' Insert <first name= ' at a '/> as a (/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:
<book category= "ITPro"
>
<first name= "at a"
/>
<title>windows Step by step</title>
<before name= "Before"
/>
<author>bill zack</author>
<after name= "after"
/>
<price>49.99</price>
<last name= "At Last"
/>
</book>
B. Inserting multiple elements into a document
--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:
1:
<
Book
Category
= "ITPro"
>
2:
<
Title
>
Windows Step by step</
Title
>
3:
<
Author
>
Bill Zack.
4:
<
The
>
One element</
The
>
5:
<
Second
>
Second element</
Second
>
6:
</
Author
>
7:
<
Price
>
49.99</
Price
>
8:
<
The
>
One element</
The
>
9:
<
Second
>
Second element</
Second
>
10:
</
Book
>
C. Inserting a property into a document
--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 {' direct insert '})
)
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:
1:
<book category= "ITPro"
var= "Variable insertion"
>
2:
<title name= "Insert directly"
>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
Set
@XMLVar. Modify
(
' Insert text{' at a '} as a
)
Into (/catalog[1]/book[1]) '
SELECT
@XMLVar. Query ('/catalog[1]/book[1] '
);
The result set is:
1:
<
Book
Category
= "ITPro"
>
2:
At the
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
Set @XMLVar. Modify (
' Insert <!--inserts 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
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: <bookcategory= "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
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 Isa 2 step</price>
5: </book>
2.XML. Introduction to Modify (DELETE) statement
--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:
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
--Replace attribute
Set @XMLVar. Modify (' Replace value of (/catalog[1]/book[1]/@category))
With ("Replace attribute") '
--Replace content
Set @XMLVar. Modify (' Replace value of (/catalog[1]/book[1]/author[1]/text () [1])]
With ("Replace content") '
--Conditional substitution
Set @XMLVar. Modify (' 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] '
);
The result set is:
1: <catalog>
2: <bookcategory= "Replace Properties" >
3: <title>windows Step by step</title>
4: <author> Replace content </author>
5: <price>49.99</price>
6: </book>
7: <bookcategory= "condition replacement 2" >
8: <title>
Developing ADO .net</title>
9:
<author>
Andrew brust</author>
: <price>39.93</price>
One: </book>
: <bookcategory= "ITPro" >
<title>windows Cluster server</title>
: <author>stephen forte</author>
: <price>59.99</price>
: </book>
: </catalog>
