SQL XML类型的删除
2011-09-06 09:32 Eric.Hu 阅读(410) 评论(0) 编辑 收藏 举报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>
<user id="1">
text
<userid>1</userid>
<userName>test1</userName>
</user>
</root>'
INSERT INTO Users(UserInfo)VALUES(@xml)
--DELETE Users
UPDATE Users SET UserInfo.modify('delete /root/user/@id')
select * from Users
/*****************删除节点*****************************/
UPDATE Users SET UserInfo.modify('delete /root/user/userid[1]')
select * from Users
/*****************删除节点中的文本*****************************/
UPDATE Users SET UserInfo.modify('delete /root/user/text()')
select * from Users
/*****************删除第二个节点(userName)*****************************/
UPDATE Users SET UserInfo.modify('delete /root/user/*[2]')
select * from Users
/*****************根据属性值删除节点*****************************/
---插入测试数据
DECLARE @xml XML
SET @xml='<root>
<user id="1" a="a">
<userid>1</userid>
<userName>test1</userName>
</user>
<user id="2" b="b">
<userid>1</userid>
<userName>test1</userName>
</user>
</root>'
INSERT INTO Users(UserInfo)VALUES(@xml)
---删除属性
UPDATE Users SET UserInfo.modify('delete /root/user[@id=1]/@a')
select * from Users
---删除节点
UPDATE Users SET UserInfo.modify('delete /root/user[@id=1]/userName')
CREATE DATABASE mytest;
GO
USE mytest;
GO
--创建测试表
CREATE TABLE Users
(
ID INT IDENTITY(1,1),
UserInfo XML
)
/*****************删除节点属性*****************************/
---插入测试数据
DECLARE @xml XML
SET @xml='<root>
<user id="1">
text
<userid>1</userid>
<userName>test1</userName>
</user>
</root>'
INSERT INTO Users(UserInfo)VALUES(@xml)
--DELETE Users
UPDATE Users SET UserInfo.modify('delete /root/user/@id')
select * from Users
/*****************删除节点*****************************/
UPDATE Users SET UserInfo.modify('delete /root/user/userid[1]')
select * from Users
/*****************删除节点中的文本*****************************/
UPDATE Users SET UserInfo.modify('delete /root/user/text()')
select * from Users
/*****************删除第二个节点(userName)*****************************/
UPDATE Users SET UserInfo.modify('delete /root/user/*[2]')
select * from Users
/*****************根据属性值删除节点*****************************/
---插入测试数据
DECLARE @xml XML
SET @xml='<root>
<user id="1" a="a">
<userid>1</userid>
<userName>test1</userName>
</user>
<user id="2" b="b">
<userid>1</userid>
<userName>test1</userName>
</user>
</root>'
INSERT INTO Users(UserInfo)VALUES(@xml)
---删除属性
UPDATE Users SET UserInfo.modify('delete /root/user[@id=1]/@a')
select * from Users
---删除节点
UPDATE Users SET UserInfo.modify('delete /root/user[@id=1]/userName')
select * from Users
着意耕耘,自有收获。