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

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

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

#|: | 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