SQLXML的Replace操作SQL XML的Replace操作
--创建测试数据库
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>
replace into<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 dify('replace value of( /root/user/userName[2]/text())[1]
with " new userName "')
select*from Users
---- 替换节点的属性值
UPDATE Users dify('replace value of( /root/user/@id)[1]
with " 1000 "')
select*from Users
---- IF条件判断替换XML属性值
UPDATE Users dify('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 dify('replace value of( /root/user/@id)[1]    with ( if ((/root/user/@id) = 2) then
"500"
else
"300"
)')
select*from Users

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。