SQL FOR XML

简介: 原文:SQL FOR XML摘要:sql中的for xml语法为表转化为xml提供了很好的支持,当然使用同样的程序语言也能够达到同样的效果,但是有了for xml将使得这一切更加的方便。 主要内容: Select 的查询结果会作为行集返回,但是你同样可以在sql中指定for xml子句使得查询作为xml来检索。
原文: SQL FOR XML

摘要:sql中的for xml语法为表转化为xml提供了很好的支持,当然使用同样的程序语言也能够达到同样的效果,但是有了for xml将使得这一切更加的方便。

主要内容:

Select 的查询结果会作为行集返回,但是你同样可以在sql中指定for xml子句使得查询作为xml来检索。在for xml子句中,可以指定以下模式之一:RAW 、AUTO、EXPLICIT和PATH。

RAW模式返回行为元素,每一列的值作为元素的属性;AUTO模式返回表名为节点的元素,每一列的属性作为属性输出;EXPLICIT模式通过SELECT语法定义输出XML结构;PATH模式中列名或列别名作为XPATH表达式来处理。

下面是四种方式输出的效果

RAW模式

 

SELECT TOP 5 ProductName,UnitPrice FROM dbo.Products FOR XML RAW

查询结果

<row ProductName="Chai" UnitPrice="18.0000" />
<row ProductName="Chang" UnitPrice="19.0000" />
<row ProductName="Aniseed Syrup" UnitPrice="10.0000" />
<row ProductName="Chef Anton's Cajun Seasoning" UnitPrice="22.0000" />
<row ProductName="Chef Anton's Gumbo Mix" UnitPrice="21.3500" />

 

AUTO模式

 

SELECT TOP 5 ProductName,UnitPrice FROM dbo.Products FOR XML AUTO

查询结果

<dbo.Products ProductName="Chai" UnitPrice="18.0000" />
<dbo.Products ProductName="Chang" UnitPrice="19.0000" />
<dbo.Products ProductName="Aniseed Syrup" UnitPrice="10.0000" />
<dbo.Products ProductName="Chef Anton's Cajun Seasoning" UnitPrice="22.0000" />
<dbo.Products ProductName="Chef Anton's Gumbo Mix" UnitPrice="21.3500" />

 

EXPLICIT模式

 

SELECT TOP 5 1 AS Tag,0 AS Parent, OrderID AS [Order!1!ID],OrderDate AS [Order!1!Date],CustomerID AS [Order!1!Customer],NULL AS [OrderDetail!2!ProductID],NULL AS [OrderDetail!2!UnitPrice],NULL AS [OrderDetail!2!Quantity] FROM dbo.Orders WHERE dbo.Orders.OrderID='10248'
UNION ALL
SELECT TOP 5 2 AS Tag,1 AS Parent, NULL,NULL ,NULL,ProductID,UnitPrice,Quantity FROM dbo.[Order Details] WHERE OrderID='10248'
FOR XML EXPLICIT

查询结果

<Order ID="10248" Date="1996-07-04T00:00:00" Customer="VINET">
<OrderDetail ProductID="11" UnitPrice="14.0000" Quantity="12" />
<OrderDetail ProductID="42" UnitPrice="9.8000" Quantity="10" />
<OrderDetail ProductID="72" UnitPrice="34.8000" Quantity="5" />
</Order>

 

PATH模式

 

SELECT TOP 5 ProductName AS Name, UnitPrice AS Price FROM dbo.Products FOR XML PATH('Cmj')

查询结果

<Cmj>
<Name>Chai</Name>
<Price>18.0000</Price>
</Cmj>
<Cmj>
<Name>Chang</Name>
<Price>19.0000</Price>
</Cmj>
<Cmj>
<Name>Aniseed Syrup</Name>
<Price>10.0000</Price>
</Cmj>
<Cmj>
<Name>Chef Anton's Cajun Seasoning</Name>
<Price>22.0000</Price>
</Cmj>
<Cmj>
<Name>Chef Anton's Gumbo Mix</Name>
<Price>21.3500</Price>
</Cmj>

 

