《SQL Server 2008从入门到精通》–20180704

澳门新葡亰亚洲在线 25

XML查询手艺

XML文书档案以贰个纯文本的花样存在,重要用以数据存款和储蓄。不但方便人民群众客商读取和应用,而且使修正和保障变得更易于。

 

XML数据类型

XML是SQL
Server中置放的数据类型,可用于SQL语句大概当作存款和储蓄进度的参数。客商能够直接在数据库中存款和储蓄、查询和治本XML文件。XML数据类型还是能保存整个XML文书档案。XML数据类型和任何数据类型不设有根本上的歧异,能够把它用在其余日常SQL数据类型基本上能用之处。
示例1:成立五个XML变量并用XML填充

DECLARE @doc XML
SELECT @doc='<Team name="Braves" />';

示例2:创制XML数据类型列

CREATE TABLE t1(
column1 INT,
column2 XML,
CONSTRAINT pk_column1 PRIMARY KEY(column1));

在地点的示范中,column2列是XML数据类型列。
示例3:无法将XML数据类型列设置为主键或外键

CREATE TABLE t1(
column1 INT,
column2 XML,
CONSTRAINT pk_column1 PRIMARY KEY(column2));

进行上边的代码,报错如下:
消息1919,级别16,状态1,第1 行
表’t1′ 中的列’column2′ 的项目不能用作索引中的键列。
消息1750,级别16,状态0,第1 行
心有余而力不足创造约束。请参阅前面包车型客车错误消息。
XML数据类型的利用范围
唯有STQashqaiING数据类型手艺转变成XML。
XML列不能够使用于GROUP BY语句中
XML数据类型存款和储蓄的数额不能够胜过2GB
XML数据类型字段不可能被设置成主键也许外键或称为其风姿浪漫部分。
Sql_variant数据类型字段的使用不可能把XML数据类型作为种子品种。
XML列不可能钦点为唯风流倜傥的。
COLLATE子句不能够被运用在XML列上。
积攒在数据库中的XML仅援助128级的层系。
表中最对只可以具备三十六个XML列。
XML列不可能投入到法则中。
唯风姿浪漫可应用于XML列的嵌入标量函数是ISNULL和COALESCE。
抱有XML数据类型列的表不能够有二个超过15列的主键。

SQL
Server对于XML帮忙的着力在于XML数据的格式,这种数据类型能够将XML的数码存款和储蓄于数据库的对象中,举个例子variables,
columns, and
parameters。当您用XML数据类型配置这一个目的中的两个时,你内定项目标名字就如您在SQLServer
中钦定三个种类相符。

类型化的XML和非类型化的XML

可以创立xml类型的变量,参数和列,也许将XML架构集结和xml类型的变量、参数或列关联,这种气象下,xml数据类型实例称之为类型化xml实例。不然XML实例称为非类型化的实例。

XML的数据类型确定保证了你的XML数据被完全的创设保存,同期也符合ISO的正式。在概念二个XML数据类型在此之前,大家首先要掌握它的二种范围,如下:

XML数据类型方法

