在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