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

 
    --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 列中*****************************/    
     
    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

--更新---------------------------------------------------
一,创建有XML类型字段的表

       Create table xml_test (xmlinfo xml)

二,导入数据

       Insert into xml_test values(‘<?xml version="1.0" ?>

<Avatar Name="xiawa" Sex="female">

       <BasicPart>

              <Part ID="00001" />

              <Part ID="00002" />

              <Part ID="00003" />

              <Part ID="00004" />

              <Part ID="00005" />

              <Part ID="00006" />

       </BasicPart>

      

   <BasicSkin ID="00001" />

       <PartList>

              <Part ID="00001" File="xiawa_partbasic_head.mesh" Part="head" />

              <Part ID="00002" File="xiawa_part_hair_0001.mesh" Part="hair" />

              <Part ID="00003" File="xiawa_part_coat_0001.mesh" Part="coat" />

              <Part ID="00004" File="xiawa_part_pants_0001.mesh" Part="pants" />

              <Part ID="00005" File="xiawa_partbasic_hands.mesh" Part="hands" />

              <Part ID="00006" File="xiawa_part_shoes_0001.mesh" Part="shoes" />

              <Part ID="00007" File="xiawa_part_hair_0003.mesh" Part="hair" />

              <Part ID="00008" File="xiawa_part_coat_0003.mesh" Part="coat" />

              <Part ID="00009" File="xiawa_part_pants_0003.mesh" Part="pants" />

              <Part ID="00010" File="xiawa_part_shoes_0003.mesh" Part="shoes" />

       </PartList>

    </BasicPart>

</Avatar>

’)

三,更新XML节点

Update xml_test set xmlinfo.modify(‘replace values of /Avatar/partList/Part[@ID=”00001”]/@File)[1] with "This is test”’)

出处：http://runboliu.blog.163.com/blog/static/13459317201010306552246/