图片 18

2008从入门到精通,在SQLServer中的使用

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数据类型的利用限制
独有STPAJEROING数据类型能力调换来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数据类型,公共语言运转时(CLPRADO)用户定义类型,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奥迪Q5XML的七种方式,分别是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格式(FORAV4 XML AUTO)
进行语句:

SELECT * FROM Student FOR XML AUTO;

查询结果如图所示
图片 10
图片 11

自己设定了变量的值,然后使用select
来搜寻那几个值。和我们想的同等,它回到了XML的文书档案。如下:

FOR XML EXPLICIT

允许用户显式地定义XML树的形象,不受AUTO方式中的种种限制。不可能将FOCRUISER XML
EXPLICIT间接用在SELECT子句中。
示例11:将xmlTest表调换为XML格式(FOCRUISER XML EXPLICIT)
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关键字去除重复成分。

首先删除代码行末的FOHighlander XML
EXPLICIT语句,仅仅实施剩下的一部分,使结果以表格方式展现,那么结果如下
图片 15
其一表格每行的一一也表示了该表格转化为XML文书档案后内容展现顺序。图中层级2(TAG=2)的几行,地方都在一起,这也等于怎么层级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]排序。让大家看看结果什么。
运行方面包车型客车代码,但不运维FOCRUISER 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表,看看每一个班级和它下属学生的层级关系是或不是有误。

注:写FOLX570 XML
EXPLICIT代码要留意,层级1的代码中先安装层级结构,不要先急着赋值。在下级层级的代码中对层级第11中学的代码举办赋值,最棒重复赋值,否则就能够出现文中的排序难点。假诺某些层级出现重复数据,在该层级的代码前加DISTINCT关键字。消除排序难点最棒的不二秘技是对一一层级的性质重复赋值并在最终用O奥迪Q3DER
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形式为各样自动生成

 

发表评论

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