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]’)