RAW模式和AUTO模式相对比较简单,灵活性当然也是比较差的。EXPLICIT模式会将查询执行生成的行集转换为XML文档,也就是说结构可以自定义,当然这必须符合一定的格式。就拿上面的语句来说:

Tag指定生成节点的嵌套级别,列名必须为"Tag"例如上面的级别为1。

Parent指定当前Tag的父级层次,列名必须为"Parent",NULL表示顶级。

其他列名,例如[Order!1!ID]分别代表元素名称、Tag标记(也就是层级)、属性名称。例如上面的这一列就代表会在第一级节点中生成一个节点名称为Order的节点并且有一个属性ID。

此外还需要指出的是这个列名还有一个可选部分,完整的形式是[ElementName!TagNumber!AttributeName!Directive],最后一部分Directive是可选的,如果不指定的话默认作为ElementName中的属性名称;当然如果制定了xml、cdata或者element那么它将作为ElementName的一个子元素而不是属性。并且指定了此选项之后AttributeName可以为空。

上面的例子中如果去掉For XML EXPLICIT之后查询到的数据时如下形式:

 sqlFORXML

按照上面的规则不难看出,FOR XML EXPLICIT会按照顺序生成xml:

第一次执行(第一行数据)时tag为1,parent为0,则此时会按照列名创建一个Order节点,并依次创建三个属性:ID、Date和Customer,其值为:10248、1996-07-04 00:00:00.000、VINET。此时不会生成ProductID属性,因为它的Tag为2,需要在第二级节点中创建,其他另个属性也是如此。

第二次执行(第二行数据)时Tag为2,Parent为1,也就是要在Tag为1的节点中创建一个2级节点。然后根据查找列名中Tag为2的列,依次创建ProductID、UnitPrice、Quantity属性,并赋值。

第三次、第四次同第二次执行类似,不同的只是数据而已。

PATH模式应该说是使用率相当高的,原因比较简单,它的使用既简单又灵活。在PATH模式中,节点名称可以使列名也可以通过别名指定又或者根本不指定,不指定的情况下就只显示节点内数据;根节点可以指定也可以不指定,不指定情况下默认为"row",并且根节点指定为""的情况下可以完全取消根节点的显示。有了这一属性就可以使用PATH模式完成很多有用的操作。

没有指定根节点时:

SELECT TOP 5 ProductName FROM dbo.Products FOR XML PATH

 

<row>
<Name>Chai</Name>
<Price>18.0000</Price>
</row>
<row>
<Name>Chang</Name>
<Price>19.0000</Price>
</row>
<row>
<Name>Aniseed Syrup</Name>
<Price>10.0000</Price>
</row>
<row>
<Name>Chef Anton's Cajun Seasoning</Name>
<Price>22.0000</Price>
</row>
<row>
<Name>Chef Anton's Gumbo Mix</Name>
<Price>21.3500</Price>
</row>

 

去掉根节点名称:

SELECT TOP 5 ProductName FROM dbo.Products FOR XML PATH('')

 

<ProductName>Alice Mutton</ProductName>
<ProductName>Aniseed Syrup</ProductName>
<ProductName>Boston Crab Meat</ProductName>
<ProductName>Camembert Pierrot</ProductName>
<ProductName>Carnarvon Tigers</ProductName>

 

去掉根节点和子节点名称:

SELECT TOP 5 ProductName+'' FROM dbo.Products FOR XML PATH('')

 

Alice MuttonAniseed SyrupBoston Crab MeatCamembert PierrotCarnarvon Tigers

 

可以发现去掉节点名称之后for xml path起到了字符串连接的作用,这样一来就可以利用这一特性完成字符串拼接,例如下面的语句就能完成可以将五种产品用","进行分割的功能:

