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>
        <userid>1</userid>
        <userName>test1</userName>
    </user>
    </root>'
    INSERT INTO Users(UserInfo)VALUES(@xml)

 
    --select * from Users
    --添加address节点,默认添加在所有节点之后
    UPDATE Users SET UserInfo.modify('insert <address>shanghai</address> 
     into (/root/user)[1]')  

    SELECT  * FROM Users

    --添加address节点,节点顺序为第一个
    UPDATE Users SET UserInfo.modify('insert <address>shanghai</address> as first
     into (/root/user)[1]')  

    SELECT  * FROM Users

    --添加address节点,节点顺序为最后一个
    UPDATE Users SET UserInfo.modify('insert <address>shanghai</address> as last
     into (/root/user)[1]')  

    SELECT  * FROM Users

    --添加address节点到某个节点之后
    UPDATE Users SET UserInfo.modify('insert <address>shanghai</address> 
     after (/root/user/userid)[1]')  

    --添加address节点到某个节点之前
    UPDATE Users SET UserInfo.modify('insert <address>shanghai</address> 
     before (/root/user/userid)[1]')  

    SELECT  * FROM Users

---注：[1] 代表第一个item


/*****************插入多节点*****************************/    


    --添加firstName and LastName,以，分割

    UPDATE Users SET UserInfo.modify('insert (<firstName>steven</firstName>,
                                        <lastName>shi</lastName>) into (/root/user)[1]')  
    
    SELECT  * FROM Users


/*****************插入节点属性*****************************/    
 
    ---在USER 节点插入ID 属性，并且值=1
    UPDATE Users SET UserInfo.modify('insert attribute ID {"1"}
                                        into (/root/user)[1]')  
    SELECT  * FROM Users


    ---在USER 节点插入ID 属性，值为一个变量，这个应该更常用
    declare @uid float
    set @uid=0.5
    UPDATE Users SET UserInfo.modify('insert attribute UID {sql:variable("@Uid")}
                                         into (/root/user)[1]')  
    SELECT  * FROM Users


    ---如果插入多个属性，需要用，分割
    declare @aid float,@bid float
    set @aid=0.5
    set @bid=0.6
    UPDATE Users SET UserInfo.modify('insert (attribute aid {sql:variable("@aid")},
                                              attribute bid {sql:variable("@bid")}
                                                )
                                     into (/root/user)[1]')  
    SELECT  * FROM Users



/*****************插入节点注释*****************************/    
 
    ---insert 注释
 
    UPDATE Users SET UserInfo.modify('insert  <!-- 注释 -->
                                     before (/root/user/userid[1])[1]')  
    SELECT  * FROM Users

 
/*****************插入处理指令*****************************/    
 
    UPDATE Users SET UserInfo.modify('insert <?Program = "A.exe" ?>   
                                     before (/root)[1]')  
    SELECT  * FROM Users


 /*****************插入CDATA*****************************/    
 

    UPDATE Users SET UserInfo.modify('insert <C><![CDATA[<city>北京</city> or cdata]]> </C> 
                                     after (/root/user)[1]')  
    SELECT  * FROM Users

 

 /*****************插入文本*****************************/    
 

    UPDATE Users SET UserInfo.modify('insert  text{"插入文本"} as first
                                     into (/root/user)[1]')  
    SELECT  * FROM Users

 
 

 /*****************根据 if 条件语句进行插入*****************************/    
 
    ---判断属性值
    UPDATE Users SET UserInfo.modify('insert  if(/root/user[@ID=1]) then (<tel>888888</tel>)
                                        else (<qq>66666</qq>)
                                     into (/root/user)[1]')  
    SELECT  * FROM Users

    ----判断节点Value
    UPDATE Users SET UserInfo.modify('insert  if(/root/user[firstName="steven1"]) then (<tel>1111</tel>)
                                        else (<qq>2222</qq>)
                                     into (/root/user)[1]')  
    SELECT  * FROM Users

 

    ----判断user 节点数是否小于等于10
    UPDATE Users SET UserInfo.modify('insert  if (count(/root/user)<=10) then element user { "This is a new user" }
                                        else () as last
                                     into (/root)[1]')  
    SELECT  * FROM Users

 


 /*****************将节点插入类型化的 xml 列中*****************************/  

DECLARE @xml XML
    SET @xml='<root xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/users">
    <user>
        <userid>1</userid>
        <userName>test1</userName>
    </user>
    </root>'
    INSERT INTO Users(UserInfo)VALUES(@xml)
  
     
     UPDATE  Users  SET  UserInfo.modify( '  
                declare namespace UI="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/users";
                 insert <UI:user>
                            <UI:firstName>steven2</UI:firstName>
                        </UI:user> as first
                 into (/UI:root)[1] ' )  
     SELECT    *   FROM  Users

 

     UPDATE  Users  SET  UserInfo.modify( '  
                declare namespace UI="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/users";
                 insert attribute ID { "55" }      
                         
                 into (/UI:root/UI:user)[1] ' )  
     SELECT    *   FROM  Users