SQL Server output as XML -

sql server code generates output xml having problem not getting output per requirement

select  section.text,         section.imageurl,         (select item.text "item/@text",                 item.url "item/@url",                 item.imageurl "item/@imageurl"                  xml path(''),type) items    tblatnot_menusections section join   (select  mit.text,                 mit.url,                 mit.imageurl,                 mit.section,                 mit.itemorder            tblatnot_menuitems mit         join    tblatnot_rolemenuaccess rma on (rma.item = mit.item , rma.valid = 1)           (mit.valid = 1 or mit.valid null)         , rma.role in (select distinct rum.roleid                            tblssappsroleusermap rum                          join   viwssappsempmasterextended vem on (vem.personid = rum.personid)                           vem.isemployeeactive = 'y'                           ,    vem.personid = 86                          ,    rum.roleid = rma.role)                          or     rma.role null                )item  on (item.section = section.section) order section.sectionorder,           item.itemorder xml auto, root('menu') 

current output:

<menu>   <section text="home" imageurl="/atnot/include/images/home.gif">     <items>       <item text="home" url="/atnot/pages/home.aspx" imageurl="/atnot/include/images/page_add.png" />     </items>   </section>   <section text="entry" imageurl="/atnot/include/images/page_go.png">     <items>       <item text="attendance entry" url="/atnot/pages/attendanceentry.aspx" imageurl="/atnot/include/images/page_add.png" />     </items>   </section>   <section text="entry" imageurl="/atnot/include/images/page_go.png">     <items>       <item text="my attendance requests" url="/atnot/pages/attendancerequests.aspx" imageurl="/atnot/include/images/page_add.png" />     </items>   </section>   <section text="claim" imageurl="/atnot/include/images/new_tour.gif">     <items>       <item text="back date ot claim" url="/atnot/pages/backdateclaim.aspx" imageurl="/atnot/include/images/page_add.png" />     </items>   </section>   <section text="claim" imageurl="/atnot/include/images/new_tour.gif">     <items>       <item text="overtime claim" url="/atnot/pages/overtimeclaim.aspx" imageurl="/atnot/include/images/page_add.png" />     </items>   </section>   <section text="claim" imageurl="/atnot/include/images/new_tour.gif">     <items>       <item text="my overtime requests" url="/atnot/pages/overtimerequests.aspx" imageurl="/atnot/include/images/page_add.png" />     </items>   </section> </menu> 

but requirements follows..... please tell me remaining..

here child node items getting repeated each time

desired output:

<menu>   <section text="home" image_url="/atnot/include/images/home.gif">     <items>       <item text="home" url="/atnot/pages/home.aspx" image_url="/atnot/include/images/page_add.png" />     </items>   </section>   <section text="entry" image_url="/atnot/include/images/page_go.png">     <items>       <item text="attendance entry" url="/atnot/pages/attendanceentry.aspx" image_url="/atnot/include/images/page_add.png" />       <item text="my attendance requests" url="/atnot/pages/attendancerequests.aspx" image_url="/atnot/include/images/page_add.png" />     </items>   </section>   <section text="claim" image_url="/atnot/include/images/new_tour.gif">     <items>       <item text="back date ot claim" url="/atnot/pages/backdateclaim.aspx" image_url="/atnot/include/images/page_add.png" />       <item text="overtime claim" url="/atnot/pages/overtimeclaim.aspx" image_url="/atnot/include/images/page_add.png" />       <item text="my overtime requests" url="/atnot/pages/overtimerequests.aspx" image_url="/atnot/include/images/page_add.png" />     </items>   </section> </menu> 

ddl script:

create table [dbo].[tblssappsroleusermap] (     [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_id] primary key clustered ([id] asc)          (pad_index = off, statistics_norecompute = off, ignore_dup_key = off,                 allow_row_locks  = on, allow_page_locks  = on) on [primary] ) on [primary] go set ansi_padding off go set identity_insert [dbo].[tblssappsroleusermap] on insert [dbo].[tblssappsroleusermap] ([id], [roleid], [personid], [profitcentercode], [costcentercode], [locationcode], [orgentitycode], [active], [createdby], [createdon]) values (1, 1, 37306, n'100', n'100', 100, n'3736', 1, 38331, cast(0x0000a307012dd394 datetime)) insert [dbo].[tblssappsroleusermap] ([id], [roleid], [personid], [profitcentercode], [costcentercode], [locationcode], [orgentitycode], [active], [createdby], [createdon]) values (2, 1, 38440, n'100', n'100', 100, n'3482', 1, 36540, cast(0x0000a2ae01043994 datetime)) insert [dbo].[tblssappsroleusermap] ([id], [roleid], [personid], [profitcentercode], [costcentercode], [locationcode], [orgentitycode], [active], [createdby], [createdon]) values (2797, 184, 194, n'100', n'100', 100, null, 1, 1, cast(0x0000a3f000bed520 datetime)) insert [dbo].[tblssappsroleusermap] ([id], [roleid], [personid], [profitcentercode], [costcentercode], [locationcode], [orgentitycode], [active], [createdby], [createdon]) values (2798, 184, 449, n'100', n'100', 100, null, 1, 1, cast(0x0000a3f000bed520 datetime)) set identity_insert [dbo].[tblssappsroleusermap] off  create table [dbo].[tblatnot_menusections] (     [section] [int] not null,     [text] [varchar](500) null,     [url] [varchar](500) null,     [imageurl] [varchar](500) null,     [sectionorder] [int] null,     [valid] [bit] null,     [active] [bit] null,     [createdby] [int] null,     [createdon] [datetime] null,     [modifiedby] [int] null,     [modifiedon] [datetime] null,      constraint [pk_mes_section] primary key clustered ([section] asc)         (pad_index = off, statistics_norecompute = off, ignore_dup_key = off,                 allow_row_locks = on, allow_page_locks = on) on [primary] ) on [primary] go set ansi_padding off go insert [dbo].[tblatnot_menusections] ([section], [text], [url], [imageurl], [sectionorder], [valid], [active], [createdby], [createdon], [modifiedby], [modifiedon]) values (1, n'home', null, n'/atnot/include/images/home.gif', 1, 1, 1, 3591, cast(0x0000a3a400000000 datetime), null, null) insert [dbo].[tblatnot_menusections] ([section], [text], [url], [imageurl], [sectionorder], [valid], [active], [createdby], [createdon], [modifiedby], [modifiedon]) values (2, n'entry', null, n'/atnot/include/images/page_go.png', 2, 1, 1, 3591, cast(0x0000a3a400000000 datetime), null, null) insert [dbo].[tblatnot_menusections] ([section], [text], [url], [imageurl], [sectionorder], [valid], [active], [createdby], [createdon], [modifiedby], [modifiedon]) values (3, n'claim', null, n'/atnot/include/images/new_tour.gif', 3, 1, 1, 3591, cast(0x0000a3a400000000 datetime), null, null) insert [dbo].[tblatnot_menusections] ([section], [text], [url], [imageurl], [sectionorder], [valid], [active], [createdby], [createdon], [modifiedby], [modifiedon]) values (4, n'report', null, n'/atnot/include/images/reportexcel.gif', 4, 1, 1, 3591, cast(0x0000a3a400000000 datetime), null, null) insert [dbo].[tblatnot_menusections] ([section], [text], [url], [imageurl], [sectionorder], [valid], [active], [createdby], [createdon], [modifiedby], [modifiedon]) values (5, n'administrator', null, n'/atnot/include/images/admin1.png', 5, 1, 1, 3591, cast(0x0000a3a400000000 datetime), null, null) /****** object:  table [dbo].[tblatnot_menuitems]    script date: 12/16/2014 14:43:27 ******/ set ansi_nulls on go set quoted_identifier on go set ansi_padding on go create table [dbo].[tblatnot_menuitems](     [item] [int] not null,     [section] [int] null,     [text] [varchar](500) null,     [url] [varchar](500) null,     [imageurl] [varchar](500) null,     [itemorder] [int] null,     [valid] [bit] null,     [active] [bit] null,     [createdby] [int] null,     [createdon] [datetime] null,     [modifiedby] [int] null,     [modifiedon] [datetime] null,  constraint [pk_meit_item] primary key clustered  (     [item] asc )with (pad_index  = off, statistics_norecompute  = off, ignore_dup_key = off, allow_row_locks  = on, allow_page_locks  = on) on [primary] ) on [primary] go set ansi_padding off go insert [dbo].[tblatnot_menuitems] ([item], [section], [text], [url], [imageurl], [itemorder], [valid], [active], [createdby], [createdon], [modifiedby], [modifiedon]) values (1, 1, n'home', n'/atnot/pages/home.aspx', n'/atnot/include/images/page_add.png', 1, 1, 1, 3591, cast(0x0000a3a400000000 datetime), null, null) insert [dbo].[tblatnot_menuitems] ([item], [section], [text], [url], [imageurl], [itemorder], [valid], [active], [createdby], [createdon], [modifiedby], [modifiedon]) values (2, 2, n'attendance entry', n'/atnot/pages/attendanceentry.aspx', n'/atnot/include/images/page_add.png', 1, 1, 1, 3591, cast(0x0000a3a400000000 datetime), null, null) insert [dbo].[tblatnot_menuitems] ([item], [section], [text], [url], [imageurl], [itemorder], [valid], [active], [createdby], [createdon], [modifiedby], [modifiedon]) values (3, 2, n'my attendance requests', n'/atnot/pages/attendancerequests.aspx', n'/atnot/include/images/page_add.png', 2, 1, 1, 3591, cast(0x0000a3a400000000 datetime), null, null) insert [dbo].[tblatnot_menuitems] ([item], [section], [text], [url], [imageurl], [itemorder], [valid], [active], [createdby], [createdon], [modifiedby], [modifiedon]) values (4, 3, n'overtime claim', n'/atnot/pages/overtimeclaim.aspx', n'/atnot/include/images/page_add.png', 2, 1, 1, 3591, cast(0x0000a3fc011e85ea datetime), null, null) insert [dbo].[tblatnot_menuitems] ([item], [section], [text], [url], [imageurl], [itemorder], [valid], [active], [createdby], [createdon], [modifiedby], [modifiedon]) values (5, 3, n'back date ot claim', n'/atnot/pages/backdateclaim.aspx', n'/atnot/include/images/page_add.png', 1, 1, 1, 3591, cast(0x0000a3a400000000 datetime), null, null) insert [dbo].[tblatnot_menuitems] ([item], [section], [text], [url], [imageurl], [itemorder], [valid], [active], [createdby], [createdon], [modifiedby], [modifiedon]) values (6, 3, n'my overtime requests', n'/atnot/pages/overtimerequests.aspx', n'/atnot/include/images/page_add.png', 3, 1, 1, 3591, cast(0x0000a3a400000000 datetime), null, null) insert [dbo].[tblatnot_menuitems] ([item], [section], [text], [url], [imageurl], [itemorder], [valid], [active], [createdby], [createdon], [modifiedby], [modifiedon]) values (7, 4, n'daily overtime ', n'/atnot/reports/dailyovertimereport.aspx', n'/atnot/include/images/page_add.png', 1, 1, 1, 3591, cast(0x0000a3a400000000 datetime), null, null) insert [dbo].[tblatnot_menuitems] ([item], [section], [text], [url], [imageurl], [itemorder], [valid], [active], [createdby], [createdon], [modifiedby], [modifiedon]) values (8, 4, n'monthly overtime ', n'/atnot/reports/monthlyovertimereport.aspx', n'/atnot/include/images/page_add.png', 2, 1, 1, 3591, cast(0x0000a3a400000000 datetime), null, null) insert [dbo].[tblatnot_menuitems] ([item], [section], [text], [url], [imageurl], [itemorder], [valid], [active], [createdby], [createdon], [modifiedby], [modifiedon]) values (9, 4, n'monthly shift allowance ', n'/atnot/reports/monthlyshiftallowancereport.aspx', n'/atnot/include/images/page_add.png', 3, 1, 1, 3591, cast(0x0000a3a400000000 datetime), null, null) insert [dbo].[tblatnot_menuitems] ([item], [section], [text], [url], [imageurl], [itemorder], [valid], [active], [createdby], [createdon], [modifiedby], [modifiedon]) values (10, 4, n'detailed overtime report', n'/atnot/reports/detailedovertimereport.aspx', n'/atnot/include/images/page_add.png', 4, 1, 1, 3591, cast(0x0000a3a400000000 datetime), null, null) insert [dbo].[tblatnot_menuitems] ([item], [section], [text], [url], [imageurl], [itemorder], [valid], [active], [createdby], [createdon], [modifiedby], [modifiedon]) values (11, 5, n'back date entry', n'/atnot/admin/backdateentry.aspx', n'/atnot/include/images/page_add.png', 1, 1, 1, 3591, cast(0x0000a3a400000000 datetime), null, null) /****** object:  table [dbo].[tblatnot_rolemenuaccess]    script date: 12/16/2014 14:43:27 ******/ set ansi_nulls on go set quoted_identifier on go create table [dbo].[tblatnot_rolemenuaccess](     [id] [int] identity(1,1) not null,     [role] [int] null,     [item] [int] null,     [valid] [bit] null,     [active] [bit] null,     [createdby] [int] null,     [createdon] [datetime] null,     [modifiedby] [int] null,     [modifiedon] [datetime] null,  constraint [pk_rma_id] 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] go set identity_insert [dbo].[tblatnot_rolemenuaccess] on insert [dbo].[tblatnot_rolemenuaccess] ([id], [role], [item], [valid], [active], [createdby], [createdon], [modifiedby], [modifiedon]) values (1, null, 1, 1, 1, 3591, cast(0x0000a3c200000000 datetime), null, null) insert [dbo].[tblatnot_rolemenuaccess] ([id], [role], [item], [valid], [active], [createdby], [createdon], [modifiedby], [modifiedon]) values (2, null, 2, 1, 1, 3591, cast(0x0000a3c200000000 datetime), null, null) insert [dbo].[tblatnot_rolemenuaccess] ([id], [role], [item], [valid], [active], [createdby], [createdon], [modifiedby], [modifiedon]) values (3, null, 3, 1, 1, 3591, cast(0x0000a3c200000000 datetime), null, null) insert [dbo].[tblatnot_rolemenuaccess] ([id], [role], [item], [valid], [active], [createdby], [createdon], [modifiedby], [modifiedon]) values (4, null, 4, 1, 1, 3591, cast(0x0000a3c200000000 datetime), null, null) insert [dbo].[tblatnot_rolemenuaccess] ([id], [role], [item], [valid], [active], [createdby], [createdon], [modifiedby], [modifiedon]) values (5, null, 5, 1, 1, 3591, cast(0x0000a3c200000000 datetime), null, null) insert [dbo].[tblatnot_rolemenuaccess] ([id], [role], [item], [valid], [active], [createdby], [createdon], [modifiedby], [modifiedon]) values (6, null, 6, 1, 1, 3591, cast(0x0000a3c200000000 datetime), null, null) insert [dbo].[tblatnot_rolemenuaccess] ([id], [role], [item], [valid], [active], [createdby], [createdon], [modifiedby], [modifiedon]) values (7, 161, 7, 1, 1, 3591, cast(0x0000a3c200000000 datetime), null, null) insert [dbo].[tblatnot_rolemenuaccess] ([id], [role], [item], [valid], [active], [createdby], [createdon], [modifiedby], [modifiedon]) values (8, 161, 8, 1, 1, 3591, cast(0x0000a3c200000000 datetime), null, null) insert [dbo].[tblatnot_rolemenuaccess] ([id], [role], [item], [valid], [active], [createdby], [createdon], [modifiedby], [modifiedon]) values (9, 161, 9, 1, 1, 3591, cast(0x0000a3c200000000 datetime), null, null) insert [dbo].[tblatnot_rolemenuaccess] ([id], [role], [item], [valid], [active], [createdby], [createdon], [modifiedby], [modifiedon]) values (10, 161, 10, 1, 1, 3591, cast(0x0000a3c200000000 datetime), null, null) insert [dbo].[tblatnot_rolemenuaccess] ([id], [role], [item], [valid], [active], [createdby], [createdon], [modifiedby], [modifiedon]) values (11, 172, 11, 1, 1, 3591, cast(0x0000a3c200000000 datetime), null, null) set identity_insert [dbo].[tblatnot_rolemenuaccess] off /****** object:  default [df__tblatnot___valid__7f16d496]    script date: 12/16/2014 14:43:27 ******/ alter table [dbo].[tblatnot_rolemenuaccess] add  default ((1)) [valid] go /****** object:  default [df__tblatnot___activ__000af8cf]    script date: 12/16/2014 14:43:27 ******/ alter table [dbo].[tblatnot_rolemenuaccess] add  default ((1)) [active] go /****** object:  foreignkey [fk_mit_mse_section_section]    script date: 12/16/2014 14:43:27 ******/ alter table [dbo].[tblatnot_menuitems]  check add  constraint [fk_mit_mse_section_section] foreign key([section]) references [dbo].[tblatnot_menusections] ([section]) go alter table [dbo].[tblatnot_menuitems] check constraint [fk_mit_mse_section_section] go /****** object:  foreignkey [fk_rac_mit_item_item]    script date: 12/16/2014 14:43:27 ******/ alter table [dbo].[tblatnot_rolemenuaccess]  check add  constraint [fk_rac_mit_item_item] foreign key([item]) references [dbo].[tblatnot_menuitems] ([item]) go alter table [dbo].[tblatnot_rolemenuaccess] check constraint [fk_rac_mit_item_item] go /****** object:  foreignkey [fk_rac_rma_role_roleid]    script date: 12/16/2014 14:43:27 ******/ alter table [dbo].[tblatnot_rolemenuaccess]  check add  constraint [fk_rac_rma_role_roleid] foreign key([role]) references [dbo].[tblssappsrolemaster] ([roleid]) go alter table [dbo].[tblatnot_rolemenuaccess] check constraint [fk_rac_rma_role_roleid] go 

you rewrite query use for xml path , use query items in correlated subquery in column list.

here simplified example show mean.

select s.text,        s.imageurl,        (          select i.text '@text',                 i.url '@url',                 i.imageurl '@imageurl'          item -- replace items query          s.section = i.section -- correlation section          order i.itemorder          xml path('item'), type        ) items tblatnot_menusections s order s.sectionorder xml path('section'), root('menu') 

using tables except viwssappsempmasterextended

select s.text,        s.imageurl,        (          select i.text '@text',                 i.url '@url',                 i.imageurl '@imageurl'          tblatnot_menuitems            inner join tblatnot_rolemenuaccess r              on i.item = r.item ,                  r.valid = 1          s.section = i.section ,                (i.valid = 1 or i.valid null) ,                (r.role in (                           select m.roleid                           tblssappsroleusermap m                           ) or r.role null)          order i.itemorder          xml path('item'), type        ) items tblatnot_menusections s order s.sectionorder xml path('section'), root('menu') 

to filter on rows has no match in subquery have duplicate logic in exists clause in main query.

select s.text,        s.imageurl,        (          select i.text '@text',                 i.url '@url',                 i.imageurl '@imageurl'          tblatnot_menuitems            inner join tblatnot_rolemenuaccess r              on i.item = r.item ,                  r.valid = 1          s.section = i.section ,                 (i.valid = 1 or i.valid null) ,                (r.role in (                           select m.roleid                           tblssappsroleusermap m                           ) or r.role null)          order i.itemorder          xml path('item'), type        ) items tblatnot_menusections s exists (              select *              tblatnot_menuitems                inner join tblatnot_rolemenuaccess r                  on i.item = r.item ,                      r.valid = 1              s.section = i.section ,                    (i.valid = 1 or i.valid null) ,                   (r.role in (                              select m.roleid                              tblssappsroleusermap m                              ) or r.role null)              ) order s.sectionorder xml path('section'), root('menu')