/*------------------------------------------------------------------------------+

#| = : = : = : = : = : = : = : = : = : = : = : = : = : = : = : = : = : = : = :  |

#|{>/------------------------------------------------------------------------\<}|

#|: | Author     :    小爱                                                      |

#|: | Description:    XML modify() 方法                                         |

#|: | SQL Version:    适用于 SQL 2005, SQL 2008 , SQL 2012                      |

#|: | Copyright  :    转载请注明出处。更多请访问：http://blog.csdn.net/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