SQL Server Table中XML列的操作代码

--创建测试表
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]')