UFN_SECURITY_SYSTEMROLE_TOXMLDOCUMENT

        CREATE FUNCTION dbo.UFN_SECURITY_SYSTEMROLE_TOXMLDOCUMENT(@SYSTEMROLEID uniqueidentifier)
        returns xml
        as
        /*
        Generates an Xml document based on assigned permissions/queryviews/tasks that can be fed to USP_SECURITY_SYSTEMROLE_LOADFROMXMLDOCUMENT
        */

        BEGIN

        declare @r xml;

        declare @recordInfo xml;
        set @recordInfo=
        (
        select ID,[NAME] as [Name], DESCRIPTION as Description

        from dbo.SYSTEMROLE where ID=@SYSTEMROLEID
        for xml raw ('RecordInformation'),type,elements
        );

        declare @tasks xml;
        set @tasks=(
        select TASKID AS TaskID, DISPLAYONHOMEPAGE as DisplayOnHomePage
        from dbo.SYSTEMROLETASK
        WHERE SYSTEMROLEID=@SYSTEMROLEID
        order by convert(nvarchar(36), TASKID)
         for xml raw('Task'),type,elements,root('Tasks')
        )
        ;

        declare @featurePerms xml;

        declare @forms xml;
        set @forms=(
        select DATAFORMINSTANCECATALOGID AS FeatureID,
        case GRANTORDENY WHEN 1 THEN 'Grant' ELSE 'Deny' END as GrantState
        from dbo.SYSTEMROLEPERM_DATAFORMINSTANCE
        WHERE SYSTEMROLEID=@SYSTEMROLEID
        order by convert(nvarchar(36), DATAFORMINSTANCECATALOGID)
         for xml raw('DataForm'),type,elements,root('DataForms')
        )
        ;

        declare @recordOps xml;
        set @recordOps=(
        select RECORDOPERATIONCATALOGID AS FeatureID,
        case GRANTORDENY WHEN 1 THEN 'Grant' ELSE 'Deny' END as GrantState
        from dbo.SYSTEMROLEPERM_RECORDOPERATION
        WHERE SYSTEMROLEID=@SYSTEMROLEID
        order by convert(nvarchar(36), RECORDOPERATIONCATALOGID)
         for xml raw('RecordOperation'),type,elements,root('RecordOperations')
        )
        ;

        declare @dataLists xml;
        set @dataLists=(
        select DATALISTCATALOGID AS FeatureID,
        case GRANTORDENY WHEN 1 THEN 'Grant' ELSE 'Deny' END as GrantState
        from dbo.SYSTEMROLEPERM_DATALIST
        WHERE SYSTEMROLEID=@SYSTEMROLEID
        order by convert(nvarchar(36), DATALISTCATALOGID)
         for xml raw('DataList'),type,elements,root('DataLists')
        )
        ;

        declare @dash xml;
        set @dash=(
        select DASHBOARDCATALOGID AS FeatureID,
        case GRANTORDENY WHEN 1 THEN 'Grant' ELSE 'Deny' END as GrantState
        from dbo.SYSTEMROLEPERM_DASHBOARD
        WHERE SYSTEMROLEID=@SYSTEMROLEID
        order by convert(nvarchar(36), DASHBOARDCATALOGID)
         for xml raw('Dashboard'),type,elements,root('Dashboards')
        )
        ;

        declare @busproc xml;
        set @busproc=(
        select BUSINESSPROCESSCATALOGID AS FeatureID,
        case GRANTORDENY WHEN 1 THEN 'Grant' ELSE 'Deny' END as GrantState
        from dbo.SYSTEMROLEPERM_BUSINESSPROCESS
        WHERE SYSTEMROLEID=@SYSTEMROLEID
        order by convert(nvarchar(36), BUSINESSPROCESSCATALOGID)
         for xml raw('BusinessProcess'),type,elements,root('BusinessProcesses')
        )
        ;

        declare @queryviews xml;
        set @queryviews=(
        select QUERYVIEWCATALOGID AS FeatureID,
        case GRANTORDENY WHEN 1 THEN 'Grant' ELSE 'Deny' END as GrantState
        from dbo.SYSTEMROLEPERM_QUERYVIEW
        WHERE SYSTEMROLEID=@SYSTEMROLEID
        order by convert(nvarchar(36), QUERYVIEWCATALOGID)
         for xml raw('QueryView'),type,elements,root('QueryViews')
        )
        ;

        declare @smartqueries xml;
        set @smartqueries=(
        select SMARTQUERYCATALOGID as FeatureID,
        case GRANTORDENY WHEN 1 THEN 'Grant' ELSE 'Deny' END as GrantState
        from dbo.SYSTEMROLEPERM_SMARTQUERY
        where SYSTEMROLEID=@SYSTEMROLEID
        order by convert(nvarchar(36), SMARTQUERYCATALOGID)
        for xml raw('SmartQuery'),type,elements,root('SmartQueries')
        )
        ;

        declare @kpis xml;
        set @kpis=(
        select KPICATALOGID as FeatureID,
        case GRANTORDENY WHEN 1 THEN 'Grant' ELSE 'Deny' END as GrantState
        from dbo.SYSTEMROLEPERM_KPI
        where SYSTEMROLEID=@SYSTEMROLEID
        order by convert(nvarchar(36), KPICATALOGID)
        for xml raw('Kpi'),type,elements,root('Kpis')
        )
        ;

        declare @reportparams xml;
        set @reportparams=(
        select REPORTCATALOGID as FeatureID,
        case GRANTORDENY WHEN 1 THEN 'Grant' ELSE 'Deny' END as GrantState
        from dbo.SYSTEMROLEPERM_REPORT
        where SYSTEMROLEID=@SYSTEMROLEID
        order by convert(nvarchar(36), REPORTCATALOGID)
        for xml raw('ReportParam'),type,elements,root('ReportParams')
        )
        ;

        declare @searchlistperms xml;
        set @searchlistperms=(
        select SEARCHLISTCATALOGID as FeatureID,
        case GRANTORDENY WHEN 1 THEN 'Grant' ELSE 'Deny' END as GrantState
        from dbo.SYSTEMROLEPERM_SEARCHLIST
        where SYSTEMROLEID=@SYSTEMROLEID
        order by convert(nvarchar(36), SEARCHLISTCATALOGID)
        for xml raw('SearchList'),type,elements,root('SearchLists')
        )
        ;


        declare @mapentityperms xml;
        set @mapentityperms=(
        select MAPENTITYCATALOGID as FeatureID,
        case GRANTORDENY WHEN 1 THEN 'Grant' ELSE 'Deny' END as GrantState
        from dbo.SYSTEMROLEPERM_MAPENTITY
        where SYSTEMROLEID=@SYSTEMROLEID
        for xml raw('MapEntity'),type,elements,root('MapEntities')
        )
        ;