XML数据类型共有5种艺术
query():实行二个XML查询并赶回查询结果(重回三个XML数据类型卡塔尔。
示例4

DECLARE @xmlDoc XML--声明XML类型的变量@xmlDoc
SET @xmlDoc='<students>
    <class name="数学" NO="8501">
        <student>
            <name>李林</name>
            <sex>男</sex>
            <age>16</age>
            <address>江苏</address>
        </student>
    </class>
</students>'--将XML实例分配给变量@xmlDoc
SELECT @xmlDoc.query('/students/class/student') AS test
--用query()查询@xmlDoc变量实例中标签<student>的子元素

查询结果如图所示
澳门新葡亰亚洲在线 1
点击查询结果
澳门新葡亰亚洲在线 2
如想询问标签

DECLARE @addr XML--声明一个XML类型变量@addr
SET @addr='/students/class/student'
SELECT @addr.exist('/students/class="江苏"') AS 返回值

结果如图所示
澳门新葡亰亚洲在线 3

注:exsit()方法的参数不必做正鲜明位

Value():总括二个查询并从XML中回到四个简单易行的值(只可以回去单个值,且该值为非XML数据类型卡塔尔国。
Value()方法有2个参数XQuery和SQLType,XQuery参数表示命令要从XML实例之中查询数据的具体地方,SQLType参数表示value()方法再次来到的值的首要推荐数据类型。
示例6

DECLARE @xmlDoc XML--声明XML类型的变量@xmlDoc
DECLARE @classID INT--声明INT类型的变量@classID
SET @xmlDoc='<students>
    <class name="数学" NO="8501">
        <student>
            <name>李林</name>
            <sex>男</sex>
            <age>16</age>
            <address>江苏</address>
        </student>
    </class>
</students>'--将XML实例分配给变量@xmlDoc
SET @classID=@xmlDoc.value('(/students/class/@NO)[1]','INT')
--将value()方法返回值赋值给变量@classID
SELECT @classID AS classID

查询结果如图所示
澳门新葡亰亚洲在线 4

注:SQLType无法是XML数据类型,公共语言运转时(CLQashqai卡塔尔国客户定义类型,image,text,ntext或sql_variant数据类型,但足以是客户自定义数据类型SQL。

Modify():在XML文书档案的适龄地点施行三个改善操作。它的参数XML_DML代表后生可畏串字符串,依据此字符串表明式来更新XML文档的剧情。
示例7:在@xmlDoc的实例中,成分

DECLARE @xmlDoc XML--声明XML类型的变量@xmlDoc
SET @xmlDoc='<students>
    <class name="数学" NO="8501">
        <student>
            <name>李林</name>
            <sex>男</sex>
            <age>16</age>
            <address>江苏</address>
        </student>
    </class>
</students>'
SELECT @xmlDoc AS '插入节点前信息'
SET @xmlDoc.modify('insert <学历>本科</学历> after (students/class/student/age)[1]')
SELECT @xmlDoc AS '插入节点后信息'

询问结果插入节点后新闻如图所示
澳门新葡亰亚洲在线 5

注:modify()方法的参数中insert和别的重点字必需小写,不然会报错

Nodes():允许把XML分解到一个表结构中。此方法将XML数据类型实例拆分为关周详据,并回到满含原始XML数据的行集。
示例8:依旧用@locat参数的实例来演示

DECLARE @locat XML--声明XML变量@locat
SET @locat=
'<root>
    <location locationID="8">
        <step>8的步骤</step>
        <step>8的步骤</step>
        <step>8的步骤</step>
    </location>
    <location locationID="9">
        <step>9的步骤</step>
        <step>9的步骤</step>
        <step>9的步骤</step>
    </location>
    <location locationID="10">
        <step>10的步骤</step>
        <step>10的步骤</step>
        <step>10的步骤</step>
    </location>
    <location locationID="11">
        <step>11的步骤</step>
        <step>11的步骤</step>
        <step>11的步骤</step>
    </location>
</root>'--@locat变量的实例

SELECT T.Loc.query('.') AS result
FROM @locat.nodes('/root/location') T(Loc)
GO

查询结果如下图所示
澳门新葡亰亚洲在线 6

  • 八个实例的XML列不能够包罗当先2GB的多少。
  • 一个XML的列不可能是索引。
  • XML对象无法选用Group By的子句中。
  • XML的数据类型不扶持相比和排序。

XQuery简介

XQuery是大器晚成种查询语言,能够查询结构化恐怕半结构化的多寡。SQL Server
二〇一〇中对XML数据类型提供了支撑,能够存款和储蓄XML文书档案,然后接受XQuery语言进行查询。

概念一个XML变量

FOR XML子句

由此在SELECT语句中选取FO索罗德XML子句能够把数据库表中的数据检索出来并生成XML格式。SQL Server
二〇〇九支撑FO大切诺基XML的种种情势,分别是RAW形式,AUTO形式,EXPLICIT格局和PATH方式。

DECLARE @ClientList XML
SET @ClientList =
'<?xml version="1.0" encoding="UTF-8"?>
<!-- A list of current clients -->
<People>
<Person id="1234">
<FirstName>John</FirstName>
<LastName>Doe</LastName>
</Person>
<Person id="5678">
<FirstName>Jane</FirstName>
<LastName>Doe</LastName>
</Person>
</People>'
SELECT @ClientList
GO
FOR XML RAW

将表转变来元素名称是row,属性名叫列名或然列的小名。
示例9:将Student表转变为XML格式(FO安德拉 XML RAW卡塔尔
Student表的数额如图所示
澳门新葡亰亚洲在线 7
推行语句:

SELECT * FROM Student FOR XML RAW;

询问结果如图所示
澳门新葡亰亚洲在线 8
澳门新葡亰亚洲在线 9

其生龙活虎例子通过应用DECLARE  声明去定义名称为@ClientList
的变量,当自己注解变量的时候,只供给蕴涵XML的数据类型的名字在变量名后。

FOR XML AUTO

利用表名称作为成分名称,使用列名称作为品质名称,SELECT关键字前边列的生龙活虎意气风发用于XML文书档案的档案的次序。
示例10:将Student表调换为XML格式(FO奥迪Q7 XML AUTO卡塔 尔(阿拉伯语:قطر‎
奉行语句:

SELECT * FROM Student FOR XML AUTO;

查询结果如图所示
澳门新葡亰亚洲在线 10
澳门新葡亰亚洲在线 11

自个儿设定了变量的值,然后利用select
来搜寻那个值。和大家想的等同,它回到了XML的文书档案。如下:

FOR XML EXPLICIT

同意客商显式地定义XML树的造型,不受AUTO情势中的各类限定。无法将FO中华V XML
EXPLICIT直接用在SELECT子句中。
示例11:将xmlTest表转变为XML格式(FO瑞虎 XML EXPLICIT卡塔 尔(英语:State of Qatar)
XmlTest表的数额如图所示
澳门新葡亰亚洲在线 12

SELECT DISTINCT 1 AS TAG,--指定顶级层级序号1
NULL AS PARENT,--该层级没有父级
NULL AS '班级信息!1!',
NULL AS '班级信息!2!班级',
NULL AS '班级信息!2!班级类型',
NULL AS '班级信息!2!班主任',
NULL AS '学生信息!3!学号!Element',
NULL AS '学生信息!3!学生姓名!Element',
NULL AS '学生信息!3!性别!Element',
NULL AS '学生信息!3!总分!Element'--设置所有层级元素和属性命名,暂时不对这些元素赋值
--例如在“学生信息!3!总分!Element”格式中,学生信息是元素名,3表示该元素所处层级,总分表示属性名
--Element指出生成以属性单独为一行的XML格式
UNION ALL--层级之间用UNION ALL相连
SELECT DISTINCT 2 AS TAG,--指定二级层级序号2
1 AS PARENT,--父级序号是序号为1的层级
NULL,--在层级的代码中已列出了所有层级元素和属性命名,因此这里给元素和属性做赋值。这句语句对应层级代码中“NULL AS '班级信息!1!'”,说明我希望该元素作为独立成行的标签,没有赋值。
班级,--对层级中的“NULL AS '班级信息!2!班级'”赋值,将xmlTest表中的班级赋值给属性班级
班级类型,--对层级中的“NULL AS '班级信息!2!班级类型'”赋值,将xmlTest表中的班级赋值给属性班级类型
班主任,--同上
NULL,--这句语句开始对应的是层级的属性,因此在层级的代码中不做赋值,在下面层级的代码中做赋值
NULL,
NULL,
NULL
FROM xmlTest--指出上面赋值的数据源来自于xmlTest表
UNION ALL--各个层级之间用UNION ALL连接
SELECT 3 AS TAG,--指定3级层级序号3
2 AS PARENT,--父级是序号为2的层级
NULL,--对应层级的”NULL AS '班级信息!1!'“语句,不希望它有值,所以不做赋值
NULL,--这三个NULL对应层级的各个属性,在层级的代码中已经做过赋值,因此在这里不做赋值
NULL,
NULL,
学号,--对应层级1代码中的层级3属性,在层级代码3中进行赋值
学生姓名,
性别,
年级总分
FROM xmlTest
FOR XML EXPLICIT;--将上述查询转换为XML,不能漏掉,否则结果会以表格形式显示

查询结果如图所示
澳门新葡亰亚洲在线 13
澳门新葡亰亚洲在线 14
在结果图中大家开掘,红框中3个班级新闻列在联合具名,而具有学子都列在高意气风发3班下,那不是大家想要的结果,大家意在每一种班级对应自身的上学的小孩子。那么什么样消除此类主题材料吗,那关系到排序。

注:假若层级中有多少个数据完全重复,能够在该层级对应的代码前加DISTINCT关键字去除重复成分。

率先删除代码行末的FO奥德赛 XML
EXPLICIT语句,仅仅履行剩下的局地,使结果以表格方式表现,那么结果如下
澳门新葡亰亚洲在线 15
以此表格每行的逐一也象征了该表格转变为XML文书档案后内容展现顺序。图中层级2(TAG=2卡塔 尔(英语:State of Qatar)的几行,地方都在同盟,那也等于干吗层级3的有所数据都在高后生可畏3班上边了。大家须求对表格每行的逐一进行调度,使学子所在行依照xmlTest表中的数据逻辑分散在班级行之下。可是办事处方的报表发掘,不管依照什么字段排序,都十分的小概高达效果。
准确代码如下

SELECT DISTINCT 1 AS TAG,
NULL AS PARENT,
NULL AS '班级信息!1!',
NULL AS '班级信息!2!班级',
NULL AS '班级信息!2!班级类型',
NULL AS '班级信息!2!班主任',
NULL AS '学生信息!3!学号!Element',
NULL AS '学生信息!3!学生姓名!Element',
NULL AS '学生信息!3!性别!Element',
NULL AS '学生信息!3!总分!Element'
UNION ALL
SELECT DISTINCT 2 AS TAG,
1 AS PARENT,
NULL,
班级,
班级类型,
班主任,
NULL,
NULL,
NULL,
NULL
FROM xmlTest
UNION ALL
SELECT 3 AS TAG,
2 AS PARENT,
NULL,
班级,
班级类型,
班主任,
学号,
学生姓名,
性别,
年级总分
FROM xmlTest
ORDER BY [班级信息!2!班级],[学生信息!3!学号!Element]
FOR XML EXPLICIT;

相对来讲第一回代码,我们发掘下边包车型大巴代码不唯有在行末对数据按成分属性举办了排序,还在赋值的代码中负有改观。在层级1代码中完全未有更正,因为层级1的代码效率是安吹牛ML格式的,对数码排序未有影响。在底下多少个层级的赋值部分,每种层级的代码中都对地方多少个层级的要素重复赋值,那样做使结果的表格中不再有那么多属性值是NULL,能够一本万利排序。最终再依照成分[班级新闻!2!班级]和[学员消息!3!学号!Element]排序。让大家看看结果什么。
运行方面包车型大巴代码,但不运转FOXC90 XML
EXPLICIT语句,看看表格中数量内容和行顺序是或不是变动
澳门新葡亰亚洲在线 16
如图所示,开采用实行反革命数据和学员数量的逐风流浪漫显示准确。运维具备代码得到XML文书档案,结果如图所示
澳门新葡亰亚洲在线 17
是因为XML文书档案内容过长,不贴图了,直接复制全体XML内容展现一下。

<班级信息>
  <班级信息 班级="高一1班" 班级类型="创新班" 班主任="李玉虎">
    <学生信息>
      <学号>20180101</学号>
      <学生姓名>李华</学生姓名>
      <性别>男</性别>
      <总分>5.680000000000000e+002</总分>
    </学生信息>
    <学生信息>
      <学号>20180103</学号>
      <学生姓名>孙丽</学生姓名>
      <性别>女</性别>
      <总分>3.390000000000000e+002</总分>
    </学生信息>
    <学生信息>
      <学号>20180108</学号>
      <学生姓名>吴伟</学生姓名>
      <性别>男</性别>
      <总分>5.280000000000000e+002</总分>
    </学生信息>
  </班级信息>
  <班级信息 班级="高一2班" 班级类型="重点班" 班主任="姜杰">
    <学生信息>
      <学号>20180102</学号>
      <学生姓名>张三</学生姓名>
      <性别>男</性别>
      <总分>6.270000000000000e+002</总分>
    </学生信息>
    <学生信息>
      <学号>20180104</学号>
      <学生姓名>袁康</学生姓名>
      <性别>男</性别>
      <总分>4.820000000000000e+002</总分>
    </学生信息>
    <学生信息>
      <学号>20180106</学号>
      <学生姓名>赵四</学生姓名>
      <性别>男</性别>
      <总分>5.680000000000000e+002</总分>
    </学生信息>
  </班级信息>
  <班级信息 班级="高一3班" 班级类型="提高班" 班主任="师从光">
    <学生信息>
      <学号>20180105</学号>
      <学生姓名>王婷</学生姓名>
      <性别>女</性别>
      <总分>7.610000000000000e+002</总分>
    </学生信息>
    <学生信息>
      <学号>20180107</学号>
      <学生姓名>周其</学生姓名>
      <性别>女</性别>
      <总分>3.480000000000000e+002</总分>
    </学生信息>
    <学生信息>
      <学号>20180109</学号>
      <学生姓名>甄诚</学生姓名>
      <性别>女</性别>
      <总分>7.020000000000000e+002</总分>
    </学生信息>
  </班级信息>
</班级信息>

将地点的结果相比较一下原始xmlTest表,看看种种班级和它下属学子的层级关系是还是不是有误。

注:写FORubicon XML
EXPLICIT代码要小心,层级1的代码中先安装层级结构,不要先急着赋值。在部属层级的代码中对层级第11中学的代码进行赋值,最棒重复赋值,不然就能够并发布公文中的排序难题。假诺有些层级现身重复数据,在该层级的代码前加DISTINCT关键字。消弭排序难题最棒的格局是对各类层级的质量重复赋值并在最终用OEvoqueDER
BY按层级属性排序。

留神考查地方的XML文书档案,开掘总分属性的值是个float类型,要把它转变来int,只须求把层级3中对总分的赋值代码改成CAST(年级总分
AS int)
澳门新葡亰亚洲在线 18

<!-- A list of current clients -->
<People>
<Person id="1234">
<FirstName>John</FirstName>
<LastName>Doe</LastName>
</Person>
<Person id="5678">
<FirstName>Jane</FirstName>
<LastName>Doe</LastName>
</Person>
</People>
FOR XML PATH

PATH情势提供了黄金时代种较简单的主意来混合成分及品质。在PATH格局中,列名或列小名被视作XPATH表达式来管理,这几个表明式钦命了什么将值映射到XML中。暗中同意情形下,PATH方式为每同样自动生成

 

从未名称的列

上面介绍生机勃勃种简易的FOEvoque XML PATH应用措施

SELECT 2+3 FOR XML PATH;--将2+3的值转换成xml格式

询问结果如图所示
澳门新葡亰亚洲在线 19

注:如果提供了空字符串FOLAND XML PATH(‘’)则不会扭转任何因素。

SELECT 2+3 FOR XML PATH('');--将2+3的值转换成xml格式并去掉<row>

询问结果如图所示
澳门新葡亰亚洲在线 20
示例12:利用xmlTest表和mainTeacher表查询出xmlTest表中培养>=700分的上学的儿童的班老总音信和学子音讯,并转造成XML格式
XmlTest表数据如下图所示
澳门新葡亰亚洲在线 21
MainTeacher表数据如下图所示
澳门新葡亰亚洲在线 22
进行下面包车型地铁言辞

SELECT xmlTest.学号 AS '学生信息/@学号',--@符号表示该名称为属性名,斜杠表示子层级
xmlTest.学生姓名 AS '学生信息/@姓名',
xmlTest.班级 AS '学生信息/@班级',
mainTeacher.姓名 AS '学生信息/班主任信息/姓名',
mainTeacher.教师编号 AS '学生信息/班主任信息/教师编号',
mainTeacher.性别 AS '学生信息/班主任信息/性别',
mainTeacher.年龄 AS '学生信息/班主任信息/年龄',
mainTeacher.联系电话 AS '学生信息/班主任信息/联系电话'
FROM xmlTest,mainTeacher
WHERE xmlTest.年级总分>=700
AND xmlTest.班主任=mainTeacher.姓名
FOR XML PATH('result');--将根目录名改为result

询问结果如下所示

<result>
  <学生信息 学号="20180105" 姓名="王婷" 班级="高一3班">
    <班主任信息>
      <姓名>师从光</姓名>
      <教师编号>83928182</教师编号>
      <性别>男</性别>
      <年龄>28</年龄>
      <联系电话>15963002120</联系电话>
    </班主任信息>
  </学生信息>
</result>
<result>
  <学生信息 学号="20180109" 姓名="甄诚" 班级="高一3班">
    <班主任信息>
      <姓名>师从光</姓名>
      <教师编号>83928182</教师编号>
      <性别>男</性别>
      <年龄>28</年龄>
      <联系电话>15963002120</联系电话>
    </班主任信息>
  </学生信息>
</result>

接下去大家看看如何定义四个XML的列

在下边的事例中,作者将创设三个商厦客户的表,表中积存了ID和每一个公司的顾客音信。

USE AdventureWorks2008R2
GO
IF OBJECT_ID('dbo.StoreClients') IS NOT NULL
DROP TABLE dbo.StoreClients
GO
CREATE TABLE dbo.StoreClients
(
StoreID INT IDENTITY PRIMARY KEY,
ClientInfo XML NOT NULL
)
GO

接下去插入数据到那些表中,包涵XML的文档和某些。小编将宣示叁个XML的变量,然后用这么些变量插入这么些文书档案到表的数据行里面。

DECLARE @ClientList XML
SET @ClientList =
'<?xml version="1.0" encoding="UTF-8"?>
<!-- A list of current clients -->
<People>
<Person id="1234">
<FirstName>John</FirstName>
<LastName>Doe</LastName>
</Person>
<Person id="5678">
<FirstName>Jane</FirstName>
<LastName>Doe</LastName>
</Person>
</People>'
INSERT INTO dbo.StoreClients (ClientInfo)
VALUES(@ClientList)
GO

尽管变量将全体XML文书档案插入了进来,不过它是被看做二个纯净的值插入到表列里面来。

相比以上所述,创制和插入都以很直白省略的,接下去大家看一下什么样创建四个XML的参数

概念多个XML参数

例如,我定义@StoreClients 作为三个输入参数,并且陈设它为XML的项目

USE AdventureWorks2008R2
GO
IF OBJECT_ID('dbo.AddClientInfo', 'P') IS NOT NULL
DROP PROCEDURE dbo.AddClientInfo
GO
CREATE PROCEDURE dbo.AddClientInfo
@StoreClients XML
AS
INSERT INTO dbo.StoreClients (ClientInfo)
VALUES(@StoreClients)
GO

下一场大家再看看在蕴藏进度中如何采用XML作为参数:

DECLARE @ClientList XML
SET @ClientList =
'<?xml version="1.0" encoding="UTF-8"?>
<!-- A list of current clients -->
<People>
<Person id="1234">
<FirstName>John</FirstName>
<LastName>Doe</LastName>
</Person>
<Person id="5678">
<FirstName>Jane</FirstName>
<LastName>Doe</LastName>
</Person>
</People>'
EXEC dbo.AddClientInfo @ClientList

经过也是很直接,先将XML数据赋值给变量,然后将变量作为参数施行SP,那是询问你会发觉数目现已在表中了。

今昔我们要学习一下XML类型匡助的主意:query(``)澳门新葡亰亚洲在线,, value().

在这里在此以前大家要了然大器晚成种表达式,正是XQuery,它是生龙活虎种强盛的脚本语言,用来获得XML的数目。SQLServer
支持这种语言的子集,所以大家能运用这种语言的表达式来搜索和改进XML的多寡。

TYPE命令

SQL Server扶持TYPE命令将FO哈弗 XML的查询结果作为XML数据类型再次回到。
示例13:仍是上边的例证,将查询结果作为XML数据类型再次来到。

CREATE TABLE xmlType(xml_col XML);
--首先创建一个表xmlType,只有一列xml数据类型的xml_col
INSERT INTO xmlType
SELECT(--将上面的查询语句全部复制到括号中,末尾加上TYPE,表示将XML文档作为xml数据类型,并插入到表xmlType中
SELECT xmlTest.学号 AS '学生信息/@学号',
xmlTest.学生姓名 AS '学生信息/@姓名',
xmlTest.班级 AS '学生信息/@班级',
mainTeacher.姓名 AS '学生信息/班主任信息/姓名',
mainTeacher.教师编号 AS '学生信息/班主任信息/教师编号',
mainTeacher.性别 AS '学生信息/班主任信息/性别',
mainTeacher.年龄 AS '学生信息/班主任信息/年龄',
mainTeacher.联系电话 AS '学生信息/班主任信息/联系电话'
FROM xmlTest,mainTeacher
WHERE xmlTest.年级总分>=700
AND xmlTest.班主任=mainTeacher.姓名
FOR XML PATH('result'),TYPE
);
SELECT * FROM xmlType;--查询xmlType表

询问结果如图所示
澳门新葡亰亚洲在线 23
双击张开查看XML

<result>
  <学生信息 学号="20180105" 姓名="王婷" 班级="高一3班">
    <班主任信息>
      <姓名>师从光</姓名>
      <教师编号>83928182</教师编号>
      <性别>男</性别>
      <年龄>28</年龄>
      <联系电话>15963002120</联系电话>
    </班主任信息>
  </学生信息>
</result>
<result>
  <学生信息 学号="20180109" 姓名="甄诚" 班级="高一3班">
    <班主任信息>
      <姓名>师从光</姓名>
      <教师编号>83928182</教师编号>
      <性别>男</性别>
      <年龄>28</年龄>
      <联系电话>15963002120</联系电话>
    </班主任信息>
  </学生信息>
</result>
注意:

因为XQuery是生机勃勃种极其复杂的言语,咱们只是提到了一片段她的零零部件,倘诺想要更进一竿的通晓它怎么样运用,请查看MSDN XQuery
language
reference.

那我们后天先来经过例子来看一下query()和value
三个点子是何等行使XML数据的。要求小心的是自身接下去的测量检验意况是SQLServer二〇〇八Rubicon2。实例中隐含了ClientDB
数据库、ClientInfoCollection 的XML数据以致ClientInfo 表。

USE master;
GO

IF DB_ID('ClientDB') IS NOT NULL
DROP DATABASE ClientDB;
GO

CREATE DATABASE ClientDB;
GO

USE ClientDB;
GO

IF OBJECT_ID('ClientInfoCollection') IS NOT NULL
DROP XML SCHEMA COLLECTION ClientInfoCollection;
GO

CREATE XML SCHEMA COLLECTION ClientInfoCollection AS 
'<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
xmlns="urn:ClientInfoNamespace" 
targetNamespace="urn:ClientInfoNamespace" 
elementFormDefault="qualified">
  <xsd:element name="People">
    <xsd:complexType>
      <xsd:sequence>
        <xsd:element name="Person" minOccurs="1" maxOccurs="unbounded">
          <xsd:complexType>
            <xsd:sequence>
              <xsd:element name="FirstName" type="xsd:string" minOccurs="1" maxOccurs="1" />
              <xsd:element name="LastName" type="xsd:string" minOccurs="1" maxOccurs="1" />
              <xsd:element name="FavoriteBook" type="xsd:string" minOccurs="0" maxOccurs="5" />
            </xsd:sequence>
            <xsd:attribute name="id" type="xsd:integer" use="required"/>
          </xsd:complexType>
        </xsd:element>
      </xsd:sequence>
    </xsd:complexType>
  </xsd:element>
</xsd:schema>';
GO

IF OBJECT_ID('ClientInfo') IS NOT NULL
DROP TABLE ClientInfo;
GO

CREATE TABLE ClientInfo
(
  ClientID INT PRIMARY KEY IDENTITY,
  Info_untyped XML,
  Info_typed XML(ClientInfoCollection)
);

INSERT INTO ClientInfo (Info_untyped, Info_typed)
VALUES
(
  '<?xml version="1.0" encoding="UTF-8"?>
  <People>
    <Person id="1234">
      <FirstName>John</FirstName>
      <LastName>Doe</LastName>
    </Person>
    <Person id="5678">
      <FirstName>Jane</FirstName>
      <LastName>Doe</LastName>
    </Person>
  </People>',
  '<?xml version="1.0" encoding="UTF-8"?>
  <People xmlns="urn:ClientInfoNamespace">
    <Person id="1234">
      <FirstName>John</FirstName>
      <LastName>Doe</LastName>
    </Person>
    <Person id="5678">
      <FirstName>Jane</FirstName>
      <LastName>Doe</LastName>
    </Person>
  </People>'
);

Listing 1: 创制测验情状和数据

FOOdyssey XML的嵌套查询

示例14:在示范12的询问结果中查询班董事长联系电话

SELECT (
SELECT xmlTest.学号 AS '学生信息/@学号',
xmlTest.学生姓名 AS '学生信息/@姓名',
xmlTest.班级 AS '学生信息/@班级',
mainTeacher.姓名 AS '学生信息/班主任信息/姓名',
mainTeacher.教师编号 AS '学生信息/班主任信息/教师编号',
mainTeacher.性别 AS '学生信息/班主任信息/性别',
mainTeacher.年龄 AS '学生信息/班主任信息/年龄',
mainTeacher.联系电话 AS '学生信息/班主任信息/联系电话'
FROM xmlTest,mainTeacher
WHERE xmlTest.年级总分>=700
AND xmlTest.班主任=mainTeacher.姓名
FOR XML PATH('result'),TYPE).query('result/学生信息/班主任信息/联系电话') AS '优秀教师联系方式';

SELECT里面依然沿用了演示第13中学被套用的代码,外面用了query方法,查询结果如下图所示
澳门新葡亰亚洲在线 24

<联系电话>15963002120</联系电话>
<联系电话>15963002120</联系电话>

The XML query() Method

query方法,常常被用来回到叁个钦定XML子集的无类型的XML实例,如下,用括号加单引号来达成表明式,语法:

db``_object``.query('``xquery_exp``')

当大家调用这一个艺术时,用诚实数据库对象替换掉引号内的表明式。通超过实际例来相比较一下结果有如何不相像。

SELECT Info_untyped.query('/People')
  AS People_untyped
FROM ClientInfo;

Listing 2: 使用query(``) 来获得<People>元素中的值

在这里种情形下,将回来标签下全数的因素,包罗子成分属性以致它们的值。

<People>
  <Person id="1234">
    <FirstName>John</FirstName>
    <LastName>Doe</LastName>
  </Person>
  <Person id="5678">
    <FirstName>Jane</FirstName>
    <LastName>Doe</LastName>
  </Person>
</People>

Listing 3: 结果集重临了/People 的内容

豆蔻梢头旦筹算寻找类型化的列中的<People>
成分的故事情节,作者急需修正XQuery的表明式。如Listing 4

SELECT Info_typed.query(
  'declare namespace ns="urn:ClientInfoNamespace";
  /ns:People') AS People_typed
FROM ClientInfo;

Listing 4: 使用query(``)
来检索类型化的XML列,然后你运转这么些讲话,就能拿走结果如Listing5

<People xmlns="urn:ClientInfoNamespace">
  <Person id="1234">
    <FirstName>John</FirstName>
    <LastName>Doe</LastName>
  </Person>
  <Person id="5678">
    <FirstName>Jane</FirstName>
    <LastName>Doe</LastName>
  </Person>
</People>

Listing 5: 显示结果

如上,大家发掘二种结果是很周边的,唯后生可畏的界别正是类型化的列里面富含了事关的命名空间。

例如我们筹划得到子下超级,子成分的内容,我们须求改过表明式,通过加多/Person
到路线名称中,如下:

SELECT 
  Info_untyped.query(
    '/People/Person') AS People_untyped,
  Info_typed.query(
    'declare namespace ns="urn:ClientInfoNamespace";
    /ns:People/ns:Person') AS People_typed
FROM ClientInfo;

Listing 6: 检索 <Person> 元素

<Person id="1234">
  <FirstName>John</FirstName>
  <LastName>Doe</LastName>
</Person>
<Person id="5678">
  <FirstName>Jane</FirstName>
  <LastName>Doe</LastName>
</Person>

Listing 7: 那一个结果集是非类型化数据的结果

<ns:Person xmlns:ns="urn:ClientInfoNamespace" id="1234">
  <ns:FirstName>John</ns:FirstName>
  <ns:LastName>Doe</ns:LastName>
</ns:Person>
<ns:Person xmlns:ns="urn:ClientInfoNamespace" id="5678">
  <ns:FirstName>Jane</ns:FirstName>
  <ns:LastName>Doe</ns:LastName>
</ns:Person>

Listing 8: 那个结果集是类型化数据的结果

只要大家思忖去获得钦命的<Person>下面的某一个元素,需要加入涉及的id属性。下面对比类型和非类型的两种情况下指定元素属性时如何获取。

SELECT 
  Info_untyped.query(
    '/People/Person[@id=1234]') AS People_untyped,
  Info_typed.query(
    'declare namespace ns="urn:ClientInfoNamespace";
    /ns:People/ns:Person[@id=5678]') AS People_typed
FROM ClientInfo;

Listing 9: 检索数据,钦点成分

眼下的远非变动,根据成分来增添表明式,然后用中括号,在中括号内加多了@id的值,结果如下

<Person id="1234">
  <FirstName>John</FirstName>
  <LastName>Doe</LastName>
</Person>

Listing 10: id为1234非类型化数据结果再次回到值。

对此类型化的列,作者利用的id为5678.小心,此次不再供给在性质名称前增加命名空间的前缀了,只须求在要素名字前引述就丰硕了。

<ns:Person xmlns:ns="urn:ClientInfoNamespace" id="5678">
  <ns:FirstName>Jane</ns:FirstName>
  <ns:LastName>Doe</ns:LastName>
</ns:Person>

Listing 11: id为5678的数量结果

更进一层的显得结果,向下一级

SELECT 
  Info_untyped.query(
    '/People/Person[@id=1234]/FirstName') AS People_untyped,
  Info_typed.query(
    'declare namespace ns="urn:ClientInfoNamespace";
    /ns:People/ns:Person[@id=5678]/ns:FirstName') AS People_typed
FROM ClientInfo;

结果

<FirstName>John</FirstName>

<ns:FirstName xmlns:ns="urn:ClientInfoNamespace">Jane</ns:FirstName>

Listing 14: 名字的结果的来得

本来仍然为能够因而数字索引的方法展示:

SELECT 
  Info_untyped.query(
    '/People/Person[1]/FirstName') AS People_untyped,
  Info_typed.query(
    'declare namespace ns="urn:ClientInfoNamespace";
    /ns:People/ns:Person[2]/ns:FirstName') AS People_typed
FROM ClientInfo;

Listing 15: 使用数字索引来引用成分下的结果

XML索引

出于XML数据类型最大可存款和储蓄2GB的多寡,因而需求创制XML索引来优化查询品质。

XML的value()方法

就有如query()方法一样方便,非常多时候当您想去检索叁个特定的因素或性质的时候,实际不是得到XML的成分,那就足以接受value()了。这种办法只会再次回到八个一定的值,不作为数据类型。因而应当要传递八个参数XQuery表达式和T-SQL数据类型。上面看语法:

db``_object``.value('``xquery_exp``', '``sql_type``')

SELECT 
  Info_untyped.value(
    '(/People/Person[1]/FirstName)[1]', 
    'varchar(20)') AS Name_untyped,
  Info_typed.value(
    'declare namespace ns="urn:ClientInfoNamespace";
    (/ns:People/ns:Person[2]/ns:FirstName)[1]',
    'varchar(20)') AS Name_typed
FROM ClientInfo;

Listing 16: 检索<FirstName> 的值

在Listing16中,笔者内定了[1]在Xquery表达式的前面,所以结果集将只回去第一个体的名字。

Name_untyped         Name_typed
-------------------- --------------------
John                 Jane

Listing 17: <FirstName>的两个结果

自然,大家也足以搜寻种种实例的id的属性值,何况钦命Int类型重返。

SELECT 
  Info_untyped.value(
    '(/People/Person/@id)[1]', 
    'int') AS Name_untyped,
  Info_typed.value(
    'declare namespace ns="urn:ClientInfoNamespace";
    (/ns:People/ns:Person/@id)[2]',
    'int') AS Name_typed
FROM ClientInfo;

Listing 19: 检索三个实例的id属性值

Name_untyped         Name_typed
-------------------- --------------------
1234                 5678

Listing 20: 再次回到四个id的性质

而外在表明式中定义你的XQuery表达式,你也能集结的效率来更是定义你的询问和操作数据。比方,count()功效,我们来获取每一个列中<Person>
成分的个数。

SELECT 
  Info_untyped.value(
    'count(/People/Person)', 
    'int') AS Number_untyped,
  Info_typed.value(
    'declare namespace ns="urn:ClientInfoNamespace";
    count(/ns:People/ns:Person)',
    'int') AS Number_typed
FROM ClientInfo;

Listing 21: 使用count成效来寻觅成分个数

结果如下:

Number_untyped Number_typed
-------------- ------------
2              2

Listing 22: 每列数据中<Person> 成分的数额

别的三个常用的意义是concat(``),
它能够一而再多个或多少个XML成分下的数据。你能够钦点你想连接的每三个局地。示例:

SELECT 
  Info_untyped.value(
    'concat((/People/Person/FirstName)[2], " ", 
      (/People/Person/LastName)[2])', 
    'varchar(25)') AS FullName
FROM ClientInfo;

Listing 23: 使用concat(``)来连接数值

FullName
-------------------------
Jane Doe

Listing 24: 连接后的再次来到值

名和姓被连接起来,组成三个十足的值。都来源于于同叁个<Person>
下,当然也得以来自差别。

主XML索引

主XML索引对XML列中XML实例内的有所标志,值和路径进行索引。成立主XML索引时,相应XML列所在的表必需对该表的主键创制了聚焦索引。

总结

 

笔者们多数明白了XML在SQLServer
中的简单利用,从概念到使用方法。也见到了query(卡塔 尔(阿拉伯语:قطر‎检索子集,也能运用value(卡塔 尔(阿拉伯语:قطر‎检索独立的因素属性的值。当然除外还应该有向exist(``)
andnodes()
那样方法,协作语法都以应用,这有的就不再举办讲了,一模一样。有不通晓的能够私聊。更加多使用方式还请访谈MSDN来收获(搜索XQuery
language reference卡塔 尔(阿拉伯语:قطر‎。

辅助XML索引

为了升高主XML索引的性质,能够创制协理XML索引。唯有创制了主XML索引后技术创立帮忙XML索引。协理XML索引分3种:PATH,VALUES和PROPERTY帮忙XML索引。

创设索引

为表中某些列成立索引,必要该列是XML数据类型。

ALTER TABLE Student
ADD xml_test XML;--对Student表添加一个XML数据类型字段xml_test
--对Student表的xml_test字段创建主XML索引,命名为学生信息表
CREATE PRIMARY XML INDEX 学生信息表
ON Student(xml_test)
GO
--对Student表的xml_test字段创建PATH辅助XML索引,记得写上主索引名
CREATE XML INDEX 辅助学生信息表
ON Student(xml_test)
USING XML INDEX 学生信息表 FOR PATH
GO

注:协助索引的命名不能够与主索引相似。

改进和删除索引(ALTE传祺 INDEX 和 DROP INDEX卡塔 尔(英语:State of Qatar)
ALTER INDEX ALL ON Student--重建所有索引
REBUILD WITH(FILLFACTOR=80,SORT_IN_TEMPDB=ON,STATISTICS_NORECOMPUTE=ON);
--删除索引
DROP INDEX 学生信息表 ON Student
GO

注:删除主索引,与其相关的享有支持索引也会被剔除。因而地点语句中剔除学子音信表索引后,帮忙学子新闻表索引也被剔除了。

OPENXML函数

OPENXML是叁个行集函数,用于检索XML文书档案。在试用OPENXML函数从前,应当要先用系统存款和储蓄进度sp_xml_preparedocument浅析文书档案,该存款和储蓄进度在解析完XML文书档案后会再次来到叁个句柄,使用OPENXML检索文书档案时要将该句柄作为参数字传送给OPENXML。
示例15

--定义两个变量@Student和@StudentInfo
DECLARE @Student int
DECLARE @StudentInfo xml
--使用SET为@StudentInfo赋值
SET @StudentInfo='
<row>
<姓名>祝红涛</姓名>
<班级编号>2019382910</班级编号>
<成绩>89</成绩>
<籍贯>沈阳</籍贯>
</row>
'
--使用系统存储过程sp_xml_preparedocument分析由@Student变量表示的XML文档,将分析得到的句柄赋值给@Student变量
EXEC sp_xml_preparedocument @Student OUTPUT,@StudentInfo
--在SELECT语句中使用OPENXML函数返回行集中的指定数据
SELECT * FROM OPENXML(@Student,'/row',2)
WITH(
姓名 varchar(8),
班级编号 varchar(10),
成绩 int,
籍贯 varchar(20)
);

结果如图所示
澳门新葡亰亚洲在线 25
在上述语句中,sp_xml_preparedocument存款和储蓄进度语句用了2个参数,当中@Student是一个int型变量,该存款和储蓄进程会将句柄存款和储蓄在@Student变量中作为结果数据,@StudentInfo是贰个XML类型的变量,存款和储蓄了将在实行剖判的XML文书档案。
OPENXML函数的言语中,使用了3个参数,个中@Student代表已经通过sp_xml_preparedocument存款和储蓄进程深入分析的文书档案的句柄,’/row’使用X帕特h形式提供了三个路径,代表要回到XML文书档案中该路径下的数量行,2是八个可选数据参数,表示将那几个数据行以元素为主干映射。

发表评论

电子邮件地址不会被公开。 必填项已用*标注