What is an elegant way to use a column of sql output for the tag of xml output within mssql?

Question

I have a stored procedure that returns xml.

Sample call is

get_organization_user_export_data_permission_procedure
    @organizationId = '64483342-af49-4a04-b25d-ce7346cb5375' 
,   @userId = '5324B48E-B560-4345-B290-12CC72D722FA'

Sample output is

<root>
  <all>false</all>
  <analytics>false</analytics>
  <none>true</none>
</root>

Which is correct.

The base SQL is

select lower([type]) name, 
value permission,
count(permissionType) found 
from [GroupExportRestrictionTypeConstants_RestrictionType_view] export
left join ( 
    select permissionType from [OrganizationUserDataPermission] 
    where organizationId = '64483342-af49-4a04-b25d-ce7346cb5375'
    and userId = '5324B48E-B560-4345-B290-12CC72D722FA' ) permission
on export.value = permission.permissionType
group by [type], value

which returns

name    permission  found
none    0   1
all 1   0
analytics   2   0

The problem is I've hardcoded the element tag names within the stored procedure. This is the stored procedure which shows I've got a separate subquery for each of the possible element tag names.

CREATE PROCEDURE [dbo].[get_organization_user_export_data_permission_procedure]
(
    @organizationId uniqueidentifier
,   @userId uniqueidentifier
)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    if (
        select count(*) from [OrganizationUserDataPermission] 
        where organizationId = @organizationId
        and userId = @userId
    ) = 0 begin
        insert into [OrganizationUserDataPermission] ( id, organizationId, userId, permissionType, createDateTime )
        select newId(),@organizationId organizationId, @userId userId, 0 permissionType, getDate()
    end 

    declare @result XML

    set @result = (
        select '',  
        (
            select case true when 0 then 'false' else 'true' end 'all' from (
                select lower([type]) name, 
                value permission,
                count(permissionType) true 
                from [GroupExportRestrictionTypeConstants_RestrictionType_view] export
                left join ( 
                    select permissionType from [OrganizationUserDataPermission] 
                    where organizationId = @organizationId
                    and userId = @userId ) permission
                on export.value = permission.permissionType
                where value = 1
                group by [type], value
            ) [all]
        ) 'all'
        , (
            select case true when 0 then 'false' else 'true' end 'analytics' from (
                select lower([type]) name, 
                value permission,
                count(permissionType) true 
                from [GroupExportRestrictionTypeConstants_RestrictionType_view] export
                left join ( 
                    select permissionType from [OrganizationUserDataPermission] 
                    where organizationId = @organizationId
                    and userId = @userId ) permission
                on export.value = permission.permissionType
                where value = 2
                group by [type], value
            ) [analytics]
        ) 'analytics'
        , (
            select case true when 0 then 'false' else 'true' end 'none' from (
                select lower([type]) name, 
                value permission,
                count(permissionType) true 
                from [GroupExportRestrictionTypeConstants_RestrictionType_view] export
                left join ( 
                    select permissionType from [OrganizationUserDataPermission] 
                    where organizationId = @organizationId
                    and userId = @userId ) permission
                on export.value = permission.permissionType
                where value = 0
                group by [type], value
            ) [none]
        ) 'none'
        for xml path(''), root('root')
    ) 

    select @result result

END

GO

At the moment if another export permission where introduced I'd have to alter the stored procedure (and the view). Is there an elegant way ... by elegant I mean without using dynamic sql ... to produce the same xml results without knowing beforehand what the permissions might be?

Supporting schema.

The view ... named so that it marries to the c# code it relates to.

CREATE VIEW [dbo].[GroupExportRestrictionTypeConstants_RestrictionType_view] AS
select 'All' [type], 1 [value]
union
select 'Analytics' [type],  2 [value]
union
select 'None' [type], 0 [value]

The table without foreign keys.

