下面是转载别人的。
——————————————————————————————————————————————
在SQL Server中利用 FOR XML PATH 语句能够把查询的数据生成XML数据,下面是它的一些应用示例。
1
2
3
4
5
|
DECLARE
@TempTable table (UserID int , UserName nvarchar(50));
insert
into @TempTable (UserID,UserName) values (1, 'a' )
insert
into @TempTable (UserID,UserName) values (2, 'b' )
select
UserID,UserName from
@TempTable FOR XML PATH
|
运行这段脚本,将生成如下结果:
1
2
3
4
5
6
7
8
|
< row >
< UserID >1</ UserID >
< UserName >a</ UserName >
</ row >
< row >
< UserID >2</ UserID >
< UserName >b</ UserName >
</ row >
|
1
|
大家可以看到两行数据生成了两个节点,修改一下PATH的参数:
|
1
|
select
UserID,UserName from
@TempTable FOR XML PATH( 'lzy' )
|
再次运行上述脚本,将生成如下的结果:
1
2
3
4
5
6
7
8
|
< lzy >
< UserID >1</ UserID >
< UserName >a</ UserName >
</ lzy >
< lzy >
< UserID >2</ UserID >
< UserName >b</ UserName >
</ lzy >
|
1
|
可以看到节点变成,其实PATH() 括号内的参数是控制节点名称的,这样的话大家可以看一下如果是空字符串(不是没有参数)会是什么结果?
|
1
|
select
UserID,UserName from
@TempTable FOR XML PATH( '' )
|
执行上面这段脚本将生成结果:
1
2
3
4
|
< UserID >1</ UserID >
< UserName >a</ UserName >
< UserID >2</ UserID >
< UserName >b</ UserName >
|
这样就不显示上级节点了,大家知道在 PATH 模式中,列名或列别名被作为 XPath 表达式来处理,也就是说,是列的名字,这样大胆试验一下不给指定列名和别名会是怎么样?
1
|
select
CAST (UserID AS varchar ) + '' ,UserName + '' from @TempTable
FOR XML PATH( '' )
|
运行上面这句将生成结果
1a2b
所有数据都生成一行,而且还没有连接字符,这样的数据可能对大家没有用处,还可以再变化一下:
1
|
select
CAST (UserID AS varchar ) + ',' ,UserName + '' , ';' from @TempTable FOR XML
PATH( '' )
|
生成结果
1,a;2,b;
大家现在明白了吧,可以通过控制参数来生成自己想要的结果,例如:
1
|
select
'{' + CAST (UserID AS varchar ) + ',' , '"' +UserName
+ '"' , '}' from @TempTable FOR XML PATH( '' )
|
生成结果
{1,"a"}{2,"b"}
还可以生成其他格式,大家可以根据自己需要的格式进行组合。
下面是一个数据统计的应用,希望大家可以通过下面的实例想到更多的应用
1
2
3
4
5
6
7
8
9
10
11
12
13
|
DECLARE
@T1 table (UserID
int , UserName nvarchar(50),CityName nvarchar(50));
insert
into @T1 (UserID,UserName,CityName) values (1, 'a' , '上海' )
insert
into @T1 (UserID,UserName,CityName) values (2, 'b' , '北京' )
insert
into @T1 (UserID,UserName,CityName) values (3, 'c' , '上海' )
insert
into @T1 (UserID,UserName,CityName) values (4, 'd' , '北京' )
insert
into @T1 (UserID,UserName,CityName) values (5, 'e' , '上海' )
SELECT
B.CityName, LEFT (UserList,LEN(UserList)-1) FROM (
SELECT
CityName,
( SELECT UserName+ ',' FROM @T1
WHERE CityName=A.CityName FOR XML PATH( '' )) AS UserList
FROM
@T1 A
GROUP
BY CityName
) B
|
生成结果(每个城市的用户名)
北京 b,d
上海 a,c,e
————————————————————————————————————————————————————————————
FOR XML PATH 有的人可能知道有的人可能不知道,其实它就是将查询结果集以XML形式展现,有了它我们可以简化我们的查询语句实现一些以前可能需要借助函数活存储过程来完成的工作。那么以一个实例为主.
一.FOR XML PATH 简单介绍
那么还是首先来介绍一下FOR XML PATH ,假设现在有一张兴趣爱好表(hobby)用来存放兴趣爱好,表结构如下:
接下来我们来看应用FOR XML PATH的查询结果语句如下:
SELECT*FROM@hobbyFORXML 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@hobbyFORXML 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吗?对了就是用它!代码如下:
SELECThobbyIDas'MyCode',hNameas'MyName'FROM@hobbyFORXML
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@hobbyFORXML
PATH('')
没错我们还可以通过符号+号,来对字符串类型字段的输出格式进行定义。结果如下:
那么其他类型的列怎么自定义? 没关系,我们将它们转换成字符串类型就行啦!例如:
SELECT'{'+STR(hobbyID)+'}','['+hName+']'FROM@hobbyFORXML
PATH('')
好的 FOR XML PATH就基本介绍到这里吧,更多关于FOR XML的知识请查阅帮助文档!
接下来我们来看一个FOR XML PATH的应用场景吧!那么开始吧。。。。。。
二.一个应用场景与FOR XML PATH应用
首先呢!我们在增加一张学生表,列分别为(stuID,sName,hobby),stuID代表学生编号,sName代表学生姓名,hobby列存学生的爱好!那么现在表结构如下:
这时,我们的要求是查询学生表,显示所有学生的爱好的结果集,代码如下:
SELECTB.sName,LEFT(StuList,LEN(StuList)-1)ashobbyFROM(
SELECTsName,
(SELECThobby+','FROMstudent
WHEREsName=A.sName
FORXML PATH(''))ASStuList
FROMstudent A
GROUPBYsName
) B
结果如下:
分析:好的,那么我们来分析一下,首先看这句:
SELECThobby+','FROMstudent
WHEREsName=A.sName
FORXML PATH('')
这句是通过FOR XML PATH 将某一姓名如张三的爱好,显示成格式为:“ 爱好1,爱好2,爱好3,”的格式!
那么接着看:
SELECTB.sName,LEFT(StuList,LEN(StuList)-1)ashobbyFROM(
SELECTsName,
(SELECThobby+','FROMstudent
WHEREsName=A.sName
FORXML PATH(''))ASStuList
FROMstudent A
GROUPBYsName
) B
剩下的代码首先是将表分组,在执行FOR XML PATH 格式化,这时当还没有执行最外层的SELECT时查询出的结构为:
可以看到StuList列里面的数据都会多出一个逗号,这时随外层的语句:SELECTB.sName,LEFT(StuList,LEN(StuList)-1)ashobby就是来去掉逗号,并赋予有意义的列名!
分享到:
相关推荐
在SQL Server中利用 FOR XML PATH 语句能够把查询的数据生成XML数据,下面是它的一些应用示例。…………
SQL截取以逗号分隔的字符串SQL截取以逗号分隔的字符串SQL截取以逗号分隔的字符串SQL截取以逗号分隔的字符串
Sql Server 有如下几种聚合函数SUM、AVG、COUNT、COUNT(*)、MAX 和 MIN,但是这些函数都只能聚合数值类型,无法聚合字符串。
NULL 博文链接:https://yufeng521000.iteye.com/blog/2076607
将字符串数据进行聚合,来实现 系统的sum(),count()等效果
sqlserver中根据某个字符切割字符串函数,比如根据逗号切割字符串,结果返回的是一个表值函数,这个函数返回结果字段包含Id和Value,Id为序号,Value为切割后的值
sql server 的聚合函数如 sum() count() 等只能对数字类型的数据进行聚合, 而对于字符串 却是不能的,朋友从网上找了相似的案例 经修改后 做了一下总结, 希望对大家有用!!
SQL 字符串处理函数 获取指定的字符
http://yufeng521000.iteye.com/blog/2076607; 根据这篇BOLG的函数编译的DLL,方便大家直接使用; 里面有两个DLL分别是NET3.5,以及NET4.0,下载后根据BOLG以及数据库版本选择对应的DLL使用;
字符串拼接 聚合函数.rar
sql server拼接字符串查询语句。 普通拼接字符串和拼接某一列的所有值。
SQL截取字符串函数(1、截取从字符串左边开始N个字符 2、截取从字符串右边开始N个字符(例如取字符www.163.com) 3、截取字符串中任意位置及长度 等等)
ms sql server 字符合计,类似sum函数的功能,不过是针对字符型!
用SQL语句截取字符串, string substr ( string string, int start [, int length]) 参数1:处理字符串 参数2:截取的起始位置(第一个字符是从0开始) 参数3:截取的字符数量
SQL字符串函数SQL字符串函数SQL字符串函数SQL字符串函数SQL字符串函数SQL字符串函数SQL字符串函数SQL字符串函数
sql中while循环控制,不需要获取数据到程序语言中处理。 SQL中直接进行json格式字符串处理,省掉数据库和程序直接的调用
NULL 博文链接:https://wuxiubing.iteye.com/blog/1010635
主要用于在数据库设计时需要自己生成定长的字符串作为关键字。这一个用户字符串每次递增而形成新串的数据库函数方法。
将sql sqlser 日期类型转化成各种格式的字符串。
sql 多行合并某一列字符串拼接的示例语句