SQL Server中的XML数据类型详解

发布时间：2023-12-26 16:30

目录
  • 一、创建测试数据，指定字段数据类型为XML
    • 1、创建表
    • 2、插入测试数据
    • 3、插入XML文件数据
    • 4、创建索引
  • 二、查询XML数据
    • 1、query(XPath条件):返回xml 类型的节点内容
    • 2、value(XPath条件,数据类型)：返回标量值
    • 3、exist(XPath条件)：返回是否存在
    • 4、nodes(XPath条件)：返回由符合条件的节点组成的多行一列的结果表
  • 三、modify()：修改XML修改XML字段
    • 1、modify（insert）增加节点
    • 2、modify（delete ）删除节点
    • 3、modify（replace value of） --更新单个节点
  • 四、for xml子句:表数据自动生成xml格式
    • 1、raw模式
    • 2、auto模式：表名作为元素名、生成简单的层次结构
    • 3、path模式

SQL Server从2005起开始支持xml类型，这个数据类型对于后期的改变非常有用。一对多的关系在后期变成了多对多的关系，XML类型就是一个不错的选择。

一、创建测试数据，指定字段数据类型为XML

1、创建表

--创建表，包含Xml类型列
　　CREATE TABLE Person
　　(
    　　Id int,
    　　Info xml
　　)

2、插入测试数据