CREATE TABLE [dbo].[OrganizationUserDataPermission](
    [Id] [uniqueidentifier] NOT NULL,
    [OrganizationId] [uniqueidentifier] NOT NULL,
    [UserId] [uniqueidentifier] NOT NULL,
    [PermissionType] [int] NOT NULL,
    [CreateDateTime] [datetime] NOT NULL,
 CONSTRAINT [PK_OrganizationUserDataPermission] PRIMARY KEY CLUSTERED 
    (
        [Id] ASC
    )  WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

-- some sample data

insert into [OrganizationUserDataPermission] ( id, organizationId, userId, permissionType, createDateTime )
select newId(),'64483342-af49-4a04-b25d-ce7346cb5375' organizationId, '5324B48E-B560-4345-B290-12CC72D722FA' userId, 1 permissionType, getDate()

Answer 1

I place this as another answer, as it follows a completely different approach:

In general it is impossible, to define a column alias dynamically. The element names in an XML are taken from the column names, so this applies here too.

But

An XML is technically seen a string. Normally I would not advise to build XML on string base, but - as long as you are sure, that your element's names will never contain any forbidden characters, this is a solution, which is truly generically taken from the data given:

DECLARE @tbl TABLE(name VARCHAR(100),permission INT,found BIT);
INSERT INTO @tbl VALUES
 ('none',0,1)
,('all',1,0)
,('analytics',2,0);


SELECT CAST('<root>'
      + (
            SELECT '<' + name + '>' + CASE WHEN found=1 THEN 'true' ELSE 'false' END + '</' + name + '>'
            FROM @tbl
            FOR XML PATH(''),TYPE
         ).value('.','varchar(max)') 
      +'</root>' AS XML)

Answer 2

One thing I can tell you for sure: This is much to complicated!

One thing you could change easily, was to transfer the identical sub-select into one CTE.

But - if I understand this correctly - this can be done much easier:

You tell me

The base SQL is

select lower([type]) name, 
value permission,
count(permissionType) found 
from [GroupExportRestrictionTypeConstants_RestrictionType_view] export
left join ( 
    select permissionType from [OrganizationUserDataPermission] 
    where organizationId = '64483342-af49-4a04-b25d-ce7346cb5375'
    and userId = '5324B48E-B560-4345-B290-12CC72D722FA' ) permission
on export.value = permission.permissionType
group by [type], value

which returns

name    permission  found
none    0   1
all 1   0
analytics   2   0

Assuming, the returned values are OK, you can go with conditional aggregation:

A mock-up table to simulate your final result from above

DECLARE @tbl TABLE(name VARCHAR(100),permission INT,found BIT);
INSERT INTO @tbl VALUES
 ('none',0,1)
,('all',1,0)
,('analytics',2,0);

--The query

SELECT MAX(CASE WHEN(name='all') THEN CASE WHEN found=1 THEN 'true' ELSE 'false' END END) AS [all]
      ,MAX(CASE WHEN(name='analytics') THEN CASE WHEN found=1 THEN 'true' ELSE 'false' END END) AS [analytics]
      ,MAX(CASE WHEN(name='none') THEN CASE WHEN found=1 THEN 'true' ELSE 'false' END END) AS [none]
FROM @tbl
GROUP BY name
FOR XML PATH(''),ROOT('root');

The result

<root>
  <all>false</all>
  <analytics>false</analytics>
  <none>true</none>
</root>

For your case this migth be this

WITH MyQuery aS
(
    select lower([type]) name, 
    value permission,
    count(permissionType) found 
    from [GroupExportRestrictionTypeConstants_RestrictionType_view] export
    left join ( 
        select permissionType from [OrganizationUserDataPermission] 
        where organizationId = '64483342-af49-4a04-b25d-ce7346cb5375'
        and userId = '5324B48E-B560-4345-B290-12CC72D722FA' ) permission
    on export.value = permission.permissionType
    group by [type], value  
)
SELECT MAX(CASE WHEN(name='all') THEN CASE WHEN found=1 THEN 'true' ELSE 'false' END END) AS [all]
      ,MAX(CASE WHEN(name='analytics') THEN CASE WHEN found=1 THEN 'true' ELSE 'false' END END) AS [analytics]
      ,MAX(CASE WHEN(name='none') THEN CASE WHEN found=1 THEN 'true' ELSE 'false' END END) AS [none]
FROM MyQuery
GROUP BY name
FOR XML PATH(''),ROOT('root');