SQL Server 2008 对XML 数据类型操作

本文涉及的产品
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
云数据库 RDS SQL Server,基础系列 2核4GB
简介: 原文 http://www.cnblogs.com/qinjian123/p/3240702.html 一、前言 从 SQL Server 2005 开始,就增加了 xml 字段类型,也就是说可以直接把 xml 内容存储在该字段中,并且 SQL Server 会把它当作 xml 来对待,而不是当作 varchar 来对待。

原文 http://www.cnblogs.com/qinjian123/p/3240702.html

一、前言

从 SQL Server 2005 开始,就增加了 xml 字段类型,也就是说可以直接把 xml 内容存储在该字段中,并且 SQL Server 会把它当作 xml 来对待,而不是当作 varchar 来对待。

随着SQL Server 对XML字段的支持,相应的,T-SQL语句也提供了大量对XML操作的功能来配合SQL Server中XML字段的使用。本文主要说明如何使用SQL语句对XML进行操作。

二、定义XML字段

          在进行数据库的设计中,我们可以在表设计器中,很方便的将一个字段定义为XML类型。需要注意的是,XML字段不能用来作为主键或者索引键。同样,我们也 可以使用SQL语句来创建使用XML字段的数据表,下面的语句创建一个名为“docs”的表,该表带有整型主键“pk”和非类型化的 XML 列“xCol”:

CREATE TABLE docs (pk INT PRIMARY KEY, xCol XML not null)

XML类型除了在表中使用,还可以在存储过程、事务、函数等中出现。下面我们来完成我们对XML操作的第一步,使用SQL语句定义一个XML类型的数据,并为它赋值:

set @xmlDoc='<?xml version="1.0" ?>

              <books>

                            <book id="0001">

<title>C Program</title>

<author>David</author>

<price>21</price>

  </book>

  <book id="0002">

<title>你必须知道的.NET</title>

<author>王涛</author>

<price>79</price>

 </book>

      </books>'

select @xmlDoc 

三、XML字段注意点

  • SQL Server 中以 Unicode(UTF-16) 来存储 XML 数据。
  • XML 字段最多可存储 2G 的数据。
  • 可以像插入字符串一样向 XML 字段写入内容。
  • 当在 xml 数据类型实例中存储 XML 数据时,不会保留 XML 声明(如 <?xml version='1.0'?>)。
  • 插入的 xml 内容的属性的顺序可能会与原 xml 实例的顺序变化。
  • 不保留属性值前后的单引号和双引号。
  • 不保留命名空间前缀。
  • 可以对 XML 字段中的 XML 内容建立索引。
  • 可以对 XML 字段中的 XML 内容建立约束,比如 age 节点必须大于等于 18。
  • 可以通过创建架构来对 XML 进行类型化,比如让 xml 内容的 <user> 节点下面必须有 <fullname> 节点。

四、查询操作

在定义了一个XML类型的数据之后,我们最常用的就是查询操作,下面我们来介绍如何使用SQL语句来进行查询操作的。

          在T-Sql中,提供了两个对XML类型数据进行查询的函数,分别是query(xquery)和value(xquery, dataType),其中,query(xquery)得到的是带有标签的数据,而value(xquery, dataType)得到的则是标签的内容。接下类我们分别使用这两个函数来进行查询。

1、使用query(xquery) 查询

我们需要得到书的标题(title),使用query(xquery)来进行查询,查询语句为:

select @xmlDoc.query('(books/book/title)[1]')

运行结果如图:

2、使用value(xquery, dataType) 查询

同样是得到书的标题,使用value函数,需要指明两个参数,一个为xquery, 另一个为得到数据的类型。看下面的查询语句:

select @xmlDoc.value('(books/book/title)[1]', 'nvarchar(max)')

运行结果如图:

 

3、查询属性值

无论是使用query还是value,都可以很容易的得到一个节点的某个属性值,例如,我们很希望得到book节点的id,我们这里使用value方法进行查询,语句为:

select @xmlDoc.value('(books/book/@id)[1]', 'nvarchar(max)')

运行结果如图:

4、使用xpath进行查询

xpath是.net平台下支持的,统一的Xml查询语句。使用XPath可以方便的得到想要的节点,而不用使用where语句。例如,

--得到id为0002的book节点

select @xmlDoc.query('(/books/book[@id="0002"])')

