• lenamagnus.frisk - Thursday, June 14, 2018 1:50 AM

    Luis Cazares - Wednesday, June 13, 2018 7:39 AM

    Can you post DDL and sample data for FunctionPermission table? The problem might be related to it and the FOR XML.

    Hi Luis and thank you for your answer,

    Here are two files (to create table FunctionPermission and some sample data)

    /A confused one

    Sorry - forgot the files...

    create table:
    /****** Object: Table [dbo].[FunctionPermission]  Script Date: 2018-06-14 09:51:18 ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    CREATE TABLE [dbo].[FunctionPermission](
        [FunctionPermissionGuid] [uniqueidentifier] ROWGUIDCOL NOT NULL,
        [SystemFunctionGuid] [uniqueidentifier] NOT NULL,
        [PermissionTypeGuid] [uniqueidentifier] NOT NULL,
        [UserGroupRoleGuid] [uniqueidentifier] NULL,
        [UserAccountGuid] [uniqueidentifier] NULL,
        [Name] [nvarchar](50) NOT NULL,
        [ReadPermission] [bit] NOT NULL,
        [CreatePermission] [bit] NOT NULL,
        [UpdatePermission] [bit] NOT NULL,
        [DeletePermission] [bit] NOT NULL,
        [FilterPermission] [bit] NOT NULL,
        [ExportPermission] [bit] NOT NULL,
        [PrintPermission] [bit] NOT NULL,
        [RightsAssignmentPermission] [bit] NOT NULL,
        [TranslatePermission] [bit] NOT NULL,
    CONSTRAINT [PK_FunctionPermission] PRIMARY KEY NONCLUSTERED
    (
        [FunctionPermissionGuid] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
    ) ON [PRIMARY]
    GO

    ALTER TABLE [dbo].[FunctionPermission] ADD CONSTRAINT [DF_FunctionPermission_FunctionPermissionGuid] DEFAULT (newid()) FOR [FunctionPermissionGuid]
    GO

    ALTER TABLE [dbo].[FunctionPermission] ADD CONSTRAINT [DF_FunctionPermission_ReadPermission] DEFAULT ((1)) FOR [ReadPermission]
    GO

    ALTER TABLE [dbo].[FunctionPermission] ADD CONSTRAINT [DF_FunctionPermission_CreatePermission] DEFAULT ((0)) FOR [CreatePermission]
    GO

    ALTER TABLE [dbo].[FunctionPermission] ADD CONSTRAINT [DF_FunctionPermission_UpdatePermission] DEFAULT ((0)) FOR [UpdatePermission]
    GO

    ALTER TABLE [dbo].[FunctionPermission] ADD CONSTRAINT [DF_FunctionPermission_DeletePermission] DEFAULT ((0)) FOR [DeletePermission]
    GO

    ALTER TABLE [dbo].[FunctionPermission] ADD CONSTRAINT [DF_FunctionPermission_FilterPermission] DEFAULT ((1)) FOR [FilterPermission]
    GO

    ALTER TABLE [dbo].[FunctionPermission] ADD CONSTRAINT [DF_FunctionPermission_ExportPermission] DEFAULT ((0)) FOR [ExportPermission]
    GO

    ALTER TABLE [dbo].[FunctionPermission] ADD CONSTRAINT [DF_FunctionPermission_PrintPermission] DEFAULT ((1)) FOR [PrintPermission]
    GO

    ALTER TABLE [dbo].[FunctionPermission] ADD CONSTRAINT [DF_FunctionPermission_RightsAssignmentPermission] DEFAULT ((0)) FOR [RightsAssignmentPermission]
    GO

    ALTER TABLE [dbo].[FunctionPermission] ADD CONSTRAINT [DF_FunctionPermission_TranslatePermission] DEFAULT ((0)) FOR [TranslatePermission]
    GO

    ALTER TABLE [dbo].[FunctionPermission] WITH CHECK ADD CONSTRAINT [FK_FunctionPermission_PermissionType] FOREIGN KEY([PermissionTypeGuid])
    REFERENCES [dbo].[PermissionType] ([PermissionTypeGuid])
    ON DELETE CASCADE
    GO

    ALTER TABLE [dbo].[FunctionPermission] CHECK CONSTRAINT [FK_FunctionPermission_PermissionType]
    GO

    ALTER TABLE [dbo].[FunctionPermission] WITH CHECK ADD CONSTRAINT [FK_FunctionPermission_SystemFunction] FOREIGN KEY([SystemFunctionGuid])
    REFERENCES [dbo].[SystemFunction] ([SystemFunctionGuid])
    ON DELETE CASCADE
    GO

    ALTER TABLE [dbo].[FunctionPermission] CHECK CONSTRAINT [FK_FunctionPermission_SystemFunction]
    GO

    ALTER TABLE [dbo].[FunctionPermission] WITH CHECK ADD CONSTRAINT [FK_FunctionPermission_UserAccount] FOREIGN KEY([UserAccountGuid])
    REFERENCES [dbo].[UserAccount] ([UserAccountGuid])
    ON DELETE CASCADE
    GO

    ALTER TABLE [dbo].[FunctionPermission] CHECK CONSTRAINT [FK_FunctionPermission_UserAccount]
    GO

    ALTER TABLE [dbo].[FunctionPermission] WITH CHECK ADD CONSTRAINT [FK_FunctionPermission_UserGroupRole] FOREIGN KEY([UserGroupRoleGuid])
    REFERENCES [dbo].[UserGroupRole] ([UserGroupRoleGuid])
    ON DELETE CASCADE
    GO

    ALTER TABLE [dbo].[FunctionPermission] CHECK CONSTRAINT [FK_FunctionPermission_UserGroupRole]
    GO

    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The internal key of the user. Automatically generated.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'FunctionPermission', @level2type=N'COLUMN',@level2name=N'UserAccountGuid'
    GO