SQLServerTable中XML列的操作代码

SQL Server Table中XML列的操作代码，需要的朋友可以参考下。

<br>--创建测试表 <br>DECLARE @Users TABLE <br>( <br>ID INT IDENTITY(1,1), <br>UserInfo XML <br>) <br>---插入测试数据 <br>DECLARE @xml XML <br>SET @xml=' <br><root> <br><user> <br><userid>1</userid> <br><username>test1</username> <br></user> <br></root>' <br>INSERT INTO @Users(UserInfo)VALUES(@xml) <br>--插入单节点,(类型：as first，as last，after（默认），before) <br>UPDATE @Users SET UserInfo.modify('insert shanghai <br>into (/root/user)[1]') <br>--插入多节点以','分割 <br>UPDATE @Users SET UserInfo.modify('insert (<firstname>steven</firstname>, <br><lastname>shi</lastname>) into (/root/user)[1]') <br>-- 增加属性 <br>declare @editTime varchar(23); <br>set @editTime=CONVERT(VARCHAR(23), GETDATE(), 121); <br>UPDATE @Users SET UserInfo.modify( <br>N'insert (attribute editTime {sql:variable("@editTime")}) <br>into(/root/user/userid)[1]' <br>) <br>--插入多属性以','分割 <br>declare @aid float,@bid float <br>set @aid=0.5 <br>UPDATE @Users SET UserInfo.modify('insert (attribute aid {sql:variable("@aid")}, <br>attribute bid {"test"} <br>) <br>into (/root/user)[1]') <br>---插入注释 <br>UPDATE @Users SET UserInfo.modify(N'insert <!-- 注释 --> <br>before (/root/user/userid[1])[1]') <br>---插入处理指令 <br>UPDATE @Users SET UserInfo.modify('insert <!--?Program = "A.exe" ?--> <br>before (/root)[1]') <br>---插入CDATA <br>UPDATE @Users SET UserInfo.modify(N'insert <c><!--[CDATA[<city-->北京 or cdata]]> </c> <br>after (/root/user)[1]') <br>---插入文本 <br>UPDATE @Users SET UserInfo.modify(N'insert text{"插入文本"} as first <br>into (/root/user)[1]') <br>---根据 if 条件语句进行插入 <br>---判断属性值 <br>UPDATE @Users SET UserInfo.modify('insert if(/root/user[@ID=1]) then (<tel>888888</tel>) <br>else (<qq>66666</qq>) <br>into (/root/user)[1]') <br>----判断节点Value <br>UPDATE @Users SET UserInfo.modify('insert if(/root/user[firstName="steven1"]) then (<tel>1111</tel>) <br>else (<qq>2222</qq>) <br>into (/root/user)[1]') <br>----判断user 节点数是否小于等于10 <br>UPDATE @Users SET UserInfo.modify('insert if (count(/root/user)<=10) then element user { "This is a new user" } <br>else () as last <br>into (/root)[1]') <br>SELECT * FROM @Users <br>--有命名空间的操作 <br>--DECLARE @xml XML <br>-- SET @xml='<root xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/users"> <br>-- <user> <br>-- <userid>1</userid> <br>-- <username>test1</username> <br>-- </user> <br>-- </root>' <br>-- INSERT INTO Users(UserInfo)VALUES(@xml) <br>-- UPDATE Users SET UserInfo.modify(' <br>-- declare namespace UI="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/users"; <br>-- insert <ui:user> <br>-- <ui:firstname>steven2</ui:firstname> <br>-- </ui:user> as first <br>-- into (/UI:root)[1]') <br>-- SELECT * FROM Users <br>-- UPDATE Users SET UserInfo.modify(' <br>-- declare namespace UI="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/users"; <br>-- insert attribute ID { "55" } <br>-- into (/UI:root/UI:user)[1]')