【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

对应结果集：