SqlServer XML数据类型DML相关操作（图文结合）

原创

JeesonHuang 2023-01-11 02:21:41 博主文章分类：SQLServer ©著作权

文章标签 xml sql microsoft 文章分类 运维

©著作权归作者所有：来自51CTO博客作者JeesonHuang的原创作品，请联系作者获取转载授权，否则将追究法律责任

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="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription">  
  <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)'

--节点插入

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

--删除XML实例的节点

delete Expression

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

replace value of Expression1withExpression2

--  在根路径"/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]');

SqlServer XML数据类型DML相关操作（图文结合）_xml

--  在根路径"/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]');

SqlServer XML数据类型DML相关操作（图文结合）_sql_02

--  插入一个属性到元素"<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]');

SqlServer XML数据类型DML相关操作（图文结合）_xml_03

--  插入一个属性到元素"<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"

SqlServer XML数据类型DML相关操作（图文结合）_sql_04

--  删除路径"/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');

SqlServer XML数据类型DML相关操作（图文结合）_xml_05

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

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

SqlServer XML数据类型DML相关操作（图文结合）_sql_06

--  插入多个属性值
UPDATE #XMLTab SET colxml.modify('
insert (
  attribute attrName2 {"0.5" },
  attribute attrName3 {".2"}  
  )
into (/Record/Exception/Task)[1]');

SqlServer XML数据类型DML相关操作（图文结合）_xml_07

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

SqlServer XML数据类型DML相关操作（图文结合）_sql_08

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

SqlServer XML数据类型DML相关操作（图文结合）_sql_09

--  还可以利用"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]');

SqlServer XML数据类型DML相关操作（图文结合）_microsoft_10

--  添加删除文本(添加文本后,该元素格式显示成一行)
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()');

--  以下为添加后的格式

SqlServer XML数据类型DML相关操作（图文结合）_microsoft_11

--  按条件更改增加属性:
--如果"/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] ');

SqlServer XML数据类型DML相关操作（图文结合）_microsoft_12

--  将元素"<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" ');

SqlServer XML数据类型DML相关操作（图文结合）_microsoft_13

--如果/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"
  )');

SqlServer XML数据类型DML相关操作（图文结合）_sql_14

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

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

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

SqlServer XML数据类型DML相关操作（图文结合）_sql_15

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

SqlServer XML数据类型DML相关操作（图文结合）_microsoft_16

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

--  可做where 条件判断某个值是否存在
SELECT colxml  
,colxml.exist('/Record[@dt="2015-05-05"]')
,colxml.exist('/Record/Exception[@type="RING_BUFFER_EXCEPTION"]')
,colxml.exist('/Record/Exception/Task[@address="0x0062B8E8"]')
FROM #XMLTab

--  查看节点"/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

SqlServer XML数据类型DML相关操作（图文结合）_sql_17

--  文本判断："/Record/Exception/Error"中的文本,只要其中一个符合就返回"1"
SELECT colxml 
,colxml.exist('/Record/Exception/Error[(text()[1] cast as xs:string ?) = xs:string("9003") ]')
,colxml.exist('/Record/Exception/Error[(text()[1] cast as xs:string ?) = xs:string("8008") ]')
,colxml.exist('/Record/Exception[1]/Error[(text()[1] cast as xs:string ?) = xs:string("9003") ]')
,colxml.exist('/Record/Exception[2]/Error[(text()[1] cast as xs:string ?) = xs:string("8008") ]')
FROM #XMLTab

SqlServer XML数据类型DML相关操作（图文结合）_sql_18

--  带前缀的查询方法
SELECT colxml 
,colxml.exist('
  declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
  /p1:Record/p1:Exception/p1:Error[(text()[1] cast as xs:string ?) = xs:string("9003") ]')
,colxml.exist('
  declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
  /p1:Record/p1:Exception/p1:Error[(text()[1] cast as xs:string ?) = xs:string("8008") ]')
,colxml.exist('
  declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
  /p1:Record/p1:Exception[1]/p1:Error[(text()[1] cast as xs:string ?) = xs:string("9003") ]')
,colxml.exist('
  declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
  /p1:Record/p1:Exception[2]/p1:Error[(text()[1] cast as xs:string ?) = xs:string("8008") ]')
FROM #XMLTab

--  还可以简写为:
WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription' AS p1)
SELECT colxml 
,colxml.exist('/p1:Record/p1:Exception/p1:Error[(text()[1] cast as xs:string ?) = xs:string("9003") ]')
,colxml.exist('/p1:Record/p1:Exception/p1:Error[(text()[1] cast as xs:string ?) = xs:string("8008") ]')
,colxml.exist('/p1:Record/p1:Exception[1]/p1:Error[(text()[1] cast as xs:string ?) = xs:string("9003") ]')
,colxml.exist('/p1:Record/p1:Exception[2]/p1:Error[(text()[1] cast as xs:string ?) = xs:string("8008") ]')
FROM #XMLTab

--  元素判断：元素中的其他元素是否存在.为1则是该节点中不包含的.
SELECT colxml 
,colxml.exist('/Record/Exception[1][not(Other)]') --1：元素"Exception[1]"不包含元素"Other"
,colxml.exist('/Record/Exception[2][not(Other)]') --0：元素"Exception[2]"包含元素"Other"
,colxml.exist('
  declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
  /p1:Record/p1:Exception[1][not(p1:Other)]')  --1：元素"p1:Exception[1]"不包含元素"p1:Other"
,colxml.exist('
  declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
  /p1:Record/p1:Exception[2][not(p1:Other)]')  --0：元素"p1:Exception[2]"包含元素"p1:Other"
FROM #XMLTab

SqlServer XML数据类型DML相关操作（图文结合）_xml_19

--  使用"value"查询属性值和元素文本值
SELECT 
 colxml.value('(/Record/Exception/Task/@address)[1]','varchar(100)') AS [address1]
,colxml.value('(/Record/Exception[2]/Task/@address)[1]','varchar(100)') AS [address2]
,colxml.value('data(/Record/Exception[1]/Error)[1]','int') AS [Error1] 
,colxml.value('data(/Record/Exception[2]/Error)[1]','int') AS [Error2]  
,colxml.value('
  declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
  data(/p1:Record/p1:Exception[2]/p1:Error)[1]
  ','int') AS [p1:Error2] 
FROM #XMLTab

SqlServer XML数据类型DML相关操作（图文结合）_sql_20

--  使用"query"查询属性值
SELECT   
 CONVERT(NVARCHAR(100),colxml.query('data(/Record/@dt)')) AS [id]  
,CONVERT(NVARCHAR(100),colxml.query('data(/Record/Exception/@type)[1]')) AS [type1] 
,CONVERT(NVARCHAR(100),colxml.query('data(/Record/Exception/@type)[2]')) AS [type2]  
,CONVERT(NVARCHAR(100),colxml.query('data(/Record/Exception[2]/@type)[1]')) AS [type2]
,CONVERT(NVARCHAR(100),colxml.query('data(/Record/Exception/@type)')) AS [type1 type2]
FROM #XMLTab

SqlServer XML数据类型DML相关操作（图文结合）_xml_21

--  可以当做查询条件:
SELECT * FROM #XMLTab WHERE CONVERT(NVARCHAR(100),colxml.query('data(/Record/@dt)'))='2015-05-05'

--  使用"nodes"中"value"查询属性值和元素文本值
SELECT node.c1.query('data(/Record/@dt)')
,CONVERT(NVARCHAR(100),colxml.query('data(/Record/@dt)')) AS [id] 
,node.c1.value('(@dt)','VARCHAR(50)') AS [id] 
,node.c1.value('(@dt)[1]','VARCHAR(50)') AS [id] 
,node.c1.value('(Exception/@type)[1]','VARCHAR(50)') AS [type1]  
,node.c1.value('(Exception/Error)[1]','VARCHAR(50)') AS [Error1]  
FROM #XMLTab
CROSS APPLY colxml.nodes('/Record') as node(c1)

SqlServer XML数据类型DML相关操作（图文结合）_xml_22

另一个栗子：

CREATE TABLE #XMLTab(colxml XML)   
GO   
INSERT INTO #XMLTab     
SELECT N'    
<Records>    
  <Record Guid="7233036C-E4CD-4CAB-B6EB-268535BBCAFE"/>    
  <Record Guid="AB4CA16F-FB9F-4E26-8A87-B5536BA46411"/>   
  <Record Guid="2C282D02-F8AD-473F-9A25-619E061B9409"/>   
</Records>'    
GO   
select colxml,CONVERT(NVARCHAR(100),colxml.query('data(/Records/Record/@Guid)[1]'))
,replace(CONVERT(NVARCHAR(1000),colxml.query('data(/Records/Record/@Guid)')),' ',',')
from #XMLTab

好了！~21图，测试到这。

XML类型的DML操作开始并不好记，看来只有多写语句才能记得。而这只是XML相关操作中很小的一部分。XML还涉及有类型化和非类型化的XML、XML类型导入导出、XML索引（主索引/辅索引/全文索引）、FOR XML的使用、XML架构集合等。太多！~有待学习！