question

taj avatar image
taj asked

How do I get SQL Server 2008 to Produce the Correct XML?

**script** select page.Page as 'name', page.ProcedureName as 'datasource', ( select control.ColumnName "control/@name", control.ParameterName "control/@parametername", control.IsEdit "control/@isedit", control.IsView "control/@isview", control.IsRequired "control/@isrequired" from tblcac control join tblrum rum on control.RoleId = rum.RoleId right outer join tblema vem on vem.PersonId = rum.PersonId where vem.PersonId = 32177 and control.Valid = 1 or control.Valid is null and control.Page = page.Page and control.ProcedureName = page.ProcedureName order by control.ColumnName for xml path(''), type ) as controls from tblcac page where exists ( select control.ColumnName "control/@name", control.ParameterName "control/@parameter_name", control.IsEdit "control/@isedit", control.IsView "control/@isview", control.IsRequired "control/@isrequired" from tblcac control join tblrum rum on control.RoleId = rum.RoleId right outer join tblema vem on vem.PersonId = rum.PersonId where vem.PersonId = 32177 and control.Valid = 1 or control.Valid is null and control.Page = page.Page and control.ProcedureName = page.ProcedureName ) for xml auto, root('ControlAccess') **required output** **here i am getting above output many times actually my requirement is it should come only once .. please somebody guide me** **ddl scripts as follows** CREATE TABLE [dbo].tblrum( [Id] [int] IDENTITY(1,1) NOT NULL, [RoleId] [int] NOT NULL, [PersonId] [int] NOT NULL, [ProfitCenterCode] [varchar](25) NULL, [CostCenterCode] [varchar](25) NULL, [LocationCode] [int] NULL, [OrgEntityCode] [varchar](10) NULL, [Active] [int] NOT NULL, [CreatedBy] [int] NOT NULL, [CreatedOn] [datetime] NOT NULL, CONSTRAINT [pk_rum_Id1] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] SET IDENTITY_INSERT [dbo].tblrum ON INSERT [dbo].tblrum ([Id], [RoleId], [PersonId], [ProfitCenterCode], [CostCenterCode], [LocationCode], [OrgEntityCode], [Active], [CreatedBy], [CreatedOn]) VALUES (719, 89, 1129, N'F7100', N'71007122', 100, N'3736', 1, 34114, CAST(0x00009EA301357C20 AS DateTime)) INSERT [dbo].tblrum ([Id], [RoleId], [PersonId], [ProfitCenterCode], [CostCenterCode], [LocationCode], [OrgEntityCode], [Active], [CreatedBy], [CreatedOn]) VALUES (720, 89, 1593, N'100', N'100', 100, N'3736', 1, 32625, CAST(0x00009C2500A9860C AS DateTime)) INSERT [dbo].tblrum ([Id], [RoleId], [PersonId], [ProfitCenterCode], [CostCenterCode], [LocationCode], [OrgEntityCode], [Active], [CreatedBy], [CreatedOn]) VALUES (721, 89, 31067, N'100', N'100', 100, N'3736', 1, 30698, CAST(0x00009B4A01078C5C AS DateTime)) INSERT [dbo].tblrum ([Id], [RoleId], [PersonId], [ProfitCenterCode], [CostCenterCode], [LocationCode], [OrgEntityCode], [Active], [CreatedBy], [CreatedOn]) VALUES (722, 89, 32177, N'100', N'100', 100, N'3736', 1, 30698, CAST(0x00009B4A010736D0 AS DateTime)) INSERT [dbo].tblrum ([Id], [RoleId], [PersonId], [ProfitCenterCode], [CostCenterCode], [LocationCode], [OrgEntityCode], [Active], [CreatedBy], [CreatedOn]) VALUES (723, 89, 32880, N'100', N'100', 100, NULL, 1, 31064, CAST(0x00009FEA00B9EE0C AS DateTime)) INSERT [dbo].tblrum ([Id], [RoleId], [PersonId], [ProfitCenterCode], [CostCenterCode], [LocationCode], [OrgEntityCode], [Active], [CreatedBy], [CreatedOn]) VALUES (724, 89, 33341, N'100', N'100', 100, N'3736', 1, 32625, CAST(0x00009C5300F78924 AS DateTime)) INSERT [dbo].tblrum ([Id], [RoleId], [PersonId], [ProfitCenterCode], [CostCenterCode], [LocationCode], [OrgEntityCode], [Active], [CreatedBy], [CreatedOn]) VALUES (725, 89, 34607, N'100', N'100', 100, N'3736', 1, 34880, CAST(0x0000A37E0143B074 AS DateTime)) INSERT [dbo].tblrum ([Id], [RoleId], [PersonId], [ProfitCenterCode], [CostCenterCode], [LocationCode], [OrgEntityCode], [Active], [CreatedBy], [CreatedOn]) VALUES (726, 89, 34902, N'100', N'100', 100, N'3736', 1, 234, CAST(0x0000A32400E8FE18 AS DateTime)) INSERT [dbo].tblrum ([Id], [RoleId], [PersonId], [ProfitCenterCode], [CostCenterCode], [LocationCode], [OrgEntityCode], [Active], [CreatedBy], [CreatedOn]) VALUES (727, 89, 36254, N'100', N'100', 100, NULL, 1, 31064, CAST(0x00009F4700B31E4C AS DateTime)) CREATE TABLE [dbo].tblema( [PersonId] [int] NOT NULL, [EID] [varchar](12) NULL, [NamePrefix] [varchar](5) NULL, [NameFirst] [varchar](40) NULL, [NameMiddle] [varchar](40) NULL, [NameLast] [varchar](40) NULL, [JobDescription] [varchar](50) NULL, [BusinessTitle] [varchar](50) NULL, [ProfitCenterCode] [varchar](10) NULL, [CostCenterCode] [varchar](20) NULL, [LegacyLocationId] [int] NULL, [PeS_LocationCode] [varchar](10) NULL, [Grade] [varchar](5) NULL, [DateOfHire] [datetime] NULL, [DateOfTermination] [datetime] NULL, [DateOfRehire] [datetime] NULL, [SupervisorEID] [varchar](12) NULL, [SupervisorPersonId] [int] NULL, [SalaryPlannerEID] [varchar](12) NULL, [SalaryPlannerPersonId] [int] NULL, [HrContactEID] [varchar](12) NULL, [HrContactPersonId] [int] NULL, [SBG] [varchar](20) NULL, [SBU] [varchar](20) NULL, [SBE] [varchar](20) NULL, [SBX] [varchar](20) NULL, [OrgEntity] [varchar](10) NULL, [EmployerId] [int] NULL, [ResidenceAddress1] [varchar](60) NULL, [ResidenceAddress2] [varchar](60) NULL, [ResidenceAddress3] [varchar](60) NULL, [ResidenceAddress4] [varchar](60) NULL, [ResidenceCity] [nvarchar](100) NULL, [ResidenceStateId] [int] NULL, [ResidencePostalCode] [varchar](20) NULL, [PermanentAddress1] [varchar](60) NULL, [PermanentAddress2] [varchar](60) NULL, [PermanentAddress3] [varchar](60) NULL, [PermanentAddress4] [varchar](60) NULL, [PermanentCity] [nvarchar](100) NULL, [PermanentStateId] [int] NULL, [PermanentPostalCode] [varchar](20) NULL, [PhoneMobile] [varchar](30) NULL, [PhoneResidence] [varchar](30) NULL, [PhoneOffice] [varchar](30) NULL, [PhoneExtension] [varchar](20) NULL, [EmailAddressOffice] [varchar](80) NULL, [EmailAddressAlternate] [varchar](80) NULL, [Sex] [char](5) NULL, [BloodGroup] [varchar](5) NULL, [DateOfBirth] [datetime] NULL, [PAN] [varchar](20) NULL, [PassportNumber] [varchar](50) NULL, [BankCode] [varchar](10) NULL, [BankAccountNo] [varchar](50) NULL, [BankIFSC_Code] [varchar](20) NULL, [ExEmployer] [varchar](50) NULL, [PastExperience] [int] NULL, [Qualification1] [varchar](60) NULL, [YearOfPassing1] [varchar](10) NULL, [Qualification2] [varchar](60) NULL, [YearOfPassing2] [varchar](10) NULL, [Qualification3] [varchar](60) NULL, [YearOfPassing3] [varchar](10) NULL, [LegacyEmpNo] [varchar](11) NULL, [LegacyFunction] [varchar](50) NULL, [HLoB] [varchar](10) NULL, [HComments] [varchar](100) NULL, [CostCenterFunction] [varchar](10) NULL, [CurrentLocationId] [int] NULL, [CurrentBuildingId] [int] NULL, [CurrentFloorId] [int] NULL, [CurrentSeatId] [nvarchar](50) NULL, [CurrentAssetId] [varchar](100) NULL, [IsValid] [int] NULL, [SysComment] [varchar](200) NULL, [Attribute0] [varchar](200) NULL, [Attribute1] [varchar](200) NULL, [Attribute2] [varchar](200) NULL, [Attribute3] [varchar](200) NULL, [Attribute4] [varchar](200) NULL, [Attribute5] [varchar](200) NULL, [Attribute6] [varchar](200) NULL, [Attribute7] [varchar](200) NULL, [Attribute8] [varchar](200) NULL, [Attribute9] [varchar](200) NULL, CONSTRAINT [pk_ema_PersonId1] PRIMARY KEY CLUSTERED ( [PersonId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] INSERT [dbo].tblema ([PersonId], [EID], [NamePrefix], [NameFirst], [NameMiddle], [NameLast], [JobDescription], [BusinessTitle], [ProfitCenterCode], [CostCenterCode], [LegacyLocationId], [PeS_LocationCode], [Grade], [DateOfHire], [DateOfTermination], [DateOfRehire], [SupervisorEID], [SupervisorPersonId], [SalaryPlannerEID], [SalaryPlannerPersonId], [HrContactEID], [HrContactPersonId], [SBG], [SBU], [SBE], [SBX], [OrgEntity], [EmployerId], [ResidenceAddress1], [ResidenceAddress2], [ResidenceAddress3], [ResidenceAddress4], [ResidenceCity], [ResidenceStateId], [ResidencePostalCode], [PermanentAddress1], [PermanentAddress2], [PermanentAddress3], [PermanentAddress4], [PermanentCity], [PermanentStateId], [PermanentPostalCode], [PhoneMobile], [PhoneResidence], [PhoneOffice], [PhoneExtension], [EmailAddressOffice], [EmailAddressAlternate], [Sex], [BloodGroup], [DateOfBirth], [PAN], [PassportNumber], [BankCode], [BankAccountNo], [BankIFSC_Code], [ExEmployer], [PastExperience], [Qualification1], [YearOfPassing1], [Qualification2], [YearOfPassing2], [Qualification3], [YearOfPassing3], [LegacyEmpNo], [LegacyFunction], [HLoB], [HComments], [CostCenterFunction], [CurrentLocationId], [CurrentBuildingId], [CurrentFloorId], [CurrentSeatId], [CurrentAssetId], [IsValid], [SysComment], [Attribute0], [Attribute1], [Attribute2], [Attribute3], [Attribute4], [Attribute5], [Attribute6], [Attribute7], [Attribute8], [Attribute9]) VALUES (32177, N'E401243', N'Mr', N'Mahesh', N'Vinayak', N'ok', N'Sr. Sales Administration Super', N'Estimation Manager', N'H9036', N'51607060', 603, NULL, N'B3', CAST(0x0000999E00000000 AS DateTime), NULL, CAST(0x0000999E00000000 AS DateTime), N'E286749', 36254, N'E286749', 36254, N'E287494', 1022, N'ACS', N'HPS', N'TCOP', NULL, N'3736', 1, N'B 107, Om lala', N'Bibwewadi', N'-', N'-', NULL, NULL, N'411037', N'As above', N'-', N'-', N'-', NULL, NULL, N'-', N'9850978059', NULL, N'66072663', N'22663', N'Mahesh.Musale@moaney.com', N'Mahesh.V.kk@gmail.com', N'M ', N'B+', CAST(0x00006F1100000000 AS DateTime), N'AJWPM0707D', N'-', N'CITI', N'5046083708', NULL, N'Rosemount Tank Gauging I Pvt Ltd', 86, N'ME Marketing Mangt', N'2005', N'BE Electrical', N'2000', NULL, NULL, N'4422', N'Sales', N'0', NULL, N'CPS_0000', 603, 1, 30, N'ST-32177', N'IE03DTXP02173', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL) CREATE TABLE [dbo].tblcac( [RoleId] [int] NOT NULL, [Page] [varchar](150) NOT NULL, [ColumnName] [varchar](150) NOT NULL, [ProcedureName] [varchar](30) NULL, [ParameterName] [varchar](30) NULL, [IsEdit] [int] NULL, [IsView] [int] NULL, [IsRequired] [int] NULL, [Valid] [int] NULL, [CreatedBy] [int] NULL, [CreatedOn] [date] NULL, [ModifiedBy] [int] NULL, [ModifiedOn] [date] NULL, CONSTRAINT [pk_EAQ_cac_RoleId_Page_ColumnName1] PRIMARY KEY CLUSTERED ( [RoleId] ASC, [Page] ASC, [ColumnName] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] INSERT [dbo].tblcac ([RoleId], [Page], [ColumnName], [ProcedureName], [ParameterName], [IsEdit], [IsView], [IsRequired], [Valid], [CreatedBy], [CreatedOn], [ModifiedBy], [ModifiedOn]) VALUES (88, N'ESTIMATESUMMARY_ASPX', N'WTW_MARGIN_ABSOLUTE', N'USP_SHOW_SUMMARY', NULL, 0, 0, 0, 1, 31955, CAST(0x03300B00 AS Date), 31955, CAST(0x03300B00 AS Date)) INSERT [dbo].tblcac ([RoleId], [Page], [ColumnName], [ProcedureName], [ParameterName], [IsEdit], [IsView], [IsRequired], [Valid], [CreatedBy], [CreatedOn], [ModifiedBy], [ModifiedOn]) VALUES (89, N'ESTIMATESUMMARY_ASPX', N'CONTRIBUTION', N'USP_SHOW_SUMMARY', NULL, 0, 1, 0, 1, 8704, CAST(0x862F0B00 AS Date), 8704, CAST(0x862F0B00 AS Date)) INSERT [dbo].tblcac ([RoleId], [Page], [ColumnName], [ProcedureName], [ParameterName], [IsEdit], [IsView], [IsRequired], [Valid], [CreatedBy], [CreatedOn], [ModifiedBy], [ModifiedOn]) VALUES (89, N'ESTIMATESUMMARY_ASPX', N'CONTRIBUTION_PERCENT', N'USP_SHOW_SUMMARY', NULL, 0, 1, 0, 1, 8704, CAST(0x862F0B00 AS Date), 8704, CAST(0x862F0B00 AS Date)) INSERT [dbo].tblcac ([RoleId], [Page], [ColumnName], [ProcedureName], [ParameterName], [IsEdit], [IsView], [IsRequired], [Valid], [CreatedBy], [CreatedOn], [ModifiedBy], [ModifiedOn]) VALUES (89, N'ESTIMATESUMMARY_ASPX', N'COST_AFTER_SUMMARY_DISC', N'USP_SHOW_SUMMARY', NULL, 0, 1, 0, 1, 8704, CAST(0x862F0B00 AS Date), 8704, CAST(0x862F0B00 AS Date)) INSERT [dbo].tblcac ([RoleId], [Page], [ColumnName], [ProcedureName], [ParameterName], [IsEdit], [IsView], [IsRequired], [Valid], [CreatedBy], [CreatedOn], [ModifiedBy], [ModifiedOn]) VALUES (89, N'ESTIMATESUMMARY_ASPX', N'COST_BEFORE_SUMMARY_DISC', N'USP_SHOW_SUMMARY', NULL, 0, 1, 0, 1, 8704, CAST(0x862F0B00 AS Date), 8704, CAST(0x862F0B00 AS Date)) INSERT [dbo].tblcac ([RoleId], [Page], [ColumnName], [ProcedureName], [ParameterName], [IsEdit], [IsView], [IsRequired], [Valid], [CreatedBy], [CreatedOn], [ModifiedBy], [ModifiedOn]) VALUES (89, N'ESTIMATESUMMARY_ASPX', N'COST_DISCOUNT', N'USP_SHOW_SUMMARY', NULL, 0, 1, 0, 1, 8704, CAST(0x862F0B00 AS Date), 8704, CAST(0x862F0B00 AS Date)) INSERT [dbo].tblcac ([RoleId], [Page], [ColumnName], [ProcedureName], [ParameterName], [IsEdit], [IsView], [IsRequired], [Valid], [CreatedBy], [CreatedOn], [ModifiedBy], [ModifiedOn]) VALUES (89, N'ESTIMATESUMMARY_ASPX', N'EXCHANGE_RATE', N'USP_SHOW_SUMMARY', NULL, 1, 1, 0, 1, 8704, CAST(0x862F0B00 AS Date), 8704, CAST(0x862F0B00 AS Date)) INSERT [dbo].tblcac ([RoleId], [Page], [ColumnName], [ProcedureName], [ParameterName], [IsEdit], [IsView], [IsRequired], [Valid], [CreatedBy], [CreatedOn], [ModifiedBy], [ModifiedOn]) VALUES (89, N'ESTIMATESUMMARY_ASPX', N'GROSS_MARGIN', N'USP_SHOW_SUMMARY', NULL, 0, 1, 0, 1, 8704, CAST(0x862F0B00 AS Date), 8704, CAST(0x862F0B00 AS Date)) INSERT [dbo].tblcac ([RoleId], [Page], [ColumnName], [ProcedureName], [ParameterName], [IsEdit], [IsView], [IsRequired], [Valid], [CreatedBy], [CreatedOn], [ModifiedBy], [ModifiedOn]) VALUES (89, N'ESTIMATESUMMARY_ASPX', N'GROSS_MARGIN_PERCENT', N'USP_SHOW_SUMMARY', NULL, 0, 1, 0, 1, 8704, CAST(0x862F0B00 AS Date), 8704, CAST(0x862F0B00 AS Date)) INSERT [dbo].tblcac ([RoleId], [Page], [ColumnName], [ProcedureName], [ParameterName], [IsEdit], [IsView], [IsRequired], [Valid], [CreatedBy], [CreatedOn], [ModifiedBy], [ModifiedOn]) VALUES (89, N'ESTIMATESUMMARY_ASPX', N'PRICE_AFTER_SUMMARY_DISC', N'USP_SHOW_SUMMARY', NULL, 0, 1, 0, 1, 8704, CAST(0x862F0B00 AS Date), 8704, CAST(0x862F0B00 AS Date)) INSERT [dbo].tblcac ([RoleId], [Page], [ColumnName], [ProcedureName], [ParameterName], [IsEdit], [IsView], [IsRequired], [Valid], [CreatedBy], [CreatedOn], [ModifiedBy], [ModifiedOn]) VALUES (89, N'ESTIMATESUMMARY_ASPX', N'PRICE_BEFORE_SUMMARY_DISC', N'USP_SHOW_SUMMARY', NULL, 0, 1, 0, 1, 8704, CAST(0x862F0B00 AS Date), 8704, CAST(0x862F0B00 AS Date)) INSERT [dbo].tblcac ([RoleId], [Page], [ColumnName], [ProcedureName], [ParameterName], [IsEdit], [IsView], [IsRequired], [Valid], [CreatedBy], [CreatedOn], [ModifiedBy], [ModifiedOn]) VALUES (89, N'ESTIMATESUMMARY_ASPX', N'PRICE_DISCOUNT', N'USP_SHOW_SUMMARY', NULL, 0, 1, 0, 1, 8704, CAST(0x862F0B00 AS Date), 8704, CAST(0x862F0B00 AS Date)) INSERT [dbo].tblcac ([RoleId], [Page], [ColumnName], [ProcedureName], [ParameterName], [IsEdit], [IsView], [IsRequired], [Valid], [CreatedBy], [CreatedOn], [ModifiedBy], [ModifiedOn]) VALUES (89, N'ESTIMATESUMMARY_ASPX', N'TOTAL_COST_GRID', N'USP_SHOW_SUMMARY', NULL, 0, 1, 0, 1, 8704, CAST(0x862F0B00 AS Date), 8704, CAST(0x862F0B00 AS Date)) INSERT [dbo].tblcac ([RoleId], [Page], [ColumnName], [ProcedureName], [ParameterName], [IsEdit], [IsView], [IsRequired], [Valid], [CreatedBy], [CreatedOn], [ModifiedBy], [ModifiedOn]) VALUES (89, N'ESTIMATESUMMARY_ASPX', N'TOTAL_COST_SUMMARY_DISC', N'USP_SHOW_SUMMARY', NULL, 0, 1, 0, 1, 8704, CAST(0x862F0B00 AS Date), 8704, CAST(0x862F0B00 AS Date)) INSERT [dbo].tblcac ([RoleId], [Page], [ColumnName], [ProcedureName], [ParameterName], [IsEdit], [IsView], [IsRequired], [Valid], [CreatedBy], [CreatedOn], [ModifiedBy], [ModifiedOn]) VALUES (89, N'ESTIMATESUMMARY_ASPX', N'TOTAL_PRICE_GRID', N'USP_SHOW_SUMMARY', NULL, 0, 1, 0, 1, 8704, CAST(0x862F0B00 AS Date), 8704, CAST(0x862F0B00 AS Date)) INSERT [dbo].tblcac ([RoleId], [Page], [ColumnName], [ProcedureName], [ParameterName], [IsEdit], [IsView], [IsRequired], [Valid], [CreatedBy], [CreatedOn], [ModifiedBy], [ModifiedOn]) VALUES (89, N'ESTIMATESUMMARY_ASPX', N'TOTAL_PRICE_SUMMARY_DISC', N'USP_SHOW_SUMMARY', NULL, 0, 1, 0, 1, 8704, CAST(0x862F0B00 AS Date), 8704, CAST(0x862F0B00 AS Date)) INSERT [dbo].tblcac ([RoleId], [Page], [ColumnName], [ProcedureName], [ParameterName], [IsEdit], [IsView], [IsRequired], [Valid], [CreatedBy], [CreatedOn], [ModifiedBy], [ModifiedOn]) VALUES (89, N'ESTIMATESUMMARY_ASPX', N'WTW_MARGIN', N'USP_SHOW_SUMMARY', NULL, 0, 1, 0, 1, 8704, CAST(0x862F0B00 AS Date), 8704, CAST(0x862F0B00 AS Date)) INSERT [dbo].tblcac ([RoleId], [Page], [ColumnName], [ProcedureName], [ParameterName], [IsEdit], [IsView], [IsRequired], [Valid], [CreatedBy], [CreatedOn], [ModifiedBy], [ModifiedOn]) VALUES (89, N'ESTIMATESUMMARY_ASPX', N'WTW_MARGIN_ABSOLUTE', N'USP_SHOW_SUMMARY', NULL, 0, 1, 0, 1, 31955, CAST(0x03300B00 AS Date), 31955, CAST(0x03300B00 AS Date))
sql-server-2008xml
5 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image Kev Riley ♦♦ commented ·
some example data to play with would be good....
0 Likes 0 ·
Kev Riley avatar image Kev Riley ♦♦ commented ·
still need either viwSSAppsEmpMasterExtended or remove this from the query
0 Likes 0 ·
Kev Riley avatar image Kev Riley ♦♦ commented ·
OK but now there's no data that matches between tblEAQ_ControlAccess and tblSSAppsRoleUserMap on RoleID?
0 Likes 0 ·
Kev Riley avatar image Kev Riley ♦♦ commented ·
@taj, but there's still no data that links tblEAQ_ControlAccess and tblSSAppsRoleUserMap - there is no RoleID that matches - I don't understand your data enough to be able to change it. Try running the code you have posted here in a clean database - you will see that the query returns nothing.
0 Likes 0 ·
taj avatar image taj commented ·
sir now i edited all scripts and ddl's now you can easily test code.... here i need required output once only but i am gettimg repetations
0 Likes 0 ·

1 Answer

·
Kev Riley avatar image
Kev Riley answered
Thanks for getting the ddl scripts correct, it helps a lot... select Page.name, Page.datasource, cast(Page.controls as xml) as controls from ( select page.Page as 'name', page.ProcedureName as 'datasource', (select control.ColumnName "control/@name", control.ParameterName "control/@parametername", control.IsEdit "control/@isedit", control.IsView "control/@isview", control.IsRequired "control/@isrequired" from tblcac control join tblrum rum on control.RoleId = rum.RoleId right outer join tblema vem on vem.PersonId = rum.PersonId where vem.PersonId = 32177 and control.Valid = 1 or control.Valid is null order by control.ColumnName for xml path('') ) as controls from tblcac page group by page.Page, page.ProcedureName )Page for xml auto, root('ControlAccess') gives you
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

taj avatar image taj commented ·
sir now its working perfect... thanks a lot, is it there any chance by using my script will give expected output.
0 Likes 0 ·
Kev Riley avatar image Kev Riley ♦♦ commented ·
your script was repeating the info as there was no grouping on the 'page' elements.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.