--插入3条测试数据
　　INSERT Person VALUES(1,\'1刘备\')
　　INSERT Person VALUES(2,\'2关羽\')
　　INSERT Person VALUES(3,\'3张飞\')

\"\"

3、插入XML文件数据

insert Person values(4,select * from  openrowset(bulk \'G:\\Document\\XMLDocument\\x3.xml\',single_clob) as x)

4、创建索引

--XML“主”索引  
create primary xml index IX_Person_Info
    on Person ( Info );

--XML“路径”辅助索引  
create xml index IX_Person_Info_Path
    on Person ( Info )
    using xml index IX_Person_Info for path;

--XML“属性”辅助索引  
create xml index IX_Person_Info_Property
    on Person ( Info )
    using xml index IX_Person_Info for property;

--XML“内容”辅助索引  
create xml index IX_Person_Info_value
    on Person ( Info )
    using xml index IX_Person_Info for value;

二、查询XML数据

T-SQL 支持用于查询  XML 数据类型的 XQuery 语言。

XQuery 基于现有的 XPath 查询语言，并支持更好的迭代、更好的排序结果以及构造必需的  XML 的功能。

1、query(XPath条件):返回xml 类型的节点内容

--查询节点内容query()方法
SELECT Id,Info.query(\'(/Person/Name)[1]\') FROM Person WHERE ID = 2

\"\"

复杂查询

declare @myxml xml
set @myxml=\'
              
                  王五
                  18
                  
湖南

              
              
                  李一
                  20
                  
湖北

              
            \'

select @myxml.query(\'
                for $ss in /people/student
                where $ss/Age[text()]<22
                return     element Res
                {    
                    (attribute age{data($ss/Age[text()[1]])})
                }\')

结果为:

一个完整实例：

declare @x xml;

set @x = \'

  
    
      彪
      阿彪
      流氓
    
  
  
    
      光辉
      二辉
      流氓
    
  
  
    
      小德
      小D
      臭流氓
    
  
\';

--1、取root的所有子节点
select @x.query(\'root\'), @x.query(\'/root\'), @x.query(\'.\');

--/*注释：
--    这里实际上是取所有节点,root 必须是最高级节点名称，当换成任意子节点都是取不到值的
--*/

--2、取 student  的所有子节点,不管 student  在文档中的位置。
select @x.query(\'//student \');

--3、取people下 所有  name
select @x.query(\'//people//name\');

--4、取属性为id 的所有节点
select @x.query(\'//student [@id]\');

/*注释：
    XQuery不支持直接顶级 attribute 节点，必须附带上对节点的查找
    属性必须要加[]
*/
--5、选取属于 root 子元素的第一个 people 元素。
select @x.query(\'/root/people[1]\');

--6、选取属于 root 子元素的最后一个 people 元素。
select @x.query(\'/root/people[last()]\');

--7、选取属于 root 子元素的倒数第二个 people 元素。
select @x.query(\'/root/people[last()-1]\');

--8、选取最前面的两个属于 root 元素的子元素的 people 元素。
select @x.query(\'/root/people[position()<3]\');

--9、选取 root 元素的所有 student  元素，且其中的属性 id 的值须大于 1。
select @x.query(\'/root//student [@id>1]\');

----10、 root 元素的所有 student  元素，且其中的属性 id 的值须大于 1 并且子节点 name 的值为 光辉 的。
select @x.query(\'/root/people[./student [@id>1 and name=\"光辉\"]]\');

--11、选取 root 子元素的所有 people 元素，且 属性id 的值须大于 为001 子元素student  属性 id 的值为 1的
select @x.query(\'/root/people[@id=\"001\" and ./student [@id=1]]\');

--12、if then else 表达式  
select @x.query(\'
if ( 1=2 ) then
  /root/people[@id=\"001\"]
else
  /root/people[@id=\"002\"]
\');

--13、路径表达式步骤中的谓词 
select @x.query(\'/root/people[1]/student /name\'); --选择第一个 /root/people 节点下的所有  元素。

select @x.query(\'/root/people/student [1]/name\'); --选择 /root/people/student  节点下的所有  元素。

select @x.query(\'/root/people/student /name[1]\'); --选择 /root/people/student  节点下的所有第一个  元素。

select @x.query(\'(/root/people/student /name)[1]\');

--选择 /root/people/student  节点下的第一个  元素。

--14、使用聚合函数
select @x.query(\'count(/root/people/student /name)\'), @x.query(\'count(/root/people/student /name[1])\');

--15、FLWOR 迭代语法。FLWOR 是 for、let、where、order by 和 return 的缩写词。
--1
select @x.query(\'

 { for $i in /root/people/student /name[1]
    return string($i)
 }
\');
--彪 光辉 小德

--2
select @x.query(\'
   for $Loc in /root/people/student ,
       $FirstStep in $Loc/name[1]
   return 
       string($FirstStep)
\');
--彪 光辉 小德

--3
select @x.query(\'
   for $i in /root/people/student 
   order by $i/@id descending
   return string($i/name[1])
\');
--小德 光辉 彪

--4
select @x.query(\'
   for $i in /root/people/student 
   order by local-name($i) 
   return string($i/name[1])
\');
--彪 光辉 小德

2、value(XPath条件,数据类型)：返回标量值

该方法对xml执行XQuery查询,返回SQL类型的标量值。xpath条件结果必须唯一。

SELECT Id,Info.value(\'(/Person/Name)[1]\',\'VARCHAR(50)\') FROM Person WHERE ID = 2
SELECT * FROM Person WHERE Info.value(\'(/Person/Name)[1]\',\'VARCHAR(50)\') = \'张飞\'

\"SQL

3、exist(XPath条件)：返回是否存在

结果为布尔值; 表示节点是否存在,如果执行查询的 XML 数据类型实例包含NULL则返回NULL。

SELECT * FROM Person WHERE Info.exist(\'(/Person/Name)[1]\') = 1

一个完整实例：

--1、判断 student 中属性 id  的值 是否为空
select @x.exist(\'(/root/people/student/@id)[1]\');
--2、判断指定节点值是否相等
declare @xml xml = \'a\';
select @xml.exist(\'(/root/name[text()[1]=\"a\"])\');

--3、比较日期
--代码 cast as xs:date? 用于将值转换为 xs:date 类型，以进行比较。
--@Somedate 属性的值是非类型化的。比较时，此值将隐式转换为比较右侧的类型（xs:date 类型）。
--可以使用 xs:date() 构造函数，而不用 cast as xs:date()。
declare @a xml;
set @a = \'\';
select @a.exist(\'/root[(@Somedate cast as xs:date?) eq xs:date(\"2012-01-01\")]\');

4、nodes(XPath条件)：返回由符合条件的节点组成的多行一列的结果表

语法: nodes(QueryString) as table(column)

如果要将xml数据类型拆分为关系数据，使用nodes方法将非常有效，它允许用户将标识映射到新行的节点。

--查询节点
　　SELECT T2.Loc.query(\'.\') as result
　　FROM Person
　　CROSS APPLY Info.nodes(\'/Person/Name\') as T2(Loc)

\"\"

例二：-将 student节点拆分成多行

--获得所有student节点的数据,每一行显示一条student节点的数据
select T.c.query(\'.\')  as result from @myxml.nodes(\'/people/student\') as  T(c)
--将这些数据显示为一个表格
select T.c.value(\'(@id)[1]\',\'int\') as id,
            T.c.value(\'(./Name)[1]\',\'nvarchar(16)\') as name,
            T.c.value(\'(./Age)[1]\',\'int\') as age,
            T.c.value(\'(./Address)[1]\',\'nvarchar(16)\') as address
            from @myxml.nodes(\'/people/student\') as T(c)

\"SQL

一个完整的实例：

--1、 对表中的 xml 数据进行解析, 节点下面有多个相同节点的 使用 cross apply 和 nodes() 方法解析
if object_id(\'tempdb..[#tb]\') is not null
    drop table [#tb];

create table [#tb]
    (
        [id] int ,
        [name] xml
    );

insert [#tb]
       select 1, \'ab\'
       union all
       select 2, \'b\'
       union all
       select 3, \'d\';

select id, T.c.query(\'.\'), T.c.value(\'.\', \'sysname\') from [#tb] A cross apply A.name.nodes(\'/r/i\') T(c);

--2、利用xml 拆分字符串
declare @s varchar(100) = \'1,2,3,4,5,6\';

select T.c.value(\'.\', \'int\') as col
from   ( select cast(\'\' + replace(@s, \',\', \'\') + \'\' as xml).query(\'.\') as name ) as a
       cross apply a.name.nodes(\'/x\') T(c);

--3、取任意属性的属性值，这里引入了 sql:variable
declare @x1 xml;

select @x1 = \'

  
  
\';

declare @pos int;

select @pos = 2;

select @x1.value(\'local-name(
        (/Employees/Employee[2]/@*[position()=sql:variable(\"@pos\")])[1] )\', \'VARCHAR(20)\') as AttName;

--4、将普通数据列和 xml 数据列进行合并
--sql:column() 函数
declare @t1 table
    (
        id int ,
        data xml
    );

insert into @t1 ( id, data )
            select 1, \'二辉流氓\'
            union all
            select 2, \'彪流氓\';

select id, data = data.query(\'
    {sql:column(\"id\")}
    {/root/name}
    {/root/type}
    \') from @t1;

--5、提取长度为5的数字
--string-length() 函数 和 number() 函数
declare @t table
    (
        CustomerID int ,
        CustomerAddress varchar(50)
    );

insert into @t ( CustomerID, CustomerAddress )
            select 1, \'12 20 97TH STREET NEW GARDENS, NY  11415  APT 8P\'
            union all
            select 2, \'20-10 93RD STREET #8A VICTORIA NY 11106 19TH FLR\'
            union all
            select 3, \'290 BERKELEY STREET APT24D  NYC, NY  10038\'
            union all
            select 4, \'351-250  345 STREET PANAMA BEACH 11414  APT4F\';

with cte
as ( select CustomerID, cast(\'\' + replace(CustomerAddress, \' \', \'\') + \'\' as xml).query(\'.\') as CustomerAddress
     from   @t )
select CustomerID, x.i.value(\'.\', \'VARCHAR(10)\') as ZipCode
from   cte
       cross apply CustomerAddress.nodes(\'//i[string-length(.)=5][number()>0]\') x(i);

三、modify()：修改XML修改XML字段

使用此方法可以修改xml数据内容。
xml数据类型的modify方法只能在update语句的set字句中使用，注意如果是针对null值调用modify方法将返回错误。

1、modify（insert）增加节点

--modify(insert)增加节点
update Person set Info.modify(\'
    insert 25
    into (/Person)[1]\') 
where Id = 3;

\"\"

实例：

--1、在 student 节点下插入 一个新节点
SET @x.modify(\'
insert 阿彪
as first
into (/root/people/student)[1]
\');
SELECT @x

--注释：如果某节点下面有多个节点的时候可以使用 as first 或 as last 来指定所需的新节点添加位置。
 
---2、在指定的 student 节点下，插入同一级节点
SET @x.modify(\'
insert 1
before (/root/people/student)[1]
\');
SELECT @x
--注释：是用 before 或者 after 关键字代替 into 在指定节点的 前面 或者 后面 插入同级节点
--after 关键字 和 before 关键字不能用于插入属性
 
--3、插入属性 一次插入多个属性值/使用变量/属性定位
DECLARE @a INT =5
SET @x.modify(\'           
insert (
            attribute a {sql:variable(\"@a\")},
            attribute b {\".5\"}          
 
        )     
into   (/root/people/student[@id=1])[1]
\');           
SELECT @x;           
GO

2、modify（delete ）删除节点

xQuery知识，没有text()就直接删除节点

UPDATE Person
　　SET Info.modify(\'
        delete (/Person)[1]/Age/text()\'
　　)
　　where ID = 3

实例：

-- 1、删除属性
SET @x.modify(\'
  delete /root/people/student/@id
\')
SELECT @x
 
-- 2、删除节点
SET @x.modify(\'
  delete /root/people/student/name[1]
\')
SELECT @x
 
-- 3、删除节点内容
SET @x.modify(\'
  delete /root/people/student/type/text()
\')
SELECT @x
 
-- 4、删除所有处理指令
SET @x.modify(\'
  delete //processing-instruction()
\')
SELECT @x
 
-- 5、删除所有的内容为空的节点
SET @x.modify(\'
delete //*[empty(./*)]
\')
 
SELECT @x

-----------------------------------------------------------
-- 把  小D  移动到 彪 前面
------------------------------------------------------------
SET @x1.modify(\'
    insert /people/student[@name=\"小D\"] 
    before (/people/student[@name=\"彪\"])[1]
    \')
 
SET @x1.modify (\'
        delete (/people/student[@name=\"小D\"])[2]
    \')
 
SELECT @x1
 

------------------------------------------------------------
-- 把 野子 向前移动一级
------------------------------------------------------------
SET @x1.modify(\'
    insert /people/student[@name=\"野子\"] 
    before (/people/student[. << (/people/student[@name=\"野子\"])[1]])[last()]
    \')
 
SET @x1.modify (\'
        delete /people/student[@name=\"野子\"] 
        [. is (/people/student[@name=\"野子\"])[last()]]
    \')
 
SELECT @x1
------------------------------------------------------------
-- 把 彪  向后 移一级
------------------------------------------------------------
set @x1.modify(\'
    insert /people/student[@name=\"彪\"] 
    before (/people/student[. >> (/people/student[@name=\"彪\"])[1]])[2]
    \')
SELECT @x1
SET @x1.modify (\'
        delete (/people/student[@name=\"彪\"])[1]
    \')
 
SELECT @x1

3、modify（replace value of） --更新单个节点

在修改语法当中 每次只能修改一个单个节点，不能批量修改或者一次修改多个值，这一点是比较郁闷的

declare @x xml;

set @x = \'

  
    
      彪
      阿彪
      流氓
    
  
  
    
      光辉
      二辉
      流氓
    
  
\';

 
-- 修改节点值
SET @x.modify(\'
  replace value of (/root/people/student/name/text())[1]
  with     \"光辉\"
\')
SELECT @x
 
-- 修改属性值
SET @x.modify(\'
  replace value of (/root/people/student/@weight)[1]
  with     \"70\"
\')
SELECT @x
 
-- 使用 if 表达式
SET @x.modify(\'
  replace value of (/root/people/student/@age)[1]
  with (
       if (count(/root/people/student/*) > 4) then
         \"30\"
       else
         \"10\"
      )
\')
SELECT @x

四、for xml子句:表数据自动生成xml格式

通过使用for xml子句，我们可以检索系统中表的数据并自动生成xml格式。一共有4种模式：RAW、AUTO、EXPLICIT、PATH。

for xml子句可以用在顶级查询和子查询中，顶级for xml子句只能出现在select语句中，子查询中的for xml子句可以出现在insert、delete、update以及赋值语句中。

1、raw模式

raw模式是这4种模式里最简单的一种。将为select语句所返回的查询结果集中的每一行转换为带有通用标记符“”或可能提供元素名称的xml元素。
默认情况下，行集中非null的列都将映射为元素的一个属性。这样当使用select查询时，会对结果集进行xml的转换，它们将会被转为row元素的属性。

select teacherId, teacherName from teacher where teacherSex = \'女\' for xml raw;

--结果：
--      

select student.id, student.name, teacher.teacherId, teacher.teacherName
from   student
       inner join teacher on student.teacherId = teacher.teacherId
for xml raw;
--结果:   
--
  • 如果将 ELEMENTS 指令添加到 FOR XML 子句，则每个列值都将映射到 元素的子元素。
  • 指定 ELEMENTS 指令之后，您还可以选择性地指定 XSINIL 选项以将结果集中的 NULL 列值映射到具有 xsi:nil=\"true\" 属性的元素。
  • 您可以通过向 RAW 模式指定一个可选参数为该元素指定另一个名称，如该查询中所示。SELECT * FROM #tb FOR XML RAW(\'流氓们\')
  • RAW 模式和 AUTO 模式都可以使用 ROOT , ELEMENTS XSINIL, TYPE 指令。
--> 测试数据：#tb
IF OBJECT_ID(\'TEMPDB.DBO.#tb\') IS NOT NULL     DROP TABLE #tb


CREATE TABLE #tb        (         [id] INT IDENTITY PRIMARY KEY ,         [name] VARCHAR(4),         [type] VARCHAR(10)        )
INSERT  #tb         SELECT  \'中\' , \'OK\'         UNION ALL         SELECT  \'美\' , \'NG\'

--------------开始查询--------------------------
 SELECT * FROM #tb FOR XML raw;--

SELECT * FROM #tb FOR XML raw(\'行\'),ELEMENTS;--<行>1中OK<行>2美NG

2、auto模式：表名作为元素名、生成简单的层次结构

auto模式也是返回xml数据，它与raw的区别在于返回的xml数据中，不是以raw作为元素节点名，而是使用表名作为元素名。这个是最明显的区别。

除此之外，auto模式的结果集还可以形成简单的层次关系。

select teacherId, teacherName from teacher where teacherSex = \'女\' for xml auto;
--结果：
--      


select student.id, student.name, teacher.teacherId, teacher.teacherName
from   student
       inner join teacher on student.teacherId = teacher.teacherId
for xml auto;
/*  生成了嵌套关系

  


  

*/

3、path模式

--> 测试数据：#tb
if object_id(\'TEMPDB.DBO.#tb\') is not null
    drop table #tb;

create table #tb
    (
        [id] int identity primary key ,
        [name] varchar(4) ,
        [type] varchar(10)
    );

insert #tb select \'中\', \'OK\' union all select \'美\', \'NG\';

--------------开始查询--------------------------
--1、没有名称的列
--生成此 XML。 默认情况下，针对行集中的每一行，生成的 XML 中将生成一个相应的  元素。 这与 RAW 模式相同。
select 1 for xml path;
--1

--2、延伸
select [name] + \'\' from #tb for xml path;
--select [name] + \'\' from #tb for xml path;

--3、去掉 元素
select [name] + \'\' from #tb for xml path(\'\');
--中美

--4、具有名称的列
select [name] from #tb for xml path;
--中美

--5、列名以 @ 符号开头。
select id as \'@id\', [name] from #tb for xml path;
--中美

--6、列名不以 @ 符号开头
select [name] as 臭流氓 from #tb for xml path(\'一群流氓\');
--<一群流氓><臭流氓>中<一群流氓><臭流氓>美

--7、列名以 @ 符号开头并包含斜杠标记 (/)
select id as \'@id\', [name] as \'一群流氓/臭流氓\' from #tb for xml path;
--<一群流氓><臭流氓>中<一群流氓><臭流氓>美

--8、名称指定为通配符的列
--如果指定的列名是一个通配符 (*)，则插入此列的内容时就像没有指定列名那样插入。 
--如果此列不是 xml 类型的列，则此列的内容将作为文本节点插入
select id as \'@id\', [name] as \'*\' from #tb for xml path;
--中美

--9、列名为 XPath 节点测试的列
--text()
--对于名为 text() 的列，该列中的字符串值将被添加为文本节点。
--comment()
--对于名为 comment() 的列，该列中的字符串值将被添加为 XML 注释。
--node()
--对于名为 node() 的列，结果与列名为通配符 (*) 时相同。
--处理指令（名称）
--如果列名为处理指令，该列中的字符串值将被添加为此处理指令目标名称的 PI 值。
select id as \'@id\', \'臭流氓\' as \'text()\', \'一个臭流氓\' as \"processing-instruction(PI)\", \'chouliumang\' as \'comment()\', [name] as \'EmpName/text()\' ,
       [name] as \'臭流氓/node()\'
from   #tb
where  id = 1
for xml path;
--臭流氓中<臭流氓>中

--10、带有指定为 data() 的路径的列名
--如果被指定为列名的路径为 data()，则在生成的 XML 中，该值将被作为一个原子值来处理。 
--如果序列化中的下一项也是一个原子值，则将向 XML 中添加一个空格字符。 
--这在创建列表类型化元素值和属性值时很有用。 以下查询将检索产品型号 ID、名称和该产品型号中的产品列表。
select id as \'@id\', [name] as \'@name\', [name], [type] as \'data()\' from #tb where id = 1 for xml path;
--中OK

--11、默认情况下，列中的 Null 值映射为“缺少相应的属性、节点或元素”。 
--通过使用 ELEMENTS 指令请求以元素为中心的 XML 并指定 XSINIL 来请求为 NULL 值添加元素，
--可以覆盖此默认行为，如以下查询所示：
--未指定 XSINIL，将缺少  元素。
select id as \'@id\', null as \'xx/null\', [name] as \'xx/name\', [type] as \'xx/type\' from #tb for xml path;
--中OK美NG
select id as \'@id\', null as \'xx/null\', [name] as \'xx/name\', [type] as \'xx/type\' from #tb for xml path, elements xsinil;
--中OK美NG

--12、ROOT/TYPE/BINARY选项
select id as \'@id\', [name], [type], 0x78786F6F as \'VARBINARY\'
from   #tb
for xml path, root(\'oo\'), --指定向产生的 XML 中添加单个顶级元素。 可以选择指定要生成的根元素名称。 默认值为“root”。
type, --指定查询以 xml 类型返回结果。
binary base64; --如果指定 BINARY Base64 选项，则查询所返回的任何二进制数据都用 base64 编码格式表示。
--若要使用 RAW 和 EXPLICIT 模式检索二进制数据，必须指定此选项。 
--在 AUTO 模式中，默认情况下将二进制数据作为引用返回。 有关使用示例，请参阅将 RAW 模式与 FOR XML 一起使用。
--中OKeHhvbw==美NGeHhvbw==

到此这篇关于SQL Server操作XML类型的文章就介绍到这了。希望对大家的学习有所帮助，也希望大家多多支持脚本之家。

相关推荐

【一起学Java-第一篇】Java语言概述

如何理解IP地址和子网掩码

Spring框架（第3天）——AOP和声明式事务

Whoosh：Python 的轻量级搜索工具

OSError WinError 182 Error loading Anaconda3\lib\site-packages\torch\lib\caffe2_detectron_ops.dll

Faster RCNN学习笔记

《第四期（2021-2022）传统行业云原生技术落地调研——央国企篇》正式发布

c语言常见的字符函数,（C语言）常见字符函数和字符串函数

JSON数据传输大法第一式——用OADate处理日期格式

自编码器 AE（AutoEncoder）程序

vite和webpack的区别

R计算两列数据的相关系数_数据相关性分析 correlation - R实现

Base64与文件互转

【2】数据湖架构中 Iceberg 的核心特性

docker 安装与docker-compose 安装配置nginx

yolov5-6.0部署：基于旋转目标的yolov5部署---opencv

机器视觉——单目相机模型（坐标标定以及去畸变）

MySQL详细讲解变量variables的用法

el-select 下拉框样式修改

python单元测试框架之unittest和pytest的区别

ItVuer - 免责声明 - 关于我们 - 联系我们

本网站信息来源于互联网，如有侵权请联系：561261067@qq.com

桂ICP备16001015号