1. 全栈程序员必看首页

XML 在SQLServer中的使用

全栈程序员-用户IM • • 未分类

XML 在SQLServer中的使用

 

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

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

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

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

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

<!-- 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>

 

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

注意：

因为XQuery是一种非常复杂的语言，我们只是涉及了一部分他的组件，如果想要更进一步的理解它如何应用，请查看MSDN XQuery language reference.

那我们现在先来通过例子来看一下query()和value 两个方法是如何使用XML数据的。需要注意的是我接下来的测试环境是SQLServer2008 R2。实例中包含了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: 创建测试环境和数据

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的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在SQLServer 中的简单应用，从定义到使用方法。也看到了query（）检索子集，也能使用value（）检索独立的元素属性的值。当然除此之外还有向exist() andnodes() 这样方法，配合语法都以应用，这部分就不再展开讲了，大同小异。有不明白的可以私聊。更多使用方法还请访问MSDN来获取（搜索XQuery language reference）。

版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。

发布者：全栈程序员-用户IM，转载请注明出处：https://javaforall.cn/119739.html原文链接：https://javaforall.cn

【正版授权，激活自己账号】： Jetbrains全家桶Ide使用，1年售后保障，每天仅需1毛

【官方授权 正版激活】： 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...

赞 (0)
全栈程序员-用户IM全栈程序员-用户IM
0 0


相关推荐

  • 计算机ATA考试详细讲解

    计算机ATA考试详细讲解

    计算机ATA考试详细讲解国计算机信息高新技术考试（CITT）是原劳动部根据中央有关稳妥发展劳动力市场、积极进行职业技能鉴定工作的有关精神，为了适应社会发展和科技进步的需要，提高劳动力素质和促进就业，加强计算机信息技术领域新职业、新工种职业技能的培训考核工作，于1996年，以”劳部发[1996]19号”文件《关于开展计算机信息高新技术培训考核工作的通知》，由劳动和社会保障部职业技能鉴定中心，在全国范围内统一组织实施的社会化职业技能考试。整个考试由劳动保障部职业技能鉴定中心负责题库管理、各省（自治区、直辖市）负责考试的组织、考试站负责

    全栈程序员-用户IM 全栈程序员-用户IM
    2022年7月13日
  • scrapy爬虫部署服务器「建议收藏」

    scrapy爬虫部署服务器「建议收藏」

    scrapy爬虫部署服务器「建议收藏」scrapy爬虫部部署服务器时间：2020年5月27日18:28:30作者：钟健记录：scrapy爬虫关键字：scrapyscrapydscrapydweb一、scrapy爬虫部署服务器scrapy通过命令行运行一般只用于测试环境，而用于运用在生产环境则一般都部署在服务器中进行远程操作。scrapy部署服务器有一套完整的开源项目：scrapy+scrapyd(服务端)+scrapy-client(客户端)+scrapydweb1、scrapyd1.介绍Scrapyd是用于部署和运

    全栈程序员-用户IM 全栈程序员-用户IM
    2022年6月18日
  • java8获取以秒单位的时间戳

    java8获取以秒单位的时间戳

    java8获取以秒单位的时间戳importjava.sql.Timestamp;//不带小时，分钟publicstaticlonggetTimeStamp(intlen){              //LocalTime.of(0,0)换成LocalTime.now()可获得完整的时间戳（13位毫秒)longtimestamp=Timestamp.valueOf(LocalDateTim…

    全栈程序员-用户IM 全栈程序员-用户IM
    2022年9月6日
  • 音乐播放器之QQ音乐最新api，亲测可用「建议收藏」

    音乐播放器之QQ音乐最新api，亲测可用「建议收藏」

    音乐播放器之QQ音乐最新api，亲测可用「建议收藏」大家好，前段时间重写了自己的音乐播放器，源码放在github上，源码地址和项目地址下面都有，如果喜欢记得star一下哈。由于之前给大家分享的api虽然可以用，但是版本太旧了，很多也没有了歌词，今天博主给大家分享一个最新的qq音乐api，亲测可用，话不多说直接上代码最新音乐leturl=’https://c.y.qq.com/v8/fcg-bin…

    全栈程序员-用户IM 全栈程序员-用户IM
    2022年6月26日
  • fragment与activity的生命周期方法是一致的吗_请描述activity的生命周期

    fragment与activity的生命周期方法是一致的吗_请描述activity的生命周期

    fragment与activity的生命周期方法是一致的吗_请描述activity的生命周期在学Fragment之前肯定学过了Activity，Activity有属于自己的生命周期，Fragment基本上和activity大体一样，但是有自己特有的生命周期方法，下面我们一起来看一下。说白了主要看两张图，和运行代码：一.Fragment的生命周期二.与Activity生命周期的对比三.场景演示

    全栈程序员-用户IM 全栈程序员-用户IM
    2022年8月16日
  • java截取某个字符后面的字符串_java如何截取字符串

    java截取某个字符后面的字符串_java如何截取字符串

    java截取某个字符后面的字符串_java如何截取字符串提示：java截取某个字符之前或者之后的字符串文章目录一、java截取某个字符之前或者之后的字符串:1.截取”_”之前字符串2.截取”_”之后字符串二、截取正数第二个”_”后面的内容一、java截取某个字符之前或者之后的字符串:1.截取”_”之前字符串代码如下（示例）：//java截取某个字符之前的字符串publicstaticvoidsubstringTest01(){Stringstr=”test_https://www.baidu.com/”;//截

    全栈程序员-用户IM 全栈程序员-用户IM
    2022年10月7日

发表回复

您的电子邮箱地址不会被公开。

关注全栈程序员社区公众号