SQL Server中的XML数据类型详解-编程学习网

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

1、创建表

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

2、插入测试数据

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

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='<people>
              <student id="201301">
                  <Name>王五</Name>
                  <Age>18</Age>
                  <Address>湖南</Address>
              </student>
              <student id="201302">
                  <Name>李一</Name>
                  <Age>20</Age>
                  <Address>湖北</Address>
              </student>
            </people>'

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

结果为: <Res age="18" /><Res age="20" />

一个完整实例：

declare @x xml;

set @x = '
<root>
  <people id="001">
    <student  id="1">
      <name>彪</name>
      <name>阿彪</name>
      <type>流氓</type>
    </student >
  </people>
  <people id="002">
    <student  id="2">
      <name>光辉</name>
      <name>二辉</name>
      <type>流氓</type>
    </student >
  </people>
  <people id="001">
    <student  id="3">
      <name>小德</name>
      <name>小D</name>
      <type>臭流氓</type>
    </student >
  </people>
</root>';

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

--

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

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

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


--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 节点下的所有 <Name> 元素。

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

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

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

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

--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('
<result>
 { for $i in /root/people/student /name[1]
    return string($i)
 }
</result>');
--<result>彪 光辉 小德</result>

--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)') = '张飞'

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 = '<root><name>a</name></root>';
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 = '<root Somedate = "2012-01-01Z"/>';
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)

一个完整的实例：

--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, '<r><i>a</i><i>b</i></r>'
       union all
       select 2, '<r><i>b</i></r>'
       union all
       select 3, '<r><i>d</i></r>';

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('<x>' + replace(@s, ',', '</x><x>') + '</x>' as xml).query('.') as name ) as a
       cross apply a.name.nodes('/x') T(c);

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

select @x1 = '
<Employees Dept="IT">
  <Employee Number="1001" Name="Jacob"/>
  <Employee Number="1002" Name="Bob" ReportsTo="Steve"/>
</Employees>';

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, '<root><name>二辉</name><type>流氓</type></root>'
            union all
            select 2, '<root><name>彪</name><type>流氓</type></root>';

select id, data = data.query('<root>
    <id>{sql:column("id")}</id>
    {/root/name}
    {/root/type}
    </root>') 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('<i>' + replace(CustomerAddress, ' ', '</i><i>') + '</i>' 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 <Age>25</Age>
    into (/Person)[1]') 
where Id = 3;

实例：

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

--注释：如果某节点下面有多个节点的时候可以使用 as first 或 as last 来指定所需的新节点添加位置。
 
---2、在指定的 student 节点下，插入同一级节点
SET @x.modify('
insert <id>1</id>
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 /

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 中将生成一个相应的 <row> 元素。 这与 RAW 模式相同。
select 1 for xml path;
--<row>1</row>

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

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

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

--5、列名以 @ 符号开头。
select id as '@id', [name] from #tb for xml path;
--<row id="1"><name>中</name></row><row id="2"><name>美</name></row>

--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;
--<row id="1">中</row><row id="2">美</row>

--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;
--<row id="1">臭流氓<?PI 一个臭流氓?><!--chouliumang--><EmpName>中</EmpName><臭流氓>中</臭流氓></row>

--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;
--<row id="1" name="中"><name>中</name>OK</row>

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

--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 一起使用。
--<oo><row id="1"><name>中</name><type>OK</type><VARBINARY>eHhvbw==</VARBINARY></row><row id="2"><name>美</name><type>NG</type><VARBINARY>eHhvbw==</VARBINARY></row></oo>

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

文章详情

SQL Server中的XML数据类型详解

目录

一、创建测试数据，指定字段数据类型为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、path模式

软考中级精品资料免费领

相关文章

猜你喜欢

SQL Server中的XML数据类型详解

SQL Server中的数据类型详解

SQL Server中的XML数据类型实例分析

SQL数据类型详解

SQL Server中的数据类型有哪些

SQL Server数据类型介绍

SQL SERVER数据类型是怎样的

SQL SERVER的数据类型有哪些

Sql Server有哪些数据类型

SQL SERVER 数据类型有哪些

sql server中的数据类型转换cast与convert

sql server属于哪个类型的数据模型

SQL Server中有哪些常见数据类型

SQL Server 中的数据类型隐式转换问题

SQL SERVER中有哪些易混淆的数据类型

SQL Server中如何实现T-SQL数据类型转换

SQL的常用数据类型列表详解

SQL Server 2008中有哪些分层数据类型

SQL Server中怎么实现数据类型转换

SQL Server数据库、表、数据类型基本概念