数据库查询XML结构，FOR XML PATH 语句的应用

【转http://it.dianping.com/sql-for-xml-path.htm】

 

FOR XML PATH 语句的应用:

 

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

运行这段脚本，将生成如下结果：

< row >
  < UserID > 1 </ UserID >
  < UserName > a </ UserName >
</ row >
< row >
  < UserID > 2 </ UserID >
  < UserName > b </ UserName >
</ row >

大家可以看到两行数据生成了两个节点，修改一下PATH的参数：

select UserID , UserName from @ TempTable FOR XML PATH ( ' lzy ' )

再次运行上述脚本，将生成如下的结果：

< lzy >
  < UserID > 1 </ UserID >
  < UserName > a </ UserName >
</ lzy >
< lzy >
  < UserID > 2 </ UserID >
  < UserName > b </ UserName >
</ lzy >

可以看到节点变成，其实PATH() 括号内的参数是控制节点名称的，这样的话大家可以看一下如果是空字符串（不是没有参数）会是什么结果?

select UserID , UserName from @ TempTable FOR XML PATH ( '' )

执行上面这段脚本将生成结果：

< UserID > 1 </ UserID >
< UserName > a </ UserName >
< UserID > 2 </ UserID >
< UserName > b </ UserName >

这样就不显示上级节点了，大家知道在 PATH 模式中，列名或列别名被作为 XPath 表达式来处理，也就是说，是列的名字，这样大胆试验一下不给指定列名和别名会是怎么样？

select CAST ( UserID AS varchar ) + '' , UserName + '' from @ TempTable FOR XML PATH ( '' )

运行上面这句将生成结果

1a2b

所有数据都生成一行，而且还没有连接字符，这样的数据可能对大家没有用处，还可以再变化一下：

select CAST ( UserID AS varchar ) + ' , ' , UserName + '' , ' ; ' from @ TempTable FOR XML PATH ( '' )

生成结果

1,a;2,b;

大家现在明白了吧，可以通过控制参数来生成自己想要的结果，例如：

select ' { ' + CAST ( UserID AS varchar ) + ' , ' , ' " ' + UserName + ' " ' , ' } ' from @ TempTable FOR XML PATH ( '' )

生成结果

{1,"a"}{2,"b"}

还可以生成其他格式，大家可以根据自己需要的格式进行组合。

下面是一个数据统计的应用，希望大家可以通过下面的实例想到更多的应用

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