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