--Create a test table
DECLARE @Users TABLE
(
ID INT IDENTITY (1,1),
UserInfo XML
)
---insert test data
DECLARE @xml XML
SET @xml = '
<root>
<user>
<userid>1</userid>
<userName>test1</userName>
</user>
</root> '
INSERT into @Users (UserInfo) VALUES (@xml)
--Insert a single node, (type: As First,as last,after (default), before)
UPDATE @Users SET userinfo.modify (' Insert <address>shanghai</address>
Into (/root/user) [1] ')
--Inserting multiple nodes to ', ' split
UPDATE @Users SET userinfo.modify (' Insert <firstname>steven</firstname>
<lastName>shi</lastName>) into (/root/user) [1] ')
--Adding attributes
DECLARE @editTime varchar (23);
Set @editTime =convert (VARCHAR), GETDATE (), 121);
UPDATE @Users SET userinfo.modify (
N ' Insert (attribute edittime {sql:variable ("@editTime")})
Into (/root/user/userid) [1] '
)
--Insert multiple attributes to ', ' split
Declare @aid float, @bid float
Set @aid =0.5
UPDATE @Users SET userinfo.modify (' Insert (attribute aid {sql:variable ("@aid")},
Attribute bid {"Test"}
)
Into (/root/user) [1] ')
---insert a comment
UPDATE @Users SET userinfo.modify (N ' Insert <!--annotation-->
Before (/root/user/userid[1]) [1] ')
---insert processing instruction
UPDATE @Users SET userinfo.modify (' Insert.? program = "A.exe"?>
Before (/root) [1] ')
---insert CDATA
UPDATE @Users SET userinfo.modify (N ' Insert <c><![ Cdata[<city> Beijing </city> or cdata]]> </C>
After (/root/user) [1] ')
---insert text
UPDATE @Users SET userinfo.modify (N ' Insert text{"Insert text"} as a
Into (/root/user) [1] ')
---Insert according to the IF condition statement
---Determine the value of a property
UPDATE @Users SET userinfo.modify (' Insert if (/root/user[@ID =1]) then (<tel>888888</tel>)
Else (<qq>66666</qq>)
Into (/root/user) [1] ')
----Judge Node value
UPDATE @Users SET userinfo.modify (' Insert if (/root/user[firstname= "Steven1"]) then (<tel>1111</tel>)
Else (<qq>2222</qq>)
Into (/root/user) [1] ')
----Determine if the number of user nodes is less than or equal to 10
UPDATE @Users SET userinfo.modify (' Insert if (count (/root/user) <=10) then element user {"This is a new user"}
Else () as Last
Into (/root) [1] ')
SELECT * from @Users
--Operations with namespaces
--declare @xml XML
--SET @xml = ' <root xmlns= ' http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/users ' >
--<user>
--<userid>1</userid>
--<userName>test1</userName>
--</user>
--</root> '
--INSERT into Users (UserInfo) VALUES (@xml)
--UPDATE Users SET userinfo.modify ('
--Declare namespace ui= "Http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/users";
--Insert <UI:user>
--<UI:firstName>steven2</UI:firstName>
--</UI:user> as a
--Into (/ui:root) [1] ')
--SELECT * from Users
--UPDATE Users SET userinfo.modify ('
--Declare namespace ui= "Http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/users";
--Insert attribute ID {"55"}
--Into (/ui:root/ui:user) [1] ')
