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

Søren Kongstad · Accepted Answer · 2016-12-20 13:22:39Z

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

edited Dec 20, 2016 at 13:22

answered Dec 20, 2016 at 13:09

Søren Kongstad

1,4409 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

TT. Over a year ago

AFAICT The cast to XML isn't necessary, if you specify FOR XML PATH('Role'), ROOT('Roles'), TYPE'. Reference: TYPE Directive in FOR XML Queries: request that the result of a FOR XML query be returned as xml data type by specifying the TYPE directive.

TT. Over a year ago

PS: You can quick-test at RexTester. See my profile for more interesting stuff.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related