Migration of Oracle code to SQL Server with XML

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Migration of Oracle code to SQL Server with XML

Author

Topic

TajKazi
Posting Yak Master

101 Posts

Posted - 2014-12-10 : 07:52:34

ORACLE CODE-------------------------------------

select xmlelement("menu",
xmlagg(
xmlelement("section",
xmlattributes(ams.text as "text",
ams.url as "url",
ams.image_url as "image_url"
),
xmlforest((select xmlagg(
xmlelement("item",
xmlattributes(itm.text as "text",
itm.url as "url",
itm.image_url as "image_url"
),
)
)
from (select distinct pmi.section section,
pmi.text text,
pmi.url url,
pmi.image_url image_url,
pmi.item_order item_order
from tbl_atnot_menu_items pmi
left join tbl_atnot_role_menu_access prm on (prm.item = pmi.item and prm.valid = 1)
where (pmi.valid = 1 or pmi.valid is null)
and prm.role in (select distinct erm.role_id
from hail_ess_role_map erm
join hail_ess_users_extended_v eue on (eue.personid = erm.person_id)
where eue.is_active = 'Y'
and eue.personid = p_person_id
and erm.role_id = prm.role)
or prm.role is null
order by pmi.item_order
)itm
where itm.section = ams.section
) "items"
)
)
)) AS "Menu"
FROM (select distinct mse.section section,
mse.text text,
mse.url url,
mse.image_url image_url,
mse.section_order section_order
from tbl_atnot_menu_sections mse
join tbl_atnot_menu_items mit2 on(mit2.section = mse.section)
join tbl_atnot_role_menu_access rma2 on (rma2.item = mit2.item)
where mse.valid = 1
and mit2.valid = 1
and rma2.role in (select distinct erm2.role_id
from hail_ess_role_map erm2
join hail_ess_users_extended_v eue2 on (eue2.personid = erm2.person_id)
where eue2.is_active = 'Y'
and eue2.personid = p_person_id
and erm2.role_id = rma2.role)
or rma2.role is null
order by mse.section_order asc
) ams;

OUTPUT........................

my requirement as follows............

- <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>

TajKazi
Posting Yak Master

101 Posts

Posted - 2014-12-10 : 07:54:20

i not getting required output as per Oracles output

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-10 : 08:38:52

The output you posted from SQL is invalid XML. It seems to be missing things on the end. Please post the entire output.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-11 : 09:50:33

This is a very complex query. Here's what I would do: I'd build it up section by section. Start with the outside and work your way in. Build CTEs for the inner queries so that you can easily debug them separately. When you have the subqueries working correctly, begin putting them together, testing the combinations as you go.

TajKazi
Posting Yak Master

101 Posts

Posted - 2014-12-12 : 02:00:41

now i wrote this code ,, but one thing is remaining here i am not getting Item as a sub menu and remaining output is as per requirement n same as output of oracle code

select Section.Text,
Section.ImageUrl,
Item.Text,
Item.Url,
Item.ImageUrl
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 = 48
and rum.RoleId = rma.Role)
or rma.Role is null

)Item
on (Item.Section = Section.Section)

--Where Exists (select distinct mit1.Section Section,
-- mit1.Text Text,
-- mit1.Url Url,
-- mit1.ImageUrl ImageUrl,
-- mit1.ItemOrder ItemOrder
-- from tblATNOT_MenuItems mit1
-- join tblATNOT_RoleMenuAccess rma1 on (rma1.Item = mit1.Item and rma1.Valid = 1)
-- where (mit1.Valid = 1 or mit1.Valid is null)
-- --and rma1.VALID = 1
-- --and mit1.Section = Section.Section
-- --and ((rma1.Role is null)
-- and rma1.Role in (select distinct rum1.RoleId
-- from dbo.tblSSAppsRoleUserMap rum1
-- join dbo.viwSSAppsEmpMasterExtended vem1 on (vem1.PersonId = rum1.PersonId)
-- where vem1.IsEmployeeActive = 'Y'
-- and vem1.PERSONID = 1
-- and rum1.RoleId = rma1.Role)
--or rma1.Role is null )

order by Section.SectionOrder,
Item.ItemOrder
for xml auto, root('Menu')

now getting desired output as ......

<Menu>
<Section Text="Home" ImageUrl="/ATNOT/Include/Images/home.gif">
<Item Text="Home" Url="/ATNOT/Pages/Home.aspx" ImageUrl="/ATNOT/Include/Images/page_add.png" />
</Section>
<Section Text="Entry" ImageUrl="/ATNOT/Include/Images/page_go.png">
<Item Text="Attendance Entry" Url="/ATNOT/Pages/AttendanceEntry.aspx" ImageUrl="/ATNOT/Include/Images/page_add.png" />
<Item Text="My Attendance Requests" Url="/ATNOT/Pages/AttendanceRequests.aspx" ImageUrl="/ATNOT/Include/Images/page_add.png" />
</Section>
<Section Text="Claim" ImageUrl="/ATNOT/Include/Images/new_tour.gif">
<Item Text="Back Date OT Claim" Url="/ATNOT/Pages/BackDateClaim.aspx" ImageUrl="/ATNOT/Include/Images/page_add.png" />
<Item Text="Overtime Claim" Url="/ATNOT/Pages/OvertimeClaim.aspx" ImageUrl="/ATNOT/Include/Images/page_add.png" />
<Item Text="My Overtime Requests" Url="/ATNOT/Pages/OvertimeRequests.aspx" ImageUrl="/ATNOT/Include/Images/page_add.png" />
</Section>
</Menu>

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-12 : 08:49:40

Please post the output you are getting with your revised query and the output you want.

TajKazi
Posting Yak Master

101 Posts

Posted - 2014-12-14 : 23:08:15

required 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>

my output.........

<Menu>
<Section Text="Home" ImageUrl="/ATNOT/Include/Images/home.gif">
<Item Text="Home" Url="/ATNOT/Pages/Home.aspx" ImageUrl="/ATNOT/Include/Images/page_add.png" />
</Section>
<Section Text="Entry" ImageUrl="/ATNOT/Include/Images/page_go.png">
<Item Text="Attendance Entry" Url="/ATNOT/Pages/AttendanceEntry.aspx" ImageUrl="/ATNOT/Include/Images/page_add.png" />
<Item Text="My Attendance Requests" Url="/ATNOT/Pages/AttendanceRequests.aspx" ImageUrl="/ATNOT/Include/Images/page_add.png" />
</Section>
<Section Text="Claim" ImageUrl="/ATNOT/Include/Images/new_tour.gif">
<Item Text="Back Date OT Claim" Url="/ATNOT/Pages/BackDateClaim.aspx" ImageUrl="/ATNOT/Include/Images/page_add.png" />
<Item Text="Overtime Claim" Url="/ATNOT/Pages/OvertimeClaim.aspx" ImageUrl="/ATNOT/Include/Images/page_add.png" />
<Item Text="My Overtime Requests" Url="/ATNOT/Pages/OvertimeRequests.aspx" ImageUrl="/ATNOT/Include/Images/page_add.png" />
</Section>
</Menu>

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-15 : 00:50:51

OK so you're getting close. Now all you need to do is wrap the section where you pull the individual items in a subquery. Basically like:

select ...
, (select ... -- get the items) as Items
...

TajKazi
Posting Yak Master

101 Posts

Posted - 2014-12-15 : 02:04:01

as per your suggestion this is my code...

select Section.Text,
Section.ImageUrl,
(SELECT Item.Text,
Item.Url,
Item.ImageUrl
FOR XML PATH(''),TYPE, ELEMENTS) 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 = 48
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

<Menu>
<Section Text="Home" ImageUrl="/ATNOT/Include/Images/home.gif">
<items>
<Text>Home</Text>
<Url>/ATNOT/Pages/Home.aspx</Url>
<ImageUrl>/ATNOT/Include/Images/page_add.png</ImageUrl>
</items>
</Section>

but problem is still there ,,,

cause in sub nodes i need as follows...

- <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>

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-15 : 17:12:41

use;

mit.url as [@url]

to force an attribute

TajKazi
Posting Yak Master

101 Posts

Posted - 2014-12-16 : 00:39:23

i tried this but getting an error

then i tried following code.... like

select Section.Text,
Section.ImageUrl,

(select Item.Text "Item/@Text",
Item.Url "Item/@Url",
Item.ImageUrl "Item/@ImageUrl"
for xml path(''),type) as Items
............
........
...
order by Section.SectionOrder,
Item.ItemOrder
for xml auto, root('Menu')

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>

but here one problem is repeated entries of <Section Text="Entry"

which i do not want ... that should come once not get repeated

TajKazi
Posting Yak Master

101 Posts

Posted - 2014-12-16 : 08:36:54

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')

this code is giving now answer.......

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources