Code for executing insert, update, and delete operations on 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: Copy codeThe Code is 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. Introduction to XML. Modify (Insert) Statements

A. insert an element to A specified position using four parameters: as first, at last, before, and after.Copy codeThe Code is as follows: 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:Copy codeThe Code is as follows: 1: <book category = "ITPro">
2: <first name = "at first"/>
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. Insert multiple elements into the documentCopy codeThe Code is as follows: -- Method 1: Use variables to insert
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:Copy codeThe Code is 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>
10: </book>

C. Insert attributes into the documentCopy codeThe Code is as follows: -- insert a variable
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:Copy codeThe Code is as follows: 1: <book category = "ITPro" var = "variable insertion">
2: <title name = "insert"> Windows Step By Step </title>
3: <author Id = "multi-value insertion 1" name = "multi-value insertion 2"> Bill Zack </author>
4: <price> 49.99 </price>
5: </book>

D. Insert a text nodeCopy codeThe Code is as follows: set @ XMLVar. modify (
'Insert text {"at first"} as first)
Into (/catalog [1]/book [1])'
SELECT @ XMLVar. query ('/catalog [1]/book [1]');

Result set:Copy codeThe Code is as follows: 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 nodesCopy codeThe Code is as follows: set @ XMLVar. modify (
N' 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 commandsCopy codeThe Code is as follows: set @ XMLVar. modify (
'Insert <? Program "Instructions.exe"?>
Before (/catalog [1]/book [1]/title [1]) ')
SELECT @ XMLVar. query ('/catalog [1]/book [1]');

Result set:
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 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 StatementCopy codeThe Code is as follows: 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 is a 2 step </price>
5: </book>

2. Introduction to XML. Modify (delete) StatementsCopy codeThe Code is as follows: -- delete an attribute
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:Copy codeThe Code is 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>
10: </book>
11: </catalog>

3. Introduction to XML. Modify (replace) StatementsCopy codeThe Code is as follows: -- replace attributes
Set @ XMLVar. modify (n' replace value of (/catalog [1]/book [1]/@ category)
With ("replace attributes ")')
-- Replace content
Set @ XMLVar. modify (n' replace value of (/catalog [1]/book [1]/author [1]/text () [1])
With ("replace content ")')
-- Conditional replacement
Set @ XMLVar. modify (n' 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]');
[Code]
Result set:
[Code]
1: <catalog>
2: <book category = "replace attribute">
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>
10: <price> 39.93 </price>
11: </book>
12: <book category = "ITPro">
13: <title> Windows Cluster Server </title>
14: <author> Stephen Forte </author>
15: <price> 59.99 </price>
16: </book>
17: </catalog>