给定以下数据集,我希望将数据按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
FOR XML PATH('Role'), ROOT('Roles'), TYPE'
,则不需要将其转换为XML。参考:FOR XML查询中的TYPE指令:通过指定TYPE指令请求将FOR XML查询的结果作为xml数据类型返回。 - TT.