XML操作很少用到，最近查询个语句，还得到官网查看用法。所以按照官方把相关的DML都操作一遍，作为记录！~
— 测试数据 — DROP TABLE #XMLTab CREATE TABLE #XMLTab(colxml XML) GO INSERT INTO #XMLTab SELECT N' <Record dt="2015-05-05"><Exception type="RING_BUFFER_EXCEPTION"><Task address="0x0062B8E8" /><Error>9003</Error><Severity>error</Severity> </Exception><Exception type="BUFFER_EXCEPTION"><Task address="0xC452BB39" /><Error>8008</Error><Severity>true</Severity><Other>00</Other> </Exception> </Record>' GO INSERT INTO #XMLTab SELECT N' <p1:Record dt="2015-05-06" xmlns:p1=""><p1:Exception type="RING_BUFFER_EXCEPTION"><p1:Task address="0x0062B8E8" /><p1:Error>9003</p1:Error><p1:Severity>error</p1:Severity> </p1:Exception><p1:Exception type="BUFFER_EXCEPTION"><p1:Task address="0xC452BB39" /><p1:Error>8008</p1:Error><p1:Severity>true</p1:Severity> <p1:Other>00</p1:Other> </p1:Exception> </p1:Record>' GO
‘XML 数据修改语言 (XML DML)’
–节点插入
last
delete Expression
–在文档中更新节点的值
replace value
–在根路径"/Record/"的第一个"/Exception"下,添加删除一个元素"<Address>".还可以使用{as first | as last}指定位置UPDATE #XMLTab SET colxml.modify('insert <Address>insert test 1</Address> into (/Record/Exception)[1]');UPDATE #XMLTab SET colxml.modify('insert <Address>insert test 1</Address> 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"下,添加删除一个元素"<Address>"UPDATE #XMLTab SET colxml.modify('insert <Address>insert test 1</Address> 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"下的元素"<Address>"UPDATE #XMLTab SET colxml.modify('delete /Record/Exception/Address[1]');UPDATE #XMLTab SET colxml.modify('delete /Record/Exception/Address');各种路径表达式都指定“[1]”或者“[2]”等,要求每次只返回单个目标。
在重新删除创建测试表
–插入一个元素变量(连续执行4次,方便下面测试)DECLARE @newFeatures xml;SET @newFeatures = N'<Address></Address>'UPDATE #XMLTab SET colxml.modify('insert sql:variable("@newFeatures") into (/Record/Exception)[1]');
–插入一个属性到元素"<Address />"(接上步操作)(多个元素相同,以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]');
–插入一个属性到元素"<Address />",其中条件为[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 FLOATSET @Hrs =0.5UPDATE #XMLTab SET colxml.modify('insert attribute attrName {sql:variable("@Hrs")} into (/Record/Exception/Task)[1]');
–插入多个属性值UPDATE #XMLTab SET colxml.modify('insert (attribute attrName2 {"0.5" },attribute attrName3 {".2"} )into (/Record/Exception/Task)[1]');
–插入注释元素(在第3个Address后插入)UPDATE #XMLTab SET colxml.modify('insert <!– some comment –> after (/Record/Exception/Address[3])[1]');
–使用"before"在跟目录前(头部)插入处理指令UPDATE #XMLTab SET colxml.modify('insert <?Program = "Instructions.exe" ?> before (/Record)[1]');
–还可以利用"after" 和 "before" 的方法插入一个元素UPDATE #XMLTab SET colxml.modify('insert <Address></Address> after (/Record/Exception/Address[1])[1]');UPDATE #XMLTab SET colxml.modify('insert <Address></Address> 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('insertif (/Record/Exception/Address[3][@attrName="attributeB"])then attribute attrName {"10"}else attribute attrName {"50"}as first into (/Record/Exception/Address[3])[1] ');
–将元素"<Error>9003</Error>"中的文本值"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" )');
但要相信真诚的爱情，对爱情永远怀有单纯的向往。
