SQL XML 类型插入操作
--创建测试数据库
CREATE DATABASE mytest;
GO
USE mytest;
GO
--创建测试表
CREATE TABLE Users
(
ID INT IDENTITY(1,1),
UserInfo XML
)
/*****************插入单节点*****************************/
---插入测试数据
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)
--select * from Users
--添加address节点,默认添加在所有节点之后
UPDATE Users SET UserInfo.modify('insert <address>shanghai</address>
into (/root/user)[1]')
SELECT * FROM Users
--添加address节点,节点顺序为第一个
UPDATE Users SET UserInfo.modify('insert <address>shanghai</address> as first
into (/root/user)[1]')
SELECT * FROM Users
--添加address节点,节点顺序为最后一个
UPDATE Users SET UserInfo.modify('insert <address>shanghai</address> as last
into (/root/user)[1]')
SELECT * FROM Users
--添加address节点到某个节点之后
UPDATE Users SET UserInfo.modify('insert <address>shanghai</address>
after (/root/user/userid)[1]')
--添加address节点到某个节点之前
UPDATE Users SET UserInfo.modify('insert <address>shanghai</address>
before (/root/user/userid)[1]')
SELECT * FROM Users
---注：[1] 代表第一个item
/*****************插入多节点*****************************/
--添加firstName and LastName,以，分割
UPDATE Users SET UserInfo.modify('insert (<firstName>steven</firstName>,
<lastName>shi</lastName>) 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 <C><![CDATA[<city>北京</city> or cdata]]> </C>
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 (<tel>888888</tel>)
else (<qq>66666</qq>)
into (/root/user)[1]')
SELECT * FROM Users
----判断节点Value
UPDATE Users SET UserInfo.modify('insert if(/root/user[firstName="steven1"]) then (<tel>1111</tel>)
else (<qq>2222</qq>)
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 <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]')
SELECT * FROM Users
------------------------------------------更新---------------------------------------------------
declare @myxml xml
set @myxml='<?xml version="1.0" encoding="gb2312"?>
<root>
<item id="1">
</item>
</root>'
select @myxml
--1、插入item的第一个子元素
set @myxml.modify('insert <course1>sql server2005</course1> into (root/item)[1]') select @myxml
--2、插入item的第一个子元素，插入到同级子元素前面
set @myxml.modify('insert <sname> 王一诺</sname> as first into (root/item)[1]') select @myxml
--3、插入item的第一个子元素，插入到同级子元素后面
set @myxml.modify('insert <grade>82</grade> as last into (root/item)[1]') select @myxml
--4、插入第二个item节点
set @myxml.modify('insert <item id="2" year="2009"></item> into (/root)[1]') select @myxml
--或者
set @myxml.modify('insert <item id="2" year="2009"></item> after (/root/item)[1]')
--5、向第二个item节点插入多个元素
set @myxml.modify('insert (<sname>王海</sname>,<course1>sql server2005</course1>,<grade>75</grade>) into (/root/item)[2]')
--6、插入属性
set @myxml.modify('insert attribute year{"2009"} into (/root/item)[1]')
select @myxml
--7、插入多个属性
set @myxml.modify('insert (attribute depart{"计算机"},attribute class{"1班"}) into (/root/item)[1]')
--8、插入文本节点
set @myxml.modify('insert text{"成绩表"} as first into (/root)[1]')
--9、插入整个节点
set @myxml.modify('insert <item id="3" year="2009">
<sname>王若天</sname>
<course1>oracle</course1>
<grade>59</grade></item>
into (/root)[1]')
--10、插入处理指令
SET @myxml.modify('insert <?Program ="Instructions.exe" ?> before (/root)[1] ')
select @myxml
二、删除操作
declare @myxml xml
set @myxml='<?Program ="Instructions.exe" ?>
<root>成绩表--这是个文本节点
<item id="1" year="2009" depart="计算机" class="1班">
<sname> 王一诺</sname>
<course1>sql server2005</course1>
<grade>82</grade>
</item>
<item id="2" year="2009" class="1班">
<sname>王海</sname>
<course1>sql server2005</course1>
<grade>75</grade></item>
<item id="3" year="2009" class="1班">
<sname>王若天</sname>
<course1>oracle</course1>
<grade>59</grade>
</item>
</root>'
--1、删除所有指令
set @myxml.modify('delete //processing-instruction()')
select '删除所有指令'=@myxml
--2、删除文本节点
set @myxml.modify('delete /root/text()')
select '删除文本节点'=@myxml
--3、删除节点id为的属性
set @myxml.modify('delete /root/item[@id=1]/@class')
select '删除节点id为的属性'=@myxml
--4、删除第二个节点属性
set @myxml.modify('delete /root/item[2]/@class')
select '删除第二个节点属性'=@myxml
--5、删除节点属性
set @myxml.modify('delete /root/item/@class')
select '删除节点属性'=@myxml
--6、删除节点id为的元素
set @myxml.modify('delete /root/item[@id=1]/grade')
select '删除节点id为的元素'=@myxml
--7、删除第二个节点元素
set @myxml.modify('delete /root/item[2]/grade')
select '删除第二个节点元素'=@myxml
--8、删除元素
set @myxml.modify('delete /root/item/grade')
select '删除元素'=@myxml
--9、删除id为的节点
set @myxml.modify('delete /root/item[@id=2]')
select '删除id为的节点'=@myxml
--10、删除第二个的节点
set @myxml.modify('delete /root/item[2]')
select '删除id为的节点'=@myxml
--11、删除第二个节点
set @myxml.modify('delete /root/item')
select '删除节点'=@myxml
三、修改操作
--1、修改文本的值
set @myxml.modify('replace value of(/root/text())[1] with "1班成绩表"')
select @myxml
--2、修改元素的值
set @myxml.modify('replace value of(/root/item[@id=3]/grade/text())[1] with "60"')
select @myxml
--3、修改属性的值
set @myxml.modify('replace value of(/root/item[@id=1]/@class)[1] with "2班"')
select @myxml
浙公网安备 33010602011771号