Operation Code for XML columns in SQL Server Table

Copy codeThe Code is as follows:
-- 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] ')
-- Insert multiple nodes separated by commas (,)
UPDATE @ Users SET UserInfo. modify ('insert (<firstName> steven </firstName>,
<LastName> shi </lastName>) into (/root/user) [1] ')
-- Add attributes
Declare @ editTime varchar (23 );
Set @edittime = CONVERT (VARCHAR (23), GETDATE (), 121 );
UPDATE @ Users SET UserInfo. modify (
N'insert (attribute editTime {SQL: variable ("@ editTime ")})
Into (/root/user/userid) [1]'
)
-- Insert multiple attributes separated by commas (,).
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 comments
UPDATE @ Users SET UserInfo. modify (n' insert <! -- Comment -->
Before (/root/user/userid [1]) [1] ')
--- Insert Processing Command
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 first
Into (/root/user) [1] ')
--- Insert According to the if Condition Statement
--- Determine attribute values
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 = "Steven 1"]) then (<tel> 1111 </tel>)
Else (<qq> 2222 </qq>)
Into (/root/user) [1] ')
---- Determine whether 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
-- Actions 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> Steven 2 </UI: firstName>
-- </UI: user> as first
-- 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] ')