Table中XML列的操作（SQL Server）

 --创建测试表
DECLARE  @Users TABLE
(
    ID INT IDENTITY(1,1),
    UserInfo XML
)

---插入测试数据
DECLARE @xml XML
SET @xml='
<root>
<user>
    <userid>1</userid>
    <userName>test1</userName>
</user>
</root>'
INSERT INTO @Users(UserInfo)VALUES(@xml)

--插入单节点,(类型：as first，as last，after（默认），before)
UPDATE @Users SET UserInfo.modify('insert <address>shanghai</address>
 into (/root/user)[1]') 

--插入多节点以','分割
UPDATE @Users SET UserInfo.modify('insert (<firstName>steven</firstName>,
<lastName>shi</lastName>) 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 <?Program = "A.exe" ?>  
 before (/root)[1]') 

---插入CDATA
UPDATE @Users SET UserInfo.modify(N'insert <C><![CDATA[<city>北京</city> or cdata]]> </C>
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 (<tel>888888</tel>)
                                        else (<qq>66666</qq>)
                                     into (/root/user)[1]') 

    ----判断节点Value
    UPDATE @Users SET UserInfo.modify('insert  if(/root/user[firstName="steven1"]) then (<tel>1111</tel>)
                                        else (<qq>2222</qq>)
                                     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='<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 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]')