《SQL Server 2008自入门到了解》–20180704

创办索引

呢表中某个列创造索引,要求该列是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

横流:援助索引的命名不可知及主索引相同。

XML索引

鉴于XML数据类型最酷而是存储2GB的数额,由此需要制造XML索引来优化查询性能。

FOR XML AUTO

接纳说明名称作为元素名称,使用列名称作为性能名称,SELECT关键字前边列的次第用于XML文档的层次。
示例10:将Student表转换为XML格式(FOR XML AUTO)
履语句:

SELECT * FROM Student FOR XML AUTO;

询问结果如图所示
图片 1
图片 2

辅助XML索引

以加强主XML索引的习性,能够创设援助XML索引。只有创设了主XML索引后才会创辅助XML索引。帮助XML索引分3种:PATH,VALUES和PROPERTY援助XML索引。

主XML索引

主XML索引对XML列中XML实例内的富有标记,值与路径进行索引。创立主XML索引时,相应XML列所于的阐发要对该表的主键成立了聚集索引。

一向不称谓的排

下介绍一种植简易的FOR XML PATH应用措施

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

询问结果如图所示
图片 3

横流:假设提供了空字符串FOR XML PATH(‘’)则不碰面扭转任何因素。

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

查询结果如图所示
图片 4
示例12:利用xmlTest表和mainTeacher表查询有xmlTest表中作育>=700划分的生的班高管新闻及学员信息,并转发成XML格式
XmlTest表数据如下图所示
图片 5
MainTeacher表数据如下图所示
图片 6
推行下的口舌

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>

TYPE命令

SQL Server协理TYPE命令将FOR 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表

查询结果如图所示
图片 7
双击打开查看XML

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

PATH情势提供了一如既往种植于简单的法子来混合元素及性能。在PATH情势被,列名或列别名深受作XPATH表明式来拍卖,这个表明式指定了什么样用值映射到XML中。默认情况下,PATH形式吗各样一样自动生成

XQuery简介

XQuery是一模一样种植查询语言,可以查询结构化或者半结构化的数据。SQL Server
2008中本着XML数据类型提供了协理,可以存储XML文档,然后利用XQuery语言举行询问。

FOR 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方法,查询结果假若下图所示
图片 8

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

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

可以创立xml类型的变量,参数与排,或者以XML架构集合和xml类型的变量、参数或列关联,这种意况下,xml数据类型实例称之为类型化xml实例。否则XML实例称为非类型化的实例。

FOR XML RAW

将表转换成元素名称是row,属性名称也列名或者列的别名。
示例9:将Student表转换为XML格式(FOR XML RAW)
Student表的数如图所示
图片 9
履行语句:

SELECT * FROM Student FOR XML RAW;

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

FOR XML EXPLICIT

同意用户显式地定义XML树的形象,不深受AUTO形式中的种限制。不克用FOR XML
EXPLICIT直接用当SELECT子句被。
示例11:将xmlTest表转换为XML格式(FOR 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关键字去除重复元素。

率先去代码行末的FOR 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的代码功用是设置XML格式的,对数码排序没有影响。在脚几乎个层级的赋值部分,每个层级的代码中还针对上面几乎单层级的要素还赋值,这样做而结果的表中不再暴发这基本上属于性值是NULL,可以方便排序。最终还以元素[班级音讯!2!班级]和[学生消息!3!学号!Element]排序。让咱看看结果什么。
运行方面的代码,但不运行FOR 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表,看看每个班级以及她下属学生的层级关系是否发生误。

流动:写FOR XML
EXPLICIT代码要留意,层级1的代码中优先安装层级结构,不要先急着赋值。在部属层级的代码中针对层级1受之代码进行赋值,最好再赋值,不然就是相会冒出文中的排序问题。尽管某层级出现更数据,在拖欠层级的代码前加DISTINCT关键字。解决排序问题太好之道是本着一一层级的性能重复赋值并当结尾用ORDER
BY按层级属性排序。

精心观看地点的XML文档,发现总分属性之值是只float类型,要将其换成为int,只需要将层级3遭到对总分的赋值代码改成为CAST(年级总分
AS int)
图片 18

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数据类型的运用范围
独自来STRING数据类型才会换成XML。
XML列不克使用叫GROUP BY语句被
XML数据类型存储的多少未可以超越2GB
XML数据类型字段不可知给装成主键或者外键或称为其一部分。
Sql_variant数据类型字段的利用无可知管XML数据类型作为种子品种。
XML列不克指定为唯一的。
COLLATE子词不克被以于XML列上。
存储于数据库中的XML仅帮助128层的层系。
注解中十分对只可以拥有32只XML列。
XML列不可知出席到规则中。
唯可应用为XML列的搁标量函数是ISNULL和COALESCE。
享有XML数据类型列的评释不能起一个跳15列的主键。

FOR XML子句

经过以SELECT语句被以FOR
XML子句可以把数据库表中的数据检索出来并生成XML格式。SQL Server
2008支撑FOR
XML的季栽格局,分别是RAW情势,AUTO情势,EXPLICIT情势及PATH形式。

XML查询技术

XML文档以一个纯文本之形式存在,重要用于数据存储。不但便宜用户读取和应用,而且一旦修改和护卫更换得更便于。

修改和去索引(ALTER INDEX 和 DROP INDEX)
ALTER INDEX ALL ON Student--重建所有索引
REBUILD WITH(FILLFACTOR=80,SORT_IN_TEMPDB=ON,STATISTICS_NORECOMPUTE=ON);
--删除索引
DROP INDEX 学生信息表 ON Student
GO

横流:删除主索引,与该休戚相关的所有协助索引为会师给剔除。因而地点语句被删去学生音讯表索引后,帮助学生消息表索引为于删除了。

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>的子元素

查询结果如图所示
图片 19
点击查询结果
图片 20
若果想询问标签

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

结果如图所示
图片 21

横流: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

查询结果如图所示
图片 22

注:SQLType不克是XML数据类型,公共语言运行时(CLR)用户定义类型,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 '插入节点后信息'

询问结果插入节点后消息如图所示
图片 23

流动: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

询问结果一旦下图所示
图片 24

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’使用XPath形式提供了一个路线,代表要回来XML文档中该路线下的数额举办,2凡是一个可选数据参数,表示以这多少个数量行以元素为主干映射。

相关文章