--	插入注释元素(在第3个Address后插入)
UPDATE #XMLTab SET colxml.modify('insert  after (/Record/Exception/Address[3])[1]');

--	使用"before"在跟目录前(头部)插入处理指令
UPDATE #XMLTab SET colxml.modify('insert  before (/Record)[1]');

--	还可以利用"after" 和 "before" 的方法插入一个元素
UPDATE #XMLTab SET colxml.modify('insert 
 after (/Record/Exception/Address[1])[1]');
UPDATE #XMLTab SET colxml.modify('insert 
 before (/Record/Exception/Address[1])[1]');

--	添加删除文本(添加文本后,该元素格式显示成一行)
UPDATE #XMLTab SET colxml.modify('insert text{"Product Catalog Description"} as first into (/Record/Exception)[1]');

--	删除后查看,格式正常
UPDATE #XMLTab SET colxml.modify('delete /Record/Exception/text()');

--	以下为添加后的格式

--	按条件更改增加属性:
--如果"/Record/Exception/Address[3]"的属性attrName="attributeB",则增加属性attrName="10",否则增加属性attrName="50"
UPDATE #XMLTab SET colxml.modify('
insert
if (/Record/Exception/Address[3][@attrName="attributeB"])
	then attribute attrName {"10"}
else 
	attribute attrName {"50"}
as first into (/Record/Exception/Address[3])[1] ');

--	将元素"9003"中的文本值"9003"改为"10000"（）
UPDATE #XMLTab SET colxml.modify('replace value of (/Record/Exception/Error[1]/text())[1] with "10000" ');

--	将元素"Task"的属性"address"的值改为"100"
UPDATE #XMLTab SET colxml.modify('replace value of (/Record/Exception/Task/@address)[1] with "100" ');
UPDATE #XMLTab SET colxml.modify('replace value of (/Record/Exception/Task[1]/@address)[2] with "999" ');

--如果/Record/路径下第一个Exception中，元素Address个数大于3，则更新Exception[1]属性type为"10.0"否则为"20.0"
UPDATE #XMLTab SET colxml.modify('
  replace value of (/Record/Exception[1]/@type)[1]
  with (
       if (count(/Record/Exception[1]/Address) > 3) then "10.0"
       else "20.0"
  )');

-------------------------------------------------------------------------------------
以下为XML相关查询
-------------------------------------------------------------------------------------

--	查询某个元素中的所有所有子项
SELECT colxml.query('/Record/Exception') FROM #XMLTab

SELECT colxml.query('//Record/Exception/Address') FROM #XMLTab
--图为第二个查询语句的结果

--	元素带前缀的查询.之前在临时表插入的第二行数据,现在有用了!~
SELECT colxml.query('
declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
/p1:Record/p1:Exception') 
FROM #XMLTab

--	条件查询用,如果在 exist() 方法中指定 true() 或 false() 函数，则总是返回 1
SELECT colxml FROM #XMLTab WHERE colxml.exist('true()')=1
SELECT colxml FROM #XMLTab WHERE colxml.exist('false()')=1

--	查看节点"/Record"的属性"@dt"日期是否为"2015-05-05"
--	元素中的属性转换为日志比较(也可以作为查询条件的判断)
--	参考构造函数:https://msdn.microsoft.com/zh-cn/library/ms189547.aspx
SELECT colxml 
,colxml.exist('/Record[(@dt cast as xs:date?) eq xs:date("2015-05-05")]')
,colxml.exist('/Record[(@dt cast as xs:date?) eq xs:date("2015-05-10")]')
FROM #XMLTab