SQL Server XML基础学习之<7>--XML modify() 方法对 XML 数据中插入、更新或删除

/*------------------------------------------------------------------------------+ 
#| = : = : = : = : = : = : = : = : = : = : = : = : = : = : = : = : = : = : = :  | 
#|{>/------------------------------------------------------------------------\<}| 
#|: | Author     :    小爱                                                      | 
#|: | Description:    XML modify() 方法                                         |
#|: | SQL Version:    适用于 SQL 2005, SQL 2008 , SQL 2012                      | 
#|: | Copyright  :    转载请注明出处。更多请访问：http://blog.youkuaiyun.com/beirut   |
#|: | Create Date:    2012-11-22                                                |
#|: | About Me   :    一个菜鸟dba                                               |
#|{>\------------------------------------------------------------------------/<}| 
#| = : = : = : = : = : = : = : = : = : = : = : = : = : = : = : = : = : = : = :  | 
#+-----------------------------------------------------------------------------*/ 


/*------------------------------------------------------------------------------------------------------------------   
本章讲述 modify() 方法 , modify() 方法是使用 XML DML 语句在 XML 数据中插入、更新或删除节点。
语法：modify (XML_DML) XML_DML 是 XML 数据操作语言 (DML) 中的字符串。
XML DML 将下列区分大小写的关键字添加到 XQuery 中：
insert --添加
delete --删除
replace value of --更新
------------------------------------------------------------------------------------------------------------------*/

DECLARE @x XML 
SET @x='
<root>
  <rogue id="001">
    <hobo id="1">
      <name>彪</name>
      <type>流氓</type>
    </hobo>
  </rogue>
</root>'

------------------------------------------------------插入------------------------------------------------------------
--在 hobo 节点下插入 一个新节点
SET @x.modify('
insert <nickname>阿彪</nickname>
as first
into (/root/rogue/hobo)[1]
');
SELECT @x
--注释：如果某节点下面有多个节点的时候可以使用 as first 或 as last 来指定所需的新节点添加位置。

---在指定的 hobo 节点下，插入同一级节点
SET @x.modify('
insert <id>1</id>
before (/root/rogue/hobo)[1]
');
SELECT @x
--注释：是用 before 或者 after 关键字代替 into 在指定节点的 前面 或者 后面 插入同级节点
--after 关键字 和 before 关键字不能用于插入属性

--插入属性 一次插入多个属性值/使用变量/属性定位
DECLARE @a INT =5
SET @x.modify('           
insert (
            attribute a {sql:variable("@a")},
            attribute b {".5"}          
 
		)     
into   (/root/rogue/hobo[@id=1])[1]
');           
SELECT @x;           
GO
----------------------------------------------------删除------------------------------------------------------------
DECLARE @x XML 
SET @x='
<?Instructions for=TheWC.exe ?> 
<root>
  <rogue id="001">
    <hobo id="1" weight="80" age="25">
      <name>彪</name>
      <type>流氓</type>
      <nickname>阿彪</nickname>
      <notes />
    </hobo>
    <page_hotspots />
  </rogue>
  <notes />
</root>'
-- 删除属性
SET @x.modify('
  delete /root/rogue/hobo/@id
')
SELECT @x

-- 删除节点
SET @x.modify('
  delete /root/rogue/hobo/name[1]
')
SELECT @x

-- 删除节点内容
SET @x.modify('
  delete /root/rogue/hobo/type/text()
')
SELECT @x

-- 删除所有处理指令
SET @x.modify('
  delete //processing-instruction()
')
SELECT @x

-- 删除所有的内容为空的节点
SET @x.modify('
delete //*[empty(./*)]
')

SELECT @x

----------------------------------------------------修改------------------------------------------------------------

DECLARE @x XML 
SET @x='
<root>
  <rogue id="001">
    <hobo id="1" weight="80" age="25">
      <name>彪</name>
      <type>流氓</type>
      <nickname>阿彪</nickname>
    </hobo>
  </rogue>
</root>'
-- 在修改语法当中 每次只能修改一个单个节点，不能批量修改或者一次修改多个值，这一点是比较郁闷的

-- 修改节点值
SET @x.modify('
  replace value of (/root/rogue/hobo/name/text())[1]
  with     "光辉"
')
SELECT @x

-- 修改属性值
SET @x.modify('
  replace value of (/root/rogue/hobo/@weight)[1]
  with     "70"
')
SELECT @x

-- 使用 if 表达式
SET @x.modify('
  replace value of (/root/rogue/hobo/@age)[1]
  with (
       if (count(/root/rogue/hobo/*) > 2) then
         "30"
       else
         "10"
      )
')
SELECT @x

------------------------------------------------------一些示例------------------------------------------------------------

-- 1
DECLARE @x1 XML
SELECT @x1 = '
<rogue>
    <hobo name="彪"/>
    <hobo name="光辉"/>
    <hobo name="小D"/>
    <hobo name="野子"/>
</rogue>'

------------------------------------------------------------
-- 把  小D  移动到 彪 前面
------------------------------------------------------------
SET @x1.modify('
    insert /rogue/hobo[@name="小D"] 
    before (/rogue/hobo[@name="彪"])[1]
    ')

SET @x1.modify ('
        delete (/rogue/hobo[@name="小D"])[2]
    ')

SELECT @x1

---------- --------------------------------------------------
-- 把 光辉 移动到 野子 后面
------------------------------------------------------------
SET @x1.modify('
    insert /rogue/hobo[@name="光辉"] 
    after (/rogue/hobo[@name="野子"])[1]
    ')

SET @x1.modify ('
        delete (/rogue/hobo[@name="光辉"])[1]
    ')

SELECT @x1

------------------------------------------------------------
-- 把 野子 向前移动一级
------------------------------------------------------------
SET @x1.modify('
    insert /rogue/hobo[@name="野子"] 
    before (/rogue/hobo[. << (/rogue/hobo[@name="野子"])[1]])[last()]
    ')

SET @x1.modify ('
        delete /rogue/hobo[@name="野子"] 
        [. is (/rogue/hobo[@name="野子"])[last()]]
    ')

SELECT @x1
------------------------------------------------------------
-- 把 彪  向后 移一级
------------------------------------------------------------
set @x1.modify('
    insert /rogue/hobo[@name="彪"] 
    before (/rogue/hobo[. >> (/rogue/hobo[@name="彪"])[1]])[2]
    ')
SELECT @x1
SET @x1.modify ('
        delete (/rogue/hobo[@name="彪"])[1]
    ')

SELECT @x1

------------------------------------------------------------
-- 使用变量修改属性名称
------------------------------------------------------------
DECLARE @x2 XML
SELECT @x2 = '
<Employees>
  <Employee FirstName="Jacob" MiddleName="V" LastName="Sebastian"/>
</Employees>'

DECLARE @var VARCHAR(20)
DECLARE @val VARCHAR(20)

SELECT @var = 'MiddleName'
SELECT @val = 'J'


SET @x2.modify('
    replace value of (
        /Employees/Employee/@*[local-name()=sql:variable("@var")]
    )[1]
    with sql:variable("@val")
')

select @x2