Insert, update, and delete XML data in SQL Server

XML is added to SQL Server. the Modify () method is xml. modify (insert), xml. modify (delete), xml. modify (replace) corresponds to XML insert, delete, and modify operations.
The following XML is used as an example to describe three DML types:
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. Introduction to XML. Modify (Insert) Statements
A. insert an element to A specified position using four parameters: as first, at last, before, and after.
Set
@ XMLVar. modify
(
'Insert <first name = "at first"/> as first into (/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"/> after (/catalog [1]/book [1]/author [1])'
)
SELECT
@ XMLVar. query ('/catalog [1]/book [1]'
);
Result set:
<Book category = "ITPro"
>
<First name = "at first"
/>
<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. Insert multiple elements into the document
-- Method 1: Use variables for insertion
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 2: insert directly
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]'
);
Result set:
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
>
10:
</
Book
>
C. Insert attributes into the document
-- Insert with variables
Declare @ var nvarchar (10) = 'variable insert'
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 insert
Set @ XMLVar. modify (
'Insert (attribute Id {"multi-value insert 1"}, attribute name {"multi-value insert 2 "})
)
Into (/catalog [1]/book [1]/author [1])'
SELECT @ XMLVar. query ('/catalog [1]/book [1]'
);
Result set:
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. Insert a text node
Set
@ XMLVar. modify
(
'Insert text {"at first"} as first
)
Into (/catalog [1]/book [1])'
SELECT
@ XMLVar. query ('/catalog [1]/book [1]'
);
Result set:
1:
<
Book
Category
= "ITPro"
>
2:
At first
3:
<
Title
>
Windows Step By Step </
Title
>
4:
<
Author
>
Bill Zack </
Author
>
5:
<
Price
>
49.99 </
Price
>
6:
</
Book
>
Note: Insert the following four options: as first, as last, before, and after. You can refer to the usage method in.
E. Insert comment nodes
Set @ XMLVar. modify (
'Insert <! -- Insert comment -->
)
Before (/catalog [1]/book [1]/title [1])'
SELECT @ XMLVar. query ('/catalog [1]/book [1]'
);
Result set:
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 that the insert comment node is also specific to four options: as first, as last, before, and after. You can refer to the usage method in.
F. Insert processing commands
Set @ XMLVar. modify (
'Insert <? Program "Instructions.exe"?>
)
Before (/catalog [1]/book [1]/title [1])'
SELECT @ XMLVar. query ('/catalog [1]/book [1]'
);
Result set:
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 Command is also specific to four options: as first, as last, before, and after. You can refer to the usage method in.
G. Insert According to the if Condition Statement
Set @ XMLVar. modify (
'Insert
)
If (/catalog [1]/book [1]/title [2]) then
Text {"this is a 1 step "}
Else (text {"this is a 2 step "})
Into (/catalog [1]/book [1]/price [1])'
SELECT @ XMLVar. query ('/catalog [1]/book [1]'
);
Result set:
1: <book category = "ITPro">
2: <title> Windows Step By Step </title>
3: <author> Bill Zack </author>
4: <price> 49.99 this isa 2 step </price>
5: </book>
2. Introduction to XML. Modify (delete) Statements
-- Delete attributes
Set @ XMLVar. modify ('delete/catalog [1]/book [1]/@ category ')
-- Delete a 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]');
Result set:
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>
10: </book>
11: </catalog>
3. Introduction to XML. Modify (replace) Statements
-- Replace attributes
Set @ XMLVar. modify ('replace value of (/catalog [1]/book [1]/@ category ))
With ("replace attributes ")'
-- Replace content
Set @ XMLVar. modify ('replace value of (/catalog [1]/book [1]/author [1]/text () [1])
With ("replace content ")'
-- Conditional replacement
Set @ XMLVar. modify ('replace value of (/catalog [1]/book [2]/@ category ))
With (
If (count (/catalog [1]/book)> 4) then
"Condition replacement 1"
Else
"Condition replacement 2 ")'
SELECT @ XMLVar. query ('/catalog [1]'
);
Result set:
1: <catalog>
2: <bookcategory = "replace attribute">
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>
10: <price> 39.93 </price>
11: </book>
12: <bookcategory = "ITPro">
13: <title> Windows Cluster Server </title>
14: <author> Stephen Forte </author>
15: <price> 59.99 </price>
16: </book>
17: </catalog>