SELECT LEFT(ProductNames,(LEN(ProductNames)-1)) FROM(
SELECT
(SELECT TOP 5 ProductName+',' FROM dbo.Products FOR XML PATH('')) AS ProductNames
) AS T

OK,今天就到这里吧,祝大家元旦快乐!

知识共享许可协议 作品采用知识共享署名 2.5 中国大陆许可协议进行许可,欢迎转载,演绎或用于商业目的。但转载请注明来自崔江涛(KenshinCui),并包含相关链接。
目录
相关文章
|
11月前
|
SQL XML Java
解决关于在idea使用Mybatis时, 编写xml的动态sql语句背景高亮不舒服问题
解决关于在idea使用Mybatis时, 编写xml的动态sql语句背景高亮不舒服问题
1083 0
|
2月前
|
XML SQL 数据格式
XML动态sql查询当前时间之前的信息报错
XML动态sql查询当前时间之前的信息报错
46 2
|
1月前
|
SQL XML Java
mybatis :sqlmapconfig.xml配置 ++++Mapper XML 文件(sql/insert/delete/update/select)(增删改查)用法
当然,这些仅是MyBatis功能的初步介绍。MyBatis还提供了高级特性,如动态SQL、类型处理器、插件等,可以进一步提供对数据库交互的强大支持和灵活性。希望上述内容对您理解MyBatis的基本操作有所帮助。在实际使用中,您可能还需要根据具体的业务要求调整和优化SQL语句和配置。
33 1
|
4月前
|
SQL XML 数据库
后端数据库开发高级之通过在xml文件中映射实现动态SQL
后端数据库开发高级之通过在xml文件中映射实现动态SQL
41 3
|
4月前
|
SQL XML Java
后端数据库开发JDBC编程Mybatis之用基于XML文件的方式映射SQL语句实操
后端数据库开发JDBC编程Mybatis之用基于XML文件的方式映射SQL语句实操
64 3
|
5月前
|
XML Java 数据库连接
Mybatis之简介、使用操作(安装、XML、SqlSession、映射的SQL语句、命名空间、作用域和生命周期)
【1月更文挑战第2天】 MyBatis 是一款优秀的持久层框架 MyBatis 避免了几乎所有的 JDBC 代码和手动设置参数以及获取结果集的过程 MyBatis 可以使用简单的 XML 或注解来配置和映射原生信息,将接口和 Java 的 实体类 【Plain Old Java Objects,普通的 Java对象】映射成数据库中的记录。
153 2
Mybatis之简介、使用操作(安装、XML、SqlSession、映射的SQL语句、命名空间、作用域和生命周期)
|
5月前
|
XML SQL Java
springboot 项目启动报Has been loaded by XML or SqlProvider, ignoring the injection of the SQL的错误的解决方案
springboot 项目启动报Has been loaded by XML or SqlProvider, ignoring the injection of the SQL的错误的解决方案
658 0
|
12月前
|
SQL XML Java
【SQL用法】Mybatis框架中的xml文件中经常使用的sql语句
【SQL用法】Mybatis框架中的xml文件中经常使用的sql语句
107 0
java202304java学习笔记第六十七天-ssm-动态sql-xml完成基本得crud之2
java202304java学习笔记第六十七天-ssm-动态sql-xml完成基本得crud之2
42 0
java202304java学习笔记第六十七天-ssm-动态sql-xml完成基本得crud之2
|
SQL XML 缓存
Mybatis源码分析 2:解析XML并映射到Sql
# XMLStatementBuilder:对单个XNode节点进行解析,得到具体的SqlSource并以此生成MappedStatement ## parseStatementNode方法: ```JAVA private final MapperBuilderAssistant builderAssistant; // 记录了当前mapper的namespace等基础信息 private
170 0
Mybatis源码分析 2:解析XML并映射到Sql