Insert, UPDATE, delete for XML data types in SQL Server

In recent days, SQL Operations XML has been organized as follows:

1. Inserting XML

DECLARE @myDoc XML
SET @myDoc = ' <Root>
<productdescription productid= "1" productname= "Road Bike" >
<Features> </Features>
</ProductDescription>
</Root> '
/* Inserts an element node into the document */
--Insert a node in the features
SET @myDoc. Modify (
' Insert <populate>put your things into basket of bike</populate>
Into (/root/productdescription/features) [1] ');
SELECT @myDoc;
--the node currently inserted is the first node in features
SET @myDoc. Modify ('
Insert <ride>people could ride bike</ride>
As first into (/root/productdescription/features) [1] ');
SELECT @myDoc;
--the node currently inserted is the last node in features
SET @myDoc. Modify ('
Insert <function> people use it as transport</function>
As last into (/root/productdescription/features) [1] ');
SELECT @myDoc;
--The currently inserted node is placed behind the <ride> tag
SET @myDoc. Modify ('
Insert <sport>ride Bike is a sport</sport>
After (/root/productdescription/features/ride) [1] ');
SELECT @myDoc;
--------------------------------------
/* Insert multiple elements into the document */
DECLARE @myDoc2 XML
SET @myDoc = ' <Root>
<productdescription productid= "1" productname= "Road Bike" >
<Features> </Features>
</ProductDescription>
</Root> '
DECLARE @NewFeatures XML
SET @NewFeatures = N ' <ride>people could ride bike</ride>
<sport>ride Bike is a sport</sport> '

Set@mydoc.modify ('
Insert Sql:variable ("@NewFeatures")
Into (/root/productdescription/features) [1] ')
SELECT @myDoc;
------------------------------------
--Inserting attributes into the document
DECLARE @myDoc XML;
SET @myDoc =
' <Root>
<location locationid= "Ten" >
<step>manufacturing Step 1 at the work center</step>
<step>manufacturing Step 2 at the work center</step>
</Location>
</Root> ';
--Inserts a number attribute in the location node with a value of 5
SET @myDoc. Modify ('
Insert attribute number {"5"}
Into (/root/location[@LocationID = 10]) [1] ')
SELECT @myDoc;
--Insert a variable in the location node
DECLARE @hour INT
SET @hour = 2;
SET @myDoc. Modify ('
Insert Attribute Hour {sql:variable ("@hour")}
Into (/root/location[@LocationID = 10]) [1] ')
SELECT @myDoc;
--------------------------------------------
--Add a new node to the table type as an XML field
IF object_id (' T ') is not NULL DROP TABLE T
CREATE TABLE T (i int, x XML);
Go
INSERT into T VALUES (1, ' <Root>
<productdescription productid= "1" productname= "Road Bike" >
<Features>
<warranty>1 Year parts and labor</warranty>
&LT;MAINTENANCE&GT;3 year parts and labor extended maintenance are available</maintenance>
</Features>
</ProductDescription>
</Root> ');
Go
UPDATE T
SET x.modify ('
Insert <ride>people could ride bike</ride>
After (/root/productdescription/features/maintenance) [1] ')
SELECT x.query ('/root/productdescription/features ') from T
-----------------------------------
--Insert according to the IF condition
DECLARE @myDoc XML;
SET @myDoc =
' <Root>
<location locationid= "laborhours=" 1.2 ">
<step>manufacturing Step 1 at the work center</step>
<step>manufacturing Step 2 at the work center</step>
</Location>
</Root> ';
--Add a new hour property to meet the current conditions
SET @myDoc. Modify ('
Insert
if (/root/location[@LocationID = 10])
Then attribute hour {"5"}
Else ()
Into (/root/location[@LocationID = 10]) [1] ')
SELECT @myDoc;
--Add a new node to meet the current conditions
SET @myDoc. Modify ('
Insert
if (count (/root/location/step) <= 2)
Then element step {"This is new step"}
Else ()
As first into (/root/location) [1] ')
SELECT @myDoc;

2. Update XML

DECLARE @myDoc XML
SET @myDoc = ' <root>
<location locationid= "Ten"
Laborhours= "1.1"
Machinehours= ". 2" >manufacturing steps is described here.
<step>manufacturing Step 1 at the Center</step>
<step>manufacturing Step 2 at the This work cen Ter</step>
</location>
</Root> '
SELECT @myDoc;
--The value of the LaborHours property in the replacement node location is
SET @myDoc. Modify ('
Replace value of (/root/location/@LaborHours) [1]
With "" ")
Select @myDoc;
--Using a table to update the properties of a field of type XML in another table
DECLARE @Friend table
(
ID INT IDENTITY (PRIMARY) KEY not NULL,
Friend XML
)
INSERT into @Friend select ' <friends>
<friend name= "Junwenli" sex= "man" age= "></friend>
<friend name= "Jinhanliu" sex= "man" age= "></friend>",
<friend name= "Fangcheng" sex= "Man" age= " "></FRIEND>
</Friends>"

DECLARE @Temp TABLE
(
ID INT IDENTITY (PRIMARY) KEY is not NULL,
Friendname NVARCHAR (32)
)
INSERT into @Temp SELECT ' Guohu ';

UPDATE F
SET friend.modify (' Replace value ' of (friends/friend/@name) [1] with Sql:column ("T.friendname")
From @Friend F, @Temp T
WHERE f.id = t.id;

SELECT Friend from @Friend;

3. Delete XML

DECLARE @myDoc XML
SET @myDoc = ' <? Instructions For=thewc.exe?>
<Root>
<!--instructions for the 1st work center--
<location locationid= "10"
Laborhours= "1.1"
Machinehours= ". 2" >some Text 1
<step>manufacturing Step 1 at the work center</step>
<step>manufacturing Step 2 at the work center</step>
</Location>
</Root> '
--Delete attribute MachineHours
SET @myDoc. Modify ('
delete/root/location/@MachineHours
‘)
SELECT @myDoc
--Delete the second step node
SET @myDoc. Modify ('
DELETE/ROOT/LOCATION/STEP[2]
‘)
SELECT @myDoc

Insert, UPDATE, delete for XML data types in SQL Server