上面的语句可以独立运行,它得到的是id为0002的节点。运行结果如下

五、修改操作

         SQL的修改操作包括更新和删除。SQL提供了modify()方法,实现对Xml的修改操作。modify方法的参数为XML修改语言。XML修改语言类似于SQL 的Insert、Delete、UpDate,但并不一样。

1、修改节点值

我们希望将id为0001的书的价钱(price)修改为100, 我们就可以使用modify方法。代码如下:

set @xmlDoc.modify('replace value of (/books/book[@id=0001]/price/text())[1] with "100"')

--得到id为0001的book节点

select @xmlDoc.query('(/books/book[@id="0001"])')

注意:modify方法必须出现在set的后面。运行结果如图:

 

2、删除节点

接下来我们来删除id为0002的节点,代码如下:

--删除节点id为0002的book节点

set @xmlDoc.modify('delete /books/book[@id=0002]')

select @xmlDoc

运行结果如图:

3、添加节点

很多时候,我们还需要向xml里面添加节点,这个时候我们一样需要使用modify方法。下面我们就向id为0001的book节点中添加一个ISBN节点,代码如下:

--添加节点

set @xmlDoc.modify('insert <isbn>78-596-134</isbn> before (/books/book[@id=0001]/price)[1]')

select @xmlDoc.query('(/books/book[@id="0001"]/isbn)')

运行结果如图:

4、添加和删除属性

当你学会对节点的操作以后,你会发现,很多时候,我们需要对节点进行操作。这个时候我们依然使用modify方法,例如,向id为0001的book节点中添加一个date属性,用来存储出版时间。代码如下:

--添加属性

set @xmlDoc.modify('insert attribute date{"2008-11-27"} into (/books/book[@id=0001])[1]')

select @xmlDoc.query('(/books/book[@id="0001"])')

运行结果如图:

如果你想同时向一个节点添加多个属性,你可以使用一个属性的集合来实现,属性的集合可以写成:(attribute date{"2008-11-27"}, attribute year{"2008"}),你还可以添加更多。这里就不再举例了。

5、删除属性

删除一个属性,例如删除id为0001 的book节点的id属性,我们可以使用如下代码:

--删除属性

set @xmlDoc.modify('delete  books/book[@id="0001"]/@id')

select @xmlDoc.query('(/books/book)[1]')

运行结果如图:

6、修改属性

修改属性值也是很常用的,例如把id为0001的book节点的id属性修改为0005,我们可以使用如下代码:

--修改属性

set @xmlDoc.modify('replace value of ( books/book[@id="0001"]/@id)[1] with "0005"')

select @xmlDoc.query('(/books/book)[1]')

运行结果如图:

经过上面的学习,相信你已经可以很好的在SQL中使用Xml类型了,下面是我们没 有提到的:exist()方法,用来判断指定的节点是否存在,返回值为true或false; nodes()方法,用来把一组由一个查询返回的节点转换成一个类似于结果集的表中的一组记录行。 你可以去MSDN查阅  http://msdn.microsoft.com/zh-cn/library/ms190798.aspx

