SQL XML类型的删除

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')

select * from Users