How to select multiple rows as single XML column with T-SQL and XQuery

Question

Given the following dataset, I want the data grouped by Name, but the values of RoleID, RoleName, PermissionID, and PermissionName to be put together as a single XML value:

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

So, the output I am trying to get to, should look something like this:

 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>

I have tried the following, but it creates a Role row for each entry in the dataset:

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

Answer 1

I used your dataset rather as a CTE (foo) and got your required result by using nested selects and Group by. I Used "Name" as the userkey, since there is no ID for users in your example data, but you should be able to get it to work with your query.

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

I have not tested the following Query, but it should be very close to something working on your tables:

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