相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS&nbsp;SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/sqlserver
目录
相关文章
|
1天前
|
SQL 安全 数据处理
揭秘数据脱敏神器:Flink SQL的神秘力量,守护你的数据宝藏!
【9月更文挑战第7天】在大数据时代,数据管理和处理尤为重要,尤其在保障数据安全与隐私方面。本文探讨如何利用Flink SQL实现数据脱敏,为实时数据处理提供有效的隐私保护方案。数据脱敏涉及在处理、存储或传输前对敏感数据进行加密、遮蔽或替换,以遵守数据保护法规(如GDPR)。Flink SQL通过内置函数和表达式支持这一过程。
8 2
|
7天前
|
SQL XML Java
mybatis :sqlmapconfig.xml配置 ++++Mapper XML 文件(sql/insert/delete/update/select)(增删改查)用法
当然,这些仅是MyBatis功能的初步介绍。MyBatis还提供了高级特性,如动态SQL、类型处理器、插件等,可以进一步提供对数据库交互的强大支持和灵活性。希望上述内容对您理解MyBatis的基本操作有所帮助。在实际使用中,您可能还需要根据具体的业务要求调整和优化SQL语句和配置。
14 1
|
7天前
|
SQL 存储 数据处理
"SQL触发器实战大揭秘:一键解锁数据自动化校验与更新魔法,让数据库管理从此告别繁琐,精准高效不再是梦!"
【8月更文挑战第31天】在数据库管理中,确保数据准确性和一致性至关重要。SQL触发器能自动执行数据校验与更新,显著提升工作效率。本文通过一个员工信息表的例子,详细介绍了如何利用触发器自动设定和校验薪资,确保其符合业务规则。提供的示例代码展示了在插入新记录时如何自动检查并调整薪资,以满足最低标准。这不仅减轻了数据库管理员的负担,还提高了数据处理的准确性和效率。触发器虽强大,但也需谨慎使用,以避免复杂性和性能问题。
18 1
|
7天前
|
Java 网络架构 数据格式
Struts 2 携手 RESTful:颠覆传统,重塑Web服务新纪元的史诗级组合!
【8月更文挑战第31天】《Struts 2 与 RESTful 设计:构建现代 Web 服务》介绍如何结合 Struts 2 框架与 RESTful 设计理念,构建高效、可扩展的 Web 服务。Struts 2 的 REST 插件提供简洁的 API 和约定,使开发者能快速创建符合 REST 规范的服务接口。通过在 `struts.xml` 中配置 `&lt;rest&gt;` 命名空间并使用注解如 `@Action`、`@GET` 等,可轻松定义服务路径及 HTTP 方法。
22 0
|
7天前
|
测试技术 Java
全面保障Struts 2应用质量:掌握单元测试与集成测试的关键策略
【8月更文挑战第31天】Struts 2 的测试策略结合了单元测试与集成测试。单元测试聚焦于单个组件(如 Action 类)的功能验证,常用 Mockito 模拟依赖项;集成测试则关注组件间的交互,利用 Cactus 等框架确保框架拦截器和 Action 映射等按预期工作。通过确保高测试覆盖率并定期更新测试用例,可以提升应用的整体稳定性和质量。
16 0
|
7天前
|
数据库 Java 监控
Struts 2 日志管理化身神秘魔法师,洞察应用运行乾坤,演绎奇幻篇章!
【8月更文挑战第31天】在软件开发中,了解应用运行状况至关重要。日志管理作为 Struts 2 应用的关键组件,记录着每个动作和决策,如同监控摄像头,帮助我们迅速定位问题、分析性能和使用情况,为优化提供依据。Struts 2 支持多种日志框架(如 Log4j、Logback),便于配置日志级别、格式和输出位置。通过在 Action 类中添加日志记录,我们能在开发过程中获取详细信息,及时发现并解决问题。合理配置日志不仅有助于调试,还能分析用户行为,提升应用性能和稳定性。
19 0
|
7天前
|
Java 测试技术 容器
从零到英雄:Struts 2 最佳实践——你的Web应用开发超级变身指南!
【8月更文挑战第31天】《Struts 2 最佳实践:从设计到部署的全流程指南》深入介绍如何利用 Struts 2 框架从项目设计到部署的全流程。从初始化配置到采用 MVC 设计模式,再到性能优化与测试,本书详细讲解了如何构建高效、稳定的 Web 应用。通过最佳实践和代码示例,帮助读者掌握 Struts 2 的核心功能,并确保应用的安全性和可维护性。无论是在项目初期还是后期运维,本书都是不可或缺的参考指南。
15 0
|
7天前
|
测试技术 Java
揭秘Struts 2测试的秘密:如何打造无懈可击的Web应用?
【8月更文挑战第31天】在软件开发中,确保代码质量的关键在于全面测试。对于基于Struts 2框架的应用,结合单元测试与集成测试是一种有效的策略。单元测试聚焦于独立组件的功能验证,如Action类的执行逻辑;而集成测试则关注组件间的交互,确保框架各部分协同工作。使用JUnit进行单元测试,可通过简单示例验证Action类的返回值;利用Struts 2 Testing插件进行集成测试,则可模拟HTTP请求,确保Action方法正确处理请求并返回预期结果。这种结合测试的方法不仅提高了代码质量和可靠性,还保证了系统各部分按需协作。
|
7天前
|
SQL 数据管理 数据库
SQL中外键:维护数据完整性的关键
【8月更文挑战第31天】
20 0
下一篇
DDNS