declare @sysprivperms xml;
set @sysprivperms=(
select SYSTEMPRIVILEGECATALOGID as FeatureID,
case GRANTORDENY WHEN 1 THEN 'Grant' ELSE 'Deny' END as GrantState
from dbo.SYSTEMROLEPERM_SYSTEMPRIVILEGE
where SYSTEMROLEID=@SYSTEMROLEID
order by convert(nvarchar(36), SYSTEMPRIVILEGECATALOGID)
for xml raw('SysPriv'),type,elements,root('SysPrivs')
)
;

set @featureperms='<FeaturePerms>' 
+ coalesce(CAST( @forms AS NVARCHAR(MAX) ),'')+
+ coalesce(CAST( @recordOps AS NVARCHAR(MAX) ),'')+
+ coalesce(CAST( @dataLists AS NVARCHAR(MAX) ),'')+
+ coalesce(CAST( @dash AS NVARCHAR(MAX) ),'')+
+ coalesce(CAST( @busproc AS NVARCHAR(MAX) ),'')+
+ coalesce(CAST( @queryviews AS NVARCHAR(MAX) ),'')+
+ coalesce(CAST( @smartqueries AS NVARCHAR(MAX) ),'')+
+ coalesce(CAST( @kpis AS NVARCHAR(MAX) ),'')+
+ coalesce(CAST( @reportparams AS NVARCHAR(MAX) ),'')+
+ coalesce(CAST( @searchlistperms AS NVARCHAR(MAX) ),'')+
+ coalesce(CAST( @mapentityperms AS NVARCHAR(MAX) ),'')+
+ coalesce(CAST( @sysprivperms AS NVARCHAR(MAX) ),'')+
 '</FeaturePerms>'


        declare @codetableperms xml;
        set @codetableperms = (
        select CODETABLECATALOGID as CodeTableID,
        replace(ADDPERMISSION, ' ', '') as AddPermission,
        replace(UPDATEPERMISSION, ' ', '') as UpdatePermission,
        replace(DELETEPERMISSION, ' ', '') as DeletePermission
        from dbo.SYSTEMROLEPERM_CODETABLE
        where SYSTEMROLEID=@SYSTEMROLEID
        order by convert(nvarchar(36), CODETABLECATALOGID)
        for xml raw('CodeTable'),type,elements,root('CodeTables')
        )
        ;

