最近比较忙,一直搁浅了,今天补上XML Insert 操作,对于XML的操作很多,这里只能列出一些常用的,不闲扯了,直接上代码:
代码
<!--Code highlighting produced by Actipro CodeHighlighter (freeware)http://www.CodeHighlighter.com/-->
-- 创建测试数据库
CREATE DATABASE mytest;
GO
USE mytest;
GO
-- 创建测试表
CREATE TABLE Users
(
ID INT IDENTITY ( 1 , 1 ),
UserInfo XML
)
/* ****************插入单节点**************************** */
-- -插入测试数据
DECLARE @xml XML
SET @xml = '
1
test1
'
INSERT INTO Users(UserInfo) VALUES ( @xml )
-- select * from Users
-- 添加address节点,默认添加在所有节点之后
UPDATE Users SET UserInfo.modify( ' insert shanghai
into (/root/user)[1] ' )
SELECT * FROM Users
-- 添加address节点,节点顺序为第一个
UPDATE Users SET UserInfo.modify( ' insert shanghai as first
into (/root/user)[1] ' )
SELECT * FROM Users
-- 添加address节点,节点顺序为最后一个
UPDATE Users SET UserInfo.modify( ' insert shanghai as last
into (/root/user)[1] ' )
SELECT * FROM Users
-- 添加address节点到某个节点之后
UPDATE Users SET UserInfo.modify( ' insert shanghai
after (/root/user/userid)[1] ' )
-- 添加address节点到某个节点之前
UPDATE Users SET UserInfo.modify( ' insert shanghai
before (/root/user/userid)[1] ' )
SELECT * FROM Users
-- -注:[1] 代表第一个item
/* ****************插入多节点**************************** */
-- 添加firstName and LastName,以,分割
UPDATE Users SET UserInfo.modify( ' insert (steven,
shi) into (/root/user)[1] ' )
SELECT * FROM Users
/* ****************插入节点属性**************************** */
-- -在USER 节点插入ID 属性,并且值=1
UPDATE Users SET UserInfo.modify( ' insert attribute ID {"1"}
into (/root/user)[1] ' )
SELECT * FROM Users
-- -在USER 节点插入ID 属性,值为一个变量,这个应该更常用
declare @uid float
set @uid = 0.5
UPDATE Users SET UserInfo.modify( ' insert attribute UID {sql:variable("@Uid")}
into (/root/user)[1] ' )
SELECT * FROM Users
-- -如果插入多个属性,需要用,分割
declare @aid float , @bid float
set @aid = 0.5
set @bid = 0.6
UPDATE Users SET UserInfo.modify( ' insert (attribute aid {sql:variable("@aid")},
attribute bid {sql:variable("@bid")}
)
into (/root/user)[1] ' )
SELECT * FROM Users
/* ****************插入节点注释**************************** */
-- -insert 注释
UPDATE Users SET UserInfo.modify( ' insert <!-- 注释 -->
before (/root/user/userid[1])[1] ' )
SELECT * FROM Users
/* ****************插入处理指令**************************** */
UPDATE Users SET UserInfo.modify( ' insert <?Program = "A.exe" ?>
before (/root)[1] ' )
SELECT * FROM Users
/* ****************插入CDATA**************************** */
UPDATE Users SET UserInfo.modify( ' insert 北京 or cdata]]>
after (/root/user)[1] ' )
SELECT * FROM Users
/* ****************插入文本**************************** */
UPDATE Users SET UserInfo.modify( ' insert text{"插入文本"} as first
into (/root/user)[1] ' )
SELECT * FROM Users
/* ****************根据 if 条件语句进行插入**************************** */
-- -判断属性值
UPDATE Users SET UserInfo.modify( ' insert if(/root/user[@ID=1]) then (888888)
else (66666)
into (/root/user)[1] ' )
SELECT * FROM Users
-- --判断节点Value
UPDATE Users SET UserInfo.modify( ' insert if(/root/user[firstName="steven1"]) then (1111)
else (2222)
into (/root/user)[1] ' )
SELECT * FROM Users
-- --判断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
/* ****************将节点插入类型化的 xml 列中**************************** */
UPDATE Users SET UserInfo.modify( '
declare namespace UI="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/users";
insert
steven2
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] ' )
SELECT * FROM Users
-- 创建测试数据库
CREATE DATABASE mytest;
GO
USE mytest;
GO
-- 创建测试表
CREATE TABLE Users
(
ID INT IDENTITY ( 1 , 1 ),
UserInfo XML
)
/* ****************插入单节点**************************** */
-- -插入测试数据
DECLARE @xml XML
SET @xml = '
1
test1
'
INSERT INTO Users(UserInfo) VALUES ( @xml )
-- select * from Users
-- 添加address节点,默认添加在所有节点之后
UPDATE Users SET UserInfo.modify( ' insert shanghai
into (/root/user)[1] ' )
SELECT * FROM Users
-- 添加address节点,节点顺序为第一个
UPDATE Users SET UserInfo.modify( ' insert shanghai as first
into (/root/user)[1] ' )
SELECT * FROM Users
-- 添加address节点,节点顺序为最后一个
UPDATE Users SET UserInfo.modify( ' insert shanghai as last
into (/root/user)[1] ' )
SELECT * FROM Users
-- 添加address节点到某个节点之后
UPDATE Users SET UserInfo.modify( ' insert shanghai
after (/root/user/userid)[1] ' )
-- 添加address节点到某个节点之前
UPDATE Users SET UserInfo.modify( ' insert shanghai
before (/root/user/userid)[1] ' )
SELECT * FROM Users
-- -注:[1] 代表第一个item
/* ****************插入多节点**************************** */
-- 添加firstName and LastName,以,分割
UPDATE Users SET UserInfo.modify( ' insert (steven,
shi) into (/root/user)[1] ' )
SELECT * FROM Users
/* ****************插入节点属性**************************** */
-- -在USER 节点插入ID 属性,并且值=1
UPDATE Users SET UserInfo.modify( ' insert attribute ID {"1"}
into (/root/user)[1] ' )
SELECT * FROM Users
-- -在USER 节点插入ID 属性,值为一个变量,这个应该更常用
declare @uid float
set @uid = 0.5
UPDATE Users SET UserInfo.modify( ' insert attribute UID {sql:variable("@Uid")}
into (/root/user)[1] ' )
SELECT * FROM Users
-- -如果插入多个属性,需要用,分割
declare @aid float , @bid float
set @aid = 0.5
set @bid = 0.6
UPDATE Users SET UserInfo.modify( ' insert (attribute aid {sql:variable("@aid")},
attribute bid {sql:variable("@bid")}
)
into (/root/user)[1] ' )
SELECT * FROM Users
/* ****************插入节点注释**************************** */
-- -insert 注释
UPDATE Users SET UserInfo.modify( ' insert <!-- 注释 -->
before (/root/user/userid[1])[1] ' )
SELECT * FROM Users
/* ****************插入处理指令**************************** */
UPDATE Users SET UserInfo.modify( ' insert <?Program = "A.exe" ?>
before (/root)[1] ' )
SELECT * FROM Users
/* ****************插入CDATA**************************** */
UPDATE Users SET UserInfo.modify( ' insert 北京 or cdata]]>
after (/root/user)[1] ' )
SELECT * FROM Users
/* ****************插入文本**************************** */
UPDATE Users SET UserInfo.modify( ' insert text{"插入文本"} as first
into (/root/user)[1] ' )
SELECT * FROM Users
/* ****************根据 if 条件语句进行插入**************************** */
-- -判断属性值
UPDATE Users SET UserInfo.modify( ' insert if(/root/user[@ID=1]) then (888888)
else (66666)
into (/root/user)[1] ' )
SELECT * FROM Users
-- --判断节点Value
UPDATE Users SET UserInfo.modify( ' insert if(/root/user[firstName="steven1"]) then (1111)
else (2222)
into (/root/user)[1] ' )
SELECT * FROM Users
-- --判断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
/* ****************将节点插入类型化的 xml 列中**************************** */
UPDATE Users SET UserInfo.modify( '
declare namespace UI="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/users";
insert
steven2
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] ' )
SELECT * FROM Users
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/16436858/viewspace-623444/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/16436858/viewspace-623444/