学步园

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