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

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

结果如图所示
太阳集团所有网址16877 1
在上述语句中,sp_xml_preparedocument存储过程语句用了2个参数,其中@Student是一个int型变量,该存储过程会将句柄存储在@Student变量中作为结果数据,@StudentInfo是一个XML类型的变量,存储了将要进行分析的XML文档。
OPENXML函数的语句中,使用了3个参数,其中@Student代表已经经过sp_xml_preparedocument存储过程分析的文档的句柄,’/row’使用XPath模式提供了一个路径,代表要返回XML文档中该路径下的数据行,2是一个可选数据参数,表示将这些数据行以元素为中心映射。

XML的数据类型确保了你的XML数据被完好的构建保存,同时也符合ISO的标准。在定义一个XML数据类型之前,我们首先要知道它的几种限制,如下:

主XML索引

主XML索引对XML列中XML实例内的所有标记,值和路径进行索引。创建主XML索引时,相应XML列所在的表必须对该表的主键创建了聚集索引。

我设定了变量的值,然后使用select 来检索这个值。和我们想的一样,它返回了XML的文档。如下:

XML查询技术

XML文档以一个纯文本的形式存在,主要用于数据存储。不但方便用户读取和使用,而且使修改和维护变得更容易。

这个例子通过使用DECLARE  声明去定义名为@ClientList 的变量,当我声明变量的时候,只需要包含XML的数据类型的名字在变量名后。

辅助XML索引

为了增强主XML索引的性能,可以创建辅助XML索引。只有创建了主XML索引后才能创建辅助XML索引。辅助XML索引分3种:PATH,VALUES和PROPERTY辅助XML索引。

接下来我们看看如何定义一个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的数据。

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

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

  • 一个实例的XML列不能包含超过2GB的数据。
  • 一个XML的列不能是索引。
  • XML对象不能使用Group By的子句中。
  • 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

注:辅助索引的命名不能与主索引相同。

太阳集团所有网址16877,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: 使用数字索引来引用元素下的结果

没有名称的列

下面介绍一种简单的FOR XML PATH应用方式

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

查询结果如图所示
太阳集团所有网址16877 2

注:如果提供了空字符串FOR XML PATH(‘’)则不会生成任何元素。

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

查询结果如图所示
太阳集团所有网址16877 3
示例12:利用xmlTest表和mainTeacher表查询出xmlTest表中成绩>=700分的学生的班主任信息和学生信息,并转化成XML格式
XmlTest表数据如下图所示
太阳集团所有网址16877 4
MainTeacher表数据如下图所示
太阳集团所有网址16877 5
执行下面的语句

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的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> 下,当然也可以来自不同。

修改和删除索引(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变量

FOR XML子句

通过在SELECT语句中使用FOR XML子句可以把数据库表中的数据检索出来并生成XML格式。SQL Server 2008支持FOR XML的四种模式,分别是RAW模式,AUTO模式,EXPLICIT模式和PATH模式。

SQL Server对于XML支持的核心在于XML数据的格式,这种数据类型可以将XML的数据存储于数据库的对象中,比如variables, columns, and parameters。当你用XML数据类型配置这些对象中的一个时,你指定类型的名字就像你在SQLServer 中指定一个类型一样。

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表

查询结果如图所示
太阳集团所有网址16877 6
双击打开查看XML

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

本文由太阳集团所有网址16877发布于太阳集团城网址送彩金,转载请注明出处:《SQL Server 2008从入门到精通》--20180704

您可能还会对下面的文章感兴趣: