如何使用T-SQL和XQuery选择多行作为单个XML列

Question

如何使用T-SQL和XQuery选择多行作为单个XML列

3

给定以下数据集，我希望将数据按Name分组，但RoleID、RoleName、PermissionID和PermissionName的值被合并为一个单独的XML值：

Name            RoleID      RoleName      PermissionID PermissionName
--------------- ----------- ------------- ------------ ---------------
User 1          2           Super User    1            View
User 1          2           Super User    2            Create
User 1          2           Super User    3            Edit
User 1          2           Super User    4            Delete
User 1          3           Report User   17           Execute

因此，我试图得到的输出应该类似于这样：

 Name        Roles
------------ -------------------------------------------------------
 User 1      <Roles>
                <Role id="2" name="Super User">
                    <Permissions>
                        <Permission id="1" name="View" />
                        <Permission id="2" name="Create" />
                        <Permission id="3" name="Edit" />
                        <Permission id="4" name="Delete" />
                    </Permissions>
                </Role>
                <Role id="3" name="Report User">
                    <Permissions>
                        <Permission id="17" name="Execute" />
                    </Permissions>
                </Role>
            </Roles>

我尝试了以下方法，但它会为数据集中的每个条目创建一个Role行：

SELECT
      U.[ID] as [Name]
    , CONVERT(xml, (
        SELECT
            R.[ID] as '@id'
          , R.[Name] as '@name'
          , CONVERT(xml, (
              SELECT
                  P.[ID] as '@id'
                , P.[Name] as '@name'
              FOR XML PATH('Permission')
            )) as [Permissions]
        FOR XML PATH('Role'), ROOT('Roles')
      )) as [Roles]
FROM User U
    LEFT JOIN UserRoles UR ON
        U.[ID] = UR.[UserID]
    LEFT JOIN Role R ON
        UR.[RoleID] = R.[ID]
    LEFT JOIN RolePermissions RP ON
        R.[ID] = RP.[RoleID]
    LEFT JOIN Permission P ON
        RP.[PermissionID] = P.[ID]
WHERE
    U.[ID] = 1234

- Rudolf Lamprecht

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Søren Kongstad · Accepted Answer

我将你的数据集用作CTE（foo），通过使用嵌套查询和Group by获得了所需结果。由于你的示例数据中没有用户ID，因此我使用“Name”作为用户键，但是你应该能够通过你的查询使它起作用。

with foo as
(
select
*
from
(values
('User 1','2',' Super User','1','View')
,('User 1','2',' Super User','2','Create')
,('User 1','2',' Super User','3','Edit')
,('User 1','2',' Super User','4','Delete')
,('User 1','3',' Report User','17',' Execute')
) foo(Name,RoleID,RoleName,PermissionID,PermissionName)
)

select
    Name,
    cast((
    SELECT
        B.RoleID as '@id'
        ,B.RoleName as '@name'
        ,cast((select PermissionID as '@id',PermissionName as '@name'
        from Foo C
        where c.RoleID=b.RoleID
              and c.Name=a.Name
        for xml path('Permission')
      ) as xml) Permissions
    FROM foo B
    group by 
        B.RoleID
        ,B.RoleName
    for xml path('Role'),root('Roles')
    ) as xml) Roles
from
foo a
group by Name

我还没有测试以下查询语句，但它应该非常接近能在您的表上工作的内容：

select
    U.ID as [Name],
    cast((
    SELECT
        R.[ID] as '@id'
        , R.[Name] as '@name'
        ,cast(
        (select 
            P.[ID] as '@id'
            , P.[Name] as '@name'
        from RolePermissions RP
            LEFT JOIN Permission P ON
            RP.[PermissionID] = P.[ID]
        where R.[ID] = RP.[RoleID]
        for xml path('Permission')
      ) as xml) [Permissions]
    FROM UserRoles UR
        Left Join Role R
        ON UR.[RoleID] = R.[ID]
        where U.[ID] = UR.[UserID]
    group by 
        R.[ID]
        ,R.[Name]
    for xml path('Role'),root('Roles')
    ) as xml) [Roles]
from
User U
group by ID
WHERE
    U.[ID] = 1234