【T-SQL系列】FOR XML PATH 语句的应用

原文:【T-SQL系列】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

--其实PATH() 括号内的参数是控制节点名称的
SELECT  UserID ,
        UserName
FROM    @TempTable
FOR     XML PATH('lzy')

--这样就不显示上级节点了
SELECT  UserID ,
        UserName
FROM    @TempTable
FOR     XML PATH('')

--大家可以根据自己需要的格式进行组合
SELECT  CAST(UserID AS VARCHAR) + '' ,
        UserName + ''
FROM    @TempTable
FOR     XML PATH('')

SELECT  CAST(UserID AS VARCHAR) + ',' ,
        UserName + '' ,
        ';'
FROM    @TempTable
FOR     XML PATH('')

SELECT  '{' + CAST(UserID AS VARCHAR) + ',' ,
        '"' + UserName + '"' ,
        '}'
FROM    @TempTable
FOR     XML PATH('')

对应结果集：

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

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  *
FROM    @T1

SELECT  CityName ,
        ( SELECT    UserName + ','
          FROM      @T1
          WHERE     CityName = A.CityName
        FOR
          XML PATH('')
        ) AS UserList
FROM    @T1 A
GROUP BY CityName

--生成结果（每个城市的用户名）

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

对应结果集：