SQL SERVER 原来还可以这样玩 FOR XML PATH

FOR XML PATH 有的人可能知道有的人可能不知道,其实它就是将查询结果集以XML形式展现,有了它我们可以简化我们的查询语句实现一些以前可能需要借助函数活存储过程来完成的工作。那么以一个实例为主.

一.FOR XML PATH 简单介绍

那么还是首先来介绍一下FOR XML PATH ,假设现在有一张兴趣爱好表(hobby)用来存放兴趣爱好,表结构如下:

       接下来我们来看应用FOR XML PATH的查询结果语句如下:

SELECT * FROM @hobby FOR XML PATH

       结果:

<row>
  <hobbyID>1</hobbyID>
  <hName>爬山</hName>
</row>
<row>
  <hobbyID>2</hobbyID>
  <hName>游泳</hName>
</row>
<row>
  <hobbyID>3</hobbyID>
  <hName>美食</hName>
</row>

      由此可见FOR XML PATH 可以将查询结果根据行输出成XML各式!

      那么,如何改变XML行节点的名称呢?代码如下:     

SELECT * FROM @hobby FOR XML PATH('MyHobby')

      结果一定也可想而知了吧?没错原来的行节点<row> 变成了我们在PATH后面括号()中,自定义的名称<MyHobby>,结果如下:

<MyHobby>
  <hobbyID>1</hobbyID>
  <hName>爬山</hName>
</MyHobby>
<MyHobby>
  <hobbyID>2</hobbyID>
  <hName>游泳</hName>
</MyHobby>
<MyHobby>
  <hobbyID>3</hobbyID>
  <hName>美食</hName>
</MyHobby>

      这个时候细心的朋友一定又会问那么列节点如何改变呢?还记的给列起别名的关键字AS吗?对了就是用它!代码如下:

SELECT hobbyID as 'MyCode',hName as 'MyName' FROM @hobby FOR XML PATH('MyHobby')

      那么这个时候我们列的节点名称也会编程我们自定义的名称 <MyCode>与<MyName>结果如下:

<MyHobby>
  <MyCode>1</MyCode>
  <MyName>爬山</MyName>
</MyHobby>
<MyHobby>
  <MyCode>2</MyCode>
  <MyName>游泳</MyName>
</MyHobby>
<MyHobby>
  <MyCode>3</MyCode>
  <MyName>美食</MyName>
</MyHobby>

    噢! 既然行的节点与列的节点我们都可以自定义,我们是否可以构建我们喜欢的输出方式呢?还是看代码: 

SELECT '[ '+hName+' ]' FROM @hobby FOR XML PATH('')

    没错我们还可以通过符号+号,来对字符串类型字段的输出格式进行定义。结果如下:

[ 爬山 ][ 游泳 ][ 美食 ]

    那么其他类型的列怎么自定义? 没关系,我们将它们转换成字符串类型就行啦!例如:

SELECT '{'+STR(hobbyID)+'}','[ '+hName+' ]' FROM @hobby FOR XML PATH('')

    好的 FOR XML PATH就基本介绍到这里吧,更多关于FOR XML的知识请查阅帮助文档!

    接下来我们来看一个FOR XML PATH的应用场景吧!那么开始吧。。。。。。

        二.一个应用场景与FOR XML PATH应用

首先呢!我们在增加一张学生表,列分别为(stuID,sName,hobby),stuID代表学生编号,sName代表学生姓名,hobby列存学生的爱好!那么现在表结构如下:

        这时,我们的要求是查询学生表,显示所有学生的爱好的结果集,代码如下:

SELECT B.sName,LEFT(StuList,LEN(StuList)-1) as hobby FROM (
SELECT sName,
(SELECT hobby+',' FROM student 
  WHERE sName=A.sName 
  FOR XML PATH('')) AS StuList
FROM student A 
GROUP BY sName
) B

         结果如下:

分析: 好的,那么我们来分析一下,首先看这句:

SELECT hobby+',' FROM student 
  WHERE sName=A.sName 
  FOR XML PATH('')

