文档章节

sqlserver - FOR XML PATH

c
 caiyezi
发布于 2016/11/08 20:24
字数 1011
阅读 2
收藏 0

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  就是来去掉逗号,并赋予有意义的列明!

 

以上转载自:http://www.cnblogs.com/doubleliang/archive/2011/07/06/2098775.html

 

如下应用:

 

city表:

 

user表:

 

查询cityIds对应的city name,如下:

 

解决sql:

SELECT TOP 1000 [id]
,[userName]
,[password]
,[isLock]
,[isAdmin]
,[nickName]
,[createTime]
,[cityIds],(select STUFF((select ',' + Convert(varchar(50),name) FROM city where charindex(','+rtrim(id)+',', ','+cityIds+',')>0 FOR XML PATH('')),1,1,'')) as cityNames
FROM [userInfo]

 

本文转载自:http://www.cnblogs.com/vipzhou/p/5175158.html

共有 人打赏支持
c
粉丝 1
博文 108
码字总数 0
作品 0
西安
程序员
私信 提问
for xml path 应用

读别人写的东西,无意中看到这样一句话 select @str=stuff((select ','+vcproductname from #temptable for xml path('')),1,1,'') 在sqlserver的存储过程中执行了一下可以把临时表#temptabl......

angeljing008
2018/07/03
0
0
SQL Server on Linux 2017(初识 LINUX下的SQL-SERVER功能应用)

安装环境Centos7: 官方安装必要條件: 必须 RHEL 7.3 或 7.4 机至少 2 GB的内存 1、设置mssql_server的YUM官方源: curl -o /etc/yum.repos.d/mssql-server.repo https://packages.microsof...

jwenshan
2018/05/28
0
0
【转】Hibernate常见错误

no bean specialed. 出错了,jsp页面报错。 原因是html:select 标签 中 option bean is null/. Set不能加同一实体 在保存数据的时候循环添加一PO数据到Set,居然最后Set的size()为1。各实体设...

mj4738
2012/01/15
0
0
SQL 2017 SQLPS执行Ad-SqlAvailabilityDatabase异常

SQL Server 2017 SQLPS执行Ad-SqlAvailabilityDatabase遇到问题 这个错误简直逆天,查了下该cmdlet的帮助 常规参数里有Debug。 执行Debug,输出如下: PS C:Windowssystem32> Add-SqlAvailab...

UltraSQL
2018/05/23
0
0
【用jersey构建REST服务】系列文章

1.用Jersey构建RESTful服务1--HelloWorldhttp://www.waylau.com/jersey-restful-helloworld/2.用Jersey构建RESTful服务2--JAVA对象转成XML输出http://www.waylau.com/jersey-restful-java-x......

waylau
2014/08/23
0
1

没有更多内容

加载失败,请刷新页面

加载更多

如何利用 Data too long for column 进行一些简单的攻击

1: 前提条件, 后台没有校验长度和频率校验,也没有CSRF校验,直接往数据库插入数据,此时很容易触发Data too long for column错误(一般抛出异常都挺浪费资源的,耗时,耗内存,耗cpu),一旦...

专业写BUG的程序员
28分钟前
5
0
RMAN命令详解

一、list命令 1. List 当前RMAN所备份的数据库: 1.1. list backup summary; --概述可用的备份 list incarnation;--汇总查询 --如果备份文件多的话多用这两个list命令可以对备份文件有个总体...

突突突酱
31分钟前
2
0
简单聊聊Linux学习经历

学习,是我们一生中都规避不了的一个话题,人的一生中都是在不断的学习,无论是功成名就的人士,还是一无是处的小混混,始终都处在一个不断学习的环境中,只是学习的内容千差万别,有的人是为...

问题终结者
39分钟前
2
0
Mysql高级 (2)——sql性能调优

sql 性能下降的原因 sql service的解析顺序 sql 的7中join

小小小施爷
43分钟前
3
0
MaxCompute用户初体验

作为一名初次使用MaxCompute的用户,我体会颇深。MaxCompute 开箱即用,拥有集成化的操作界面,你不必关心集群搭建、配置和运维工作。仅需简单的点击鼠标,几步操作,就可以在MaxCompute中上...

阿里云官方博客
44分钟前
1
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部