SQL Server TableのXML列の操作コード

 
  
--      
  
DECLARE @Users TABLE 
  
( 
  
ID INT IDENTITY(1,1), 
  
UserInfo XML 
  
) 
  
---       
  
DECLARE @xml XML 
  
SET @xml=' 
  
 
  
 
  
1 
  
test1 
  
 
  
' 
  
INSERT INTO @Users(UserInfo)VALUES(@xml) 
  
--     ,(  ：as first，as last，after（  ），before) 
  
UPDATE @Users SET UserInfo.modify('insert 
shanghai
 
  
into (/root/user)[1]') 
  
--      ','   
  
UPDATE @Users SET UserInfo.modify('insert (steven, 
  
shi) into (/root/user)[1]') 
  
--      
  
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]' 
  
) 
  
--      ','   
  
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]') 
  
---     
  
UPDATE @Users SET UserInfo.modify(N'insert  
  
before (/root/user/userid[1])[1]') 
  
---       
  
UPDATE @Users SET UserInfo.modify('insert  
  
before (/root)[1]') 
  
---  CDATA 
  
UPDATE @Users SET UserInfo.modify(N'insert    or cdata]]>  
  
after (/root/user)[1]') 
  
---     
  
UPDATE @Users SET UserInfo.modify(N'insert text{"    "} as first 
  
into (/root/user)[1]') 
  
---   if          
  
---      
  
UPDATE @Users SET UserInfo.modify('insert if(/root/user[@ID=1]) then (888888) 
  
else (66666) 
  
into (/root/user)[1]') 
  
----    Value 
  
UPDATE @Users SET UserInfo.modify('insert if(/root/user[firstName="steven1"]) then (1111) 
  
else (2222) 
  
into (/root/user)[1]') 
  
----  user          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 
  
--         
  
--DECLARE @xml XML 
  
-- SET @xml=' 
  
--  
  
-- 1 
  
-- test1 
  
--  
  
-- ' 
  
-- 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  
  
-- steven2 
  
--  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]')