SQL XML 类型replace 操作
有了前边两篇的Insert 和Delete 的学习基础,replace 的操作应该是容易的多了。
还是一起来看看Sample:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
--创建测试数据库
CREATE DATABASE mytest;
GO
USE mytest;
GO
--创建测试表
CREATE TABLE Users
(
ID INT IDENTITY(1,1),
UserInfo XML
)
/*****************XML 的 Replace 操作*****************************/
---插入测试数据
DECLARE @xml XML
SET @xml='<root>
<user id="1">
<userid>1</userid>
<userName>test1</userName>
<userName>test2</userName>
</user>
<user id="2">
<userid>1</userid>
<userName>test1</userName>
<userName>test2</userName>
</user>
</root>'
INSERT INTO Users(UserInfo)VALUES(@xml)
--DELETE Users
---- 替换节点中的Value
UPDATE Users SET UserInfo.modify('replace value of( /root/user/userName[2]/text())[1]
with " new userName "')
select * from Users
---- 替换节点的属性值
UPDATE Users SET UserInfo.modify('replace value of( /root/user/@id)[1]
with " 1000 "')
select * from Users
---- IF条件判断替换XML属性值
UPDATE Users SET UserInfo.modify('replace value of( /root/user/@id)[1]
with ( if (count(/root/user[1]/userName) > 1) then
"500"
else
"300"
)')
select * from Users
---- IF条件判断替换XML属性值 ,判断id=300
UPDATE Users SET UserInfo.modify('replace value of( /root/user/@id)[1]
with ( if ((/root/user/@id) = 2) then
"500"
else
"300"
)')
select * from Users