SQL Server FOR XML PATH 语句的应用
来源:互联网 发布:适合矮个子的淘宝店铺 编辑:程序博客网 时间:2024/05/22 13:42
在SQL Server中利用 FOR XML PATH 语句能够把查询的数据生成XML数据,下面是它的一些应用示例。
1
DECLARE
@TempTable
table
(UserID
int
, UserName nvarchar(50));
2
insert
into
@TempTable (UserID,UserName)
values
(1,
'a'
)
3
insert
into
@TempTable (UserID,UserName)
values
(2,
'b'
)
4
5
select
UserID,UserName
from
@TempTable
FOR
XML PATH
运行这段脚本,将生成如下结果:
1
<
row
>
2
<
UserID
>1</
UserID
>
3
<
UserName
>a</
UserName
>
4
</
row
>
5
<
row
>
6
<
UserID
>2</
UserID
>
7
<
UserName
>b</
UserName
>
8
</
row
>
1
大家可以看到两行数据生成了两个节点,修改一下PATH的参数:
1
select
UserID,UserName
from
@TempTable
FOR
XML PATH(
'lzy'
)
再次运行上述脚本,将生成如下的结果:
1
<
lzy
>
2
<
UserID
>1</
UserID
>
3
<
UserName
>a</
UserName
>
4
</
lzy
>
5
<
lzy
>
6
<
UserID
>2</
UserID
>
7
<
UserName
>b</
UserName
>
8
</
lzy
>
1
可以看到节点变成,其实PATH() 括号内的参数是控制节点名称的,这样的话大家可以看一下如果是空字符串(不是没有参数)会是什么结果?
1
select
UserID,UserName
from
@TempTable
FOR
XML PATH(
''
)
执行上面这段脚本将生成结果:
1
<
UserID
>1</
UserID
>
2
<
UserName
>a</
UserName
>
3
<
UserID
>2</
UserID
>
4
<
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"}
还可以生成其他格式,大家可以根据自己需要的格式进行组合。
下面是一个数据统计的应用,希望大家可以通过下面的实例想到更多的应用
01
DECLARE
@T1
table
(UserID
int
, UserName nvarchar(50),CityName nvarchar(50));
02
insert
into
@T1 (UserID,UserName,CityName)
values
(1,
'a'
,
'上海'
)
03
insert
into
@T1 (UserID,UserName,CityName)
values
(2,
'b'
,
'北京'
)
04
insert
into
@T1 (UserID,UserName,CityName)
values
(3,
'c'
,
'上海'
)
05
insert
into
@T1 (UserID,UserName,CityName)
values
(4,
'd'
,
'北京'
)
06
insert
into
@T1 (UserID,UserName,CityName)
values
(5,
'e'
,
'上海'
)
07
08
SELECT
B.CityName,
LEFT
(UserList,LEN(UserList)-1)
FROM
(
09
SELECT
CityName,
10
(
SELECT
UserName+
','
FROM
@T1
WHERE
CityName=A.CityName
FOR
XML PATH(
''
))
AS
UserList
11
FROM
@T1 A
12
GROUP
BY
CityName
13
) B
生成结果(每个城市的用户名)
北京 b,d
上海 a,c,e
---------------------------------------------------------
比如先想实现一个简单的
三条记录(或者更多条)
count price type
1 10 1
2 3 1
3 4 1
4 5 1
5 2 2
6 1 2
我想输出 最后的结果表达式 type1: (1*10)+(2*3)+(3*4)+(4*5)=48
type2: (5*2)+(6*1)=16
这个要怎么做才能分组显示出想要的字符串呢
--------------------------
CREATE TABLE tb
(
pid int,
id int,
[type] int
)
insert tb
select 1, 10, 1 union all
select 2, 3, 1 union all
select 3, 4, 1 union all
select 4, 5, 1 union all
select 5, 2, 2 union all
select 6, 1, 2
go
select [type],stuff((select '+(' + ltrim(pid) + '*' + ltrim(id) + ')' from tb where [type] = t.[type] for xml path('')),1,1,'')
+ ' = ' + (select ltrim(sum(pid*id)) from tb where [type] = t.[type]) as cd
from tb t
group by [type]
drop table tb
/*
type cd
----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 (1*10)+(2*3)+(3*4)+(4*5) = 48
2 (5*2)+(6*1) = 16
(2 行受影响)
-----------------------------------------------------
从这个例子也可以看到,GROUP BY 只能显示 GROUP BY 后面出现的字段,如果想把相关字段
能够在结果中显示出来,就需要 那个组合的字段具有唯一的一值(也就是说不能是个数据集合例如表什么的),所以这个
时候 for xml path就非常有用。
- [转]SQL Server FOR XML PATH 语句的应用
- SQL Server FOR XML PATH 语句的应用---列转行
- SQL Server FOR XML PATH 语句的应用
- SQL Server FOR XML PATH 语句的应用
- SQL Server FOR XML PATH 语句的应用
- SQL Server FOR XML PATH 语句的应用
- SQL Server FOR XML PATH 语句的应用
- SQL Server FOR XML PATH 语句的应用
- SQL Server FOR XML PATH 语句的应用
- SQL Server FOR XML PATH 语句的应用
- SQL Server FOR XML PATH 语句的应用
- SQL Server FOR XML PATH 语句的应用
- SQL Server FOR XML PATH 语句的应用实例
- SQL Server FOR XML PATH 语句的应用
- SQL Server FOR XML PATH 语句的应用
- SQL Server FOR XML PATH 应用
- SQL Server (FOR XML PATH )的应用
- FOR XML PATH 语句的应用
- S3C2440平台下调试u-boot的方法——代码加载
- jQuery 的插件 dataTables
- c++笔试题汇总
- PHP 基础测试!
- win2003+Eclipse+Tomcat+Lomboz的配置总结
- SQL Server FOR XML PATH 语句的应用
- 用ADO操作数据库的方法步骤
- emacs常用命令
- jsp的exception技术整理
- Java2下Applet数字签名具体实现方法
- apue第四章(一)
- DECLARE_DYNAMIC和IMPLEMENT_DYNAMIC宏
- matplotlib中文显示坐标--修改matplotlibrc文件(for windows系统)
- window7硬盘安装全攻略