/*
        declare @constitsecurityringfence xml;
        declare @recsecuritymode bit;

        select 
            @recsecuritymode = RECORDSECURITYMODE
        from 
            dbo.SYSTEMROLE
        where 
            ID=@SYSTEMROLEID;

        if @recsecuritymode = 0 begin
            set @constitsecurityringfence = (
                select
                CSR.NAME Name,
        CSR.DESCRIPTION Description
        from dbo.SYSTEMROLE_CONSTIT_SECURITY_ATTRIBUTE SRCSR
        inner join dbo.CONSTIT_SECURITY_ATTRIBUTE CSR on CSR.ID = SRCSR.CONSTIT_SECURITY_ATTRIBUTEID
        where SRCSR.ID=@SYSTEMROLEID
        order by convert(nvarchar(36), CSR.NAME)
        for xml raw('ConstitSecurityRingFence'),type,elements
        )
        ;
        end
        else -- Record security mode is set to non-assigned constituents
        begin
        set @constitsecurityringfence = (
        select
        'Records with no groups assigned' Name,
        'A user in this role will be able to access any Constituent record that has no security group assigned using the Constituent related features granted to this role.' Description
        for xml raw('ConstitSecurityRingFence'),type,elements
        )
        ;
        end
*/

        declare @batchtype xml;
        set @batchtype = (
        select SRP.BATCHTYPECATALOGID as BatchTypeID,
        case GRANTORDENY WHEN 1 THEN 'Grant' ELSE 'Deny' END as GrantState
        from dbo.SYSTEMROLEPERM_BATCHTYPE SRP
        WHERE SYSTEMROLEID=@SYSTEMROLEID
        order by convert(nvarchar(36), SRP.BATCHTYPECATALOGID)
         for xml raw('BatchType'),type,elements,root('BatchTypes')
        )
        ;

        declare @kpiinstanceperms xml;
        set @kpiinstanceperms = (
        select KPIINSTANCEID as KpiInstanceID
        from dbo.SYSTEMROLEKPIINSTANCE
        where SYSTEMROLEID=@SYSTEMROLEID
        order by convert(nvarchar(36), KPIINSTANCEID)
        for xml raw('KpiInstance'),type,elements,root('KpiInstances')
        )
        ;

        declare @codetableentryperms xml;
        set @codetableentryperms = (
        select SRP.CODETABLECATALOGID as CodeTableCatalogID,
        SRP.CODETABLEENTRYID as CodeTableEntryID,
        case ISDENIED WHEN 0 THEN 'Grant' ELSE 'Deny' END as GrantState
        from dbo.SYSTEMROLEPERM_CODETABLEENTRY SRP
        WHERE SYSTEMROLEID=@SYSTEMROLEID
        order by convert(nvarchar(36), SRP.CODETABLEENTRYID)
         for xml raw('CodeTableEntry'),type,elements,root('CodeTableEntries')
        )
        ;

        declare @featuregroupperms xml;
        set @featuregroupperms = (
        select FEATUREGROUPCATALOGID as FeatureGroupCatalogID,
        case GRANTORDENY WHEN 1 THEN 'Grant' ELSE 'Deny' END as GrantState
        from dbo.SYSTEMROLEPERM_FEATUREGROUP
        WHERE SYSTEMROLEID=@SYSTEMROLEID
        order by convert(nvarchar(36), FEATUREGROUPCATALOGID)
         for xml raw('FeatureGroup'),type,elements,root('FeatureGroups')
        )
        ;

        /*
        Site security has been moved down to the system role application user level
        declare @sitesecurity xml;
        declare @sitesecuritymode bit;

        select @sitesecuritymode = SITESECURITYMODE
        from dbo.SYSTEMROLE
        where ID = @SYSTEMROLEID;

        if @sitesecuritymode = 0 
            begin
                declare @site nvarchar(1024);
                select @site = SITE.NAME
                from dbo.SYSTEMROLE
                inner join dbo.SITE
                on SYSTEMROLE.SITEID = SITE.ID
                where SYSTEMROLE.ID = @SYSTEMROLEID;

                if @site is not null
                begin
                    set @sitesecurity = (
                    select @site
                    for xml raw('SiteSecurity'),type,elements
                    );
                end
            end
        else -- Site security mode is set to non-assigned constituents
            begin
                set @sitesecurity = (
                select 'Records with no site'
                for xml raw('SiteSecurity'),type,elements
                );    
            end
        */

        declare @doc nvarchar(max);

        set @doc=
        N'<SystemRole>' +

        cast(@recordInfo as nvarchar(max))
        +
        coalesce(cast(@featureperms as nvarchar(max)),'')
        +

        coalesce(cast(@tasks as nvarchar(max)),'')
        +

        coalesce(cast(@codetableperms as nvarchar(max)),'')
        +

        /*
        coalesce(cast(@constitsecurityringfence as nvarchar(max)),'')
        +        
        */

coalesce(cast(@batchtype as nvarchar(max)),'')
+

        coalesce(cast(@kpiinstanceperms as nvarchar(max)),'')

        +

        coalesce(cast(@codetableentryperms as nvarchar(max)),'')

        +

        coalesce(cast(@featuregroupperms as nvarchar(max)),'')

        +

        N'</SystemRole>' 


        set @r=@doc;


        return @r;

        END