SQL基础：SQL Server中的XML数据类型详解

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

–插入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>’)

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

–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;

–查询节点内容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]])})
}’)

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(‘.’);

–/*注释：
— 这里实际上是取所有节点,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 节点下的所有 <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])
‘);
–彪 光辉 小德

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

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”)]’);

–查询节点
　　SELECT T2.Loc.query(‘.’) as result
　　FROM Person
　　CROSS APPLY Info.nodes(‘/Person/Name’) as T2(Loc)

–获得所有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(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

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

declare @x xml;

set @x = ‘
<root>
<people id=”001″>
<student id=”1″ weight=”80″ age=”25″>
<name>彪</name>
<nickname>阿彪</nickname>
<type>流氓</type>
</student>
</people>
<people id=”002″>
<student id=”2″>
<name>光辉</name>
<nickname>二辉</nickname>
<type>流氓</type>
</student>
</people>
</root>’;

— 修改节点值
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

select teacherId, teacherName from teacher where teacherSex = ‘女’ for xml raw;

–结果：<row teacherId=”4″ teacherName=”谢一”/>
— <row teacherId=”5″ teacherName=”罗二”/>

select student.id, student.name, teacher.teacherId, teacher.teacherName
from student
inner join teacher on student.teacherId = teacher.teacherId
for xml raw;
–结果: <row id=”10″ name=”小李” teacherId=”1″ teacherName=”王静” />
— <row id=”11″ name=”小方” teacherId=”2″ teacherName=”李四” />

–> 测试数据：#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;–<row id=”1″ name=”中” type=”OK”/><row id=”2″ name=”美” type=”NG”/>

SELECT * FROM #tb FOR XML raw(‘行’),ELEMENTS;–<行><id>1</id><name>中</name><type>OK</type></行><行><id>2</id><name>美</name><type>NG</type></行>

select teacherId, teacherName from teacher where teacherSex = ‘女’ for xml auto;
–结果：<teacher teacherId=”4″ teacherName=”谢一”/>
— <teacher teacherId=”5″ teacherName=”罗二”/>

select student.id, student.name, teacher.teacherId, teacher.teacherName
from student
inner join teacher on student.teacherId = teacher.teacherId
for xml auto;
/* 生成了嵌套关系
<student id=”10″ name=”小李 “>
<teacher teacherId=”1″ teacherName=”王静” />
</student>
<student id=”11″ name=”小方 “>
<teacher teacherId=”2″ teacherName=”李四” />
</student>
*/

–> 测试数据：#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>