XML操作很少用到，最近查询个语句，还得到官网查看用法。所以按照官方把相关的DML都操作一遍，作为记录！~

--  测试数据  
--  DROP TABLE #XMLTab  
CREATE TABLE #XMLTab(colxml XML) 
GO 
INSERT INTO #XMLTab   
SELECT N'  
  
    
      
    9003  
    error
    
    
      
    8008  
    true
    00
    
'  
GO 
INSERT INTO #XMLTab   
SELECT N'  
  
    
      
    9003  
    error
    
    
      
    8008  
    true 
	00 
    
'  
GO 

'XML 数据修改语言 (XML DML)'

--节点插入

insert Expression1({asfirst | as last} into | after| before Expression2 )

--删除XML实例的节点

delete Expression

--在文档中更新节点的值

replace value of Expression1with Expression2

--	在根路径"/Record/"的第一个"/Exception"下,添加删除一个元素"
".还可以使用{as first | as last}指定位置
UPDATE #XMLTab SET colxml.modify('insert 
insert test 1
 into (/Record/Exception)[1]');
UPDATE #XMLTab SET colxml.modify('insert 
insert test 1
 as first into (/Record/Exception)[1]');

--	删除所有元素"Address"
UPDATE #XMLTab SET colxml.modify('delete /Record/Exception[1]/Address');
UPDATE #XMLTab SET colxml.modify('delete /Record/Exception[1]/Address[1]');

--	在根路径"/Record/"的第二个"/Exception"下,添加删除一个元素"
"
UPDATE #XMLTab SET colxml.modify('insert 
insert test 1
 into (/Record/Exception)[2]');

--	删除第二个节点"/Exception"下的所有元素"Address"
UPDATE #XMLTab SET colxml.modify('delete /Record/Exception[2]/Address');
UPDATE #XMLTab SET colxml.modify('delete /Record/Exception[2]/Address[1]');

--	删除"/Record/Exception/"下的第2个元素(即在Exception中第二行的删除)
UPDATE #XMLTab SET colxml.modify('delete /Record/Exception/*[2]')

--	删除所有路径"/Record/Exception"下的元素"
"
UPDATE #XMLTab SET colxml.modify('delete /Record/Exception/Address[1]');
UPDATE #XMLTab SET colxml.modify('delete /Record/Exception/Address');

在重新删除创建测试表

--	插入一个元素变量(连续执行4次,方便下面测试)
DECLARE @newFeatures xml;
SET @newFeatures = N'
'

UPDATE #XMLTab SET colxml.modify('insert sql:variable("@newFeatures") into (/Record/Exception)[1]');

--	插入一个属性到元素"
"(接上步操作)(多个元素相同,以Address[1]/[2]/[3]/[4]区分)
UPDATE #XMLTab SET colxml.modify('insert attribute attrName {"attributeA" } into (/Record/Exception/Address[1])[1]');
UPDATE #XMLTab SET colxml.modify('insert attribute attrName {"attributeB" } into (/Record/Exception/Address[2])[1]');
UPDATE #XMLTab SET colxml.modify('insert attribute attrName {"attributeA" } into (/Record/Exception/Address[3])[1]');
UPDATE #XMLTab SET colxml.modify('insert attribute attrName {"attributeB" } into (/Record/Exception/Address[4])[1]');

--	插入一个属性到元素"
",其中条件为[attrName="attributeB"],(每个元素以Address[1]/[2]/[3]/[4]区分)
UPDATE #XMLTab SET colxml.modify('
	insert attribute attrName2 {"0.5" } 
	into (/Record/Exception/Address[4][@attrName="attributeB"])[1]');--成功:第四个Address,attrName="attributeB"

UPDATE #XMLTab SET colxml.modify('
	insert attribute attrName3 {"0.5" } 
	into (/Record/Exception/Address[4][@attrName="attributeA"])[1]');--失败:第四个Address,attrName<>"attributeA"

--	删除路径"/Record/Exception/Address"中"Address"的属性"attrName"(所有)
UPDATE #XMLTab SET colxml.modify('delete /Record/Exception/Address/@attrName');
UPDATE #XMLTab SET colxml.modify('delete /Record/Exception/Address/@attrName2');

--	变量插入属性值
DECLARE @Hrs FLOAT        
SET @Hrs =0.5

UPDATE #XMLTab SET colxml.modify('insert attribute attrName {sql:variable("@Hrs")} into (/Record/Exception/Task)[1]');