SQL XML的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>
    <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