这句是通过FOR XML PATH 将某一姓名如张三的爱好,显示成格式为:“ 爱好1,爱好2,爱好3,”的格式!

那么接着看:

SELECT B.sName,LEFT(StuList,LEN(StuList)-1) as hobby FROM (
SELECT sName,
(SELECT hobby+',' FROM student 
  WHERE sName=A.sName 
  FOR XML PATH('')) AS StuList
FROM student A 
GROUP BY sName
) B

剩下的代码首先是将表分组,在执行FOR XML PATH 格式化,这时当还没有执行最外层的SELECT时查询出的结构为:

可以看到StuList列里面的数据都会多出一个逗号,这时随外层的语句:SELECT B.sName,LEFT(StuList,LEN(StuList)-1) as hobby  就是来去掉逗号,并赋予有意义的列明!

好啦,太晚啦就说到这里吧!

大于2033个字符会自动换行的问题原因记录在此:

 如果您使用 ExecuteReader 或 BeginExecuteReader 访问 XML 数据,SQL Server 将以多行(每行 2,033 个字符)方式返回长度大于 2,033 个字符的所有 XML 结果。 若要避免发生此行为,请使用 ExecuteXmlReader 或 BeginExecuteXmlReader 读取 FOR XML 查询。 有关更多信息,请参见位于 http://support.microsoft.com 上的 Microsoft 知识库中的文章 Q310378“PRB: XML Data Is Truncated When You Use SqlDataReader”(PRB:使用 SqlDataReader 时 XML 数据被截断)

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏PPV课数据科学社区

【学习】七天搞定SAS(二):基本操作(判断、运算、基本函数)

SAS生成新变量 SAS支持基本的加减乘除,值得一提的是它的**代表指数,而不是^。 * Modify homegarden data set with ass...

3314
来自专栏PPV课数据科学社区

【学习】七天搞定SAS(二):基本操作(判断、运算、基本函数)

? 今天开始注重变量操作。 SAS生成新变量 SAS支持基本的加减乘除,值得一提的是它的**代表指数,而不是^。* Modify homegarden dat...

2864
来自专栏互联网杂技

修改数据表(列操作)

前面有介绍数据的增删改查,是针对具体的数据表格里面的数据; 下面是对列的操作; 修改表名 alter table test rename test1; -...

33911
来自专栏python成长之路

(细节)My SQL中主键为0和主键自排约束的关系

1394
来自专栏大数据挖掘DT机器学习

【知识】SAS学习笔记(1--2)

(1)SAS基本概念 1. SAS数据集 SAS数据集(SAS Datasets)可以看作由若干行和若干列组成的表格,类似于一个矩阵,但各列可以取不同的类型值...

3197
来自专栏文渊之博

关于UNPIVOT 操作符

UNPIVOT 操作符说明 简而言之,UNPIVOT操作符就是取得一个行的数据集合,然后把每一行都转换成多个行数据。为了更好地理解,请看下图: ? 图1 从上...

17010
来自专栏程序员的SOD蜜

调用PostgreSQL存储过程,找不到函数名的问题

PostgreSQL的表,函数名称都是严格区分大小写的,所以在使用的时候没有注意大小写问题容易导致找不到函数名的错误,但最近两天我们发现,如果函数参数使用了自定...

2115
来自专栏小樱的经验随笔

每日一水之strcmp用法

strcmp函数 C/C++函数,比较两个字符串 设这两个字符串为str1,str2, 若str1==str2,则返回零; 若str1<str2,则返回负数; ...

3079
来自专栏抠抠空间

MySQL之表的数据类型

一 介绍 存储引擎决定了表的类型,而表内存放的数据也要有不同的类型,每种数据类型都有自己的宽度,但宽度是可选的 详细参考: http://www.runoob....

3288
来自专栏不想当开发的产品不是好测试

mybatis 使用tips - 使用多个参数

执行如下命令: mvn -Dmybatis.generator.overwrite=true mybatis-generator:generate 可以使用my...

1639

扫描关注云+社区