SQLServer20052008 added support for XML data and added several XML operations. This article mainly uses SQLServer2008 as an example to introduce how to insert, update, and delete XML data.
SQL Server 2005/2008 has added support for XML data and added several XML operations. This document uses SQL Server 2008 as an example to describe how to insert, update, and delete XML data.
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 ='
Windows Step By Step
Bill Zack
49.99
Developing ADO. NET
Andrew Brust
39.93
Windows Cluster Server
Stephen Forte
59.99
'
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 As first into (/catalog [1]/book [1])'
)
Set
@ XMLVar. modify
(
'Insert As last into (/catalog [1]/book [1])'
)
Set
@ XMLVar. modify
(
'Insert Before (/catalog [1]/book [1]/author [1])'
)
Set
@ XMLVar. modify
(
'Insert after (/catalog [1]/book [1]/author [1])'
)
SELECT
@ XMLVar. query ('/catalog [1]/book [1]'
);
Result set:
>
/>
Windows Step By Step
/>
Bill Zack
/>
49.99
/>
B. Insert multiple elements into the document
-- Method 1: Use variables for insertion
DECLARE @ newFeatures xml;
SET @ newFeatures = N'
One element
Second element '
SET @ XMLVar. modify ('
)
Insert SQL: variable ("@ newFeatures ")
Into (/catalog [1]/book [1])'
-- Method 2: insert directly
Set @ XMLVar. modify ('
)
Insert ( One element , Second element )
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:
Var = "variable insertion"
>
2:
> Windows Step By Step
3:
Name = "multi-value insert 2"
> Bill Zack
4:
49.99
5:
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
)
Before (/catalog [1]/book [1]/title [1])'
SELECT @ XMLVar. query ('/catalog [1]/book [1]'
);
Result set:
1:
>
2:
3:
Windows Step By Step
4:
Bill Zack
5:
49.99
6:
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
)
Before (/catalog [1]/book [1]/title [1])'
SELECT @ XMLVar. query ('/catalog [1]/book [1]'
);
Result set:
1:
2:
3: Windows Step By Step
4: Bill Zack
5: 49.99
6:
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:
2: Windows Step By Step
3: Bill Zack
4: 49.99 this isa 2 step
5:
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:
2:
3:
4: 49.99
5:
6:
7: Windows Cluster Server
8: Stephen Forte
9: 59.99
10:
11:
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:
2:
3: Windows Step By Step
4: replace content
5: 49.99
6:
7:
8: <BR> Developing ADO. NET
9:
Andrew Brust
10: 39.93
11:
12:
13: Windows Cluster Server
14: Stephen Forte
15: 59.99
16:
17:
