question

taj avatar image
taj asked

SQL Server output as XML

**SQL Server code which generates output as XML i having problem with this that not getting output as per requirement ** select Section.Text, Section.ImageUrl, (select Item.Text "Item/@Text", Item.Url "Item/@Url", Item.ImageUrl "Item/@ImageUrl" for xml path(''),type) as Items from tblATNOT_MenuSections Section join (select mit.Text, mit.Url, mit.ImageUrl, mit.Section, mit.ItemOrder from tblATNOT_MenuItems mit join tblATNOT_RoleMenuAccess rma on (rma.Item = mit.Item and rma.Valid = 1) where (mit.Valid = 1 or mit.Valid is null) and rma.Role in (select distinct rum.RoleId from tblSSAppsRoleUserMap rum join viwSSAppsEmpMasterExtended vem on (vem.PersonId = rum.PersonId) where vem.IsEmployeeActive = 'Y' and vem.PersonId = 86 and rum.RoleId = rma.Role) or rma.Role is null )Item on (Item.Section = Section.Section) order by Section.SectionOrder, Item.ItemOrder for xml auto, root('Menu') **output** **but required output as follows** - - - - - - - **i am just close to my requirement but not getting what little change made me happy, please tell me someone**
xmlsqlserver
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 answered
select Section.Text, Section.ImageUrl, ( select Item.Text "Item/@Text", Item.Url "Item/@Url", Item.ImageUrl "Item/@ImageUrl" from tblATNOT_MenuItems as Item inner join tblATNOT_RoleMenuAccess as rma on Item.Item = rma.Item and rma.Valid = 1 where Section.Section = Item.Section and (Item.Valid = 1 or Item.Valid is null) and (rma.Role in (select distinct rum.RoleId from tblSSAppsRoleUserMap rum join viwSSAppsEmpMasterExtended vem on (vem.PersonId = rum.PersonId) where vem.IsEmployeeActive = 'Y' and vem.PersonId = @p_PersonId and rum.RoleId = rma.Role) or rma.Role is null) order by Item.ItemOrder for xml path(''), type ) as Items from tblATNOT_MenuSections as Section where exists ( select * from tblATNOT_MenuItems as Item inner join tblATNOT_RoleMenuAccess as rma on Item.Item = rma.Item and rma.Valid = 1 where Section.Section = Item.Section and (Item.Valid = 1 or Item.Valid is null) and (rma.Role in (select distinct rum.RoleId from tblSSAppsRoleUserMap rum join viwSSAppsEmpMasterExtended vem on (vem.PersonId = rum.PersonId) where vem.IsEmployeeActive = 'Y' and vem.PersonId = @p_PersonId and rum.RoleId = rma.Role) or rma.Role is null) ) order by Section.SectionOrder for xml auto, root('Menu')
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 ·
this code giving output as per my requirement ..
0 Likes 0 ·
Kev Riley avatar image Kev Riley ♦♦ commented ·
OK - I don't have any data to test against, so if this works for you then great!
0 Likes 0 ·
Kev Riley avatar image
Kev Riley answered
You need a sub-query to get the distinct items before joining onto the section table. This also will have to return the xml as not XML type, so that you can distinct/group it. But once you have the data, it is simple to convert it back to xml. Something like: select Section.Text, Section.ImageUrl, cast(Items3.Items as xml) from tblATNOT_MenuSections Section join ( select Item2.Section , ( select Item.Text "Item/@Text" , Item.Url "Item/@Url" , Item.ImageUrl "Item/@ImageUrl" from tblATNOT_MenuItems Item where Item.Section = Item2.Section for xml path('') ) as Items from tblATNOT_MenuItems Item2 group by Item2.Section ) Items3 on Items3.Section = Section.Section for xml auto, root('Menu')
1 comment
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 ·
still not getting required output
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.