problem with recursive cte query

gswartz

I am running into 2 problems with this cte query. First off, it's not returning the results in the right order. What I mean is that, as you can see from the graph, the first row (tabid=4) should be followed by rows 3,4 and 5 but it's throwing in a row between. No matter how I try and order the results, it's not working as it should. The tabindex field is a user defined field as to what order the tabs should show up for the user so I have to order by that field at some point.

ParentTabId	TabId	Title	Link	TabIndex
1	4	Personal Info	/Employee/employeeAdmin.aspx	1
1	5	Employment Info	/Employee/positionInfo.aspx	2
4	8	Dependents	/Employee/dependents.aspx	3
4	169	Emergency Contacts	/Employee/Contacts.aspx	2
4	170	Demographics	/Employee/EmployeeAdmin.aspx	1
1	172	Employment Files	/Employee/empFiles.aspx	3
172	210	New Hire Paperwork	/Employee/empFiles.aspx	1
172	211	Form I-9	/Employee/I9.aspx	2
172	212	General	/Employee/employment.aspx	3
172	213	Notes	/Employee/notes.aspx	4
172	214	Pre-Employment	/Employee/preemployment.aspx	5
172	217	Protected Health Information	/Employee/protectedhealth.aspx	6
1	220	View All	/Employee/viewAll.aspx	4

Problem 2 is that I need to format all of this as heirarchal xml but when I output it as xml in the query itself, every element is coming through as MainTabs (the cte name) instead of nesting rows 3,4 & 5 under tab id 4. So, how do I (if it's even possible) fix these two things If I can't do this in a query, how would you suggest getting an xml formatted result set from a table structure like this Structure meaning parenttabid = tabid of another row ordered by the tabid and then tabindex Thanks.

with MainTabs(ParentTabId, TabId, Title, Link, tabindex) as
(
select ParentTabId, TabId, Title, Link, tabindex from Tabs where parenttabid = 1

UNION ALL

select t.ParentTabId, t.TabId, t.Title, t.Link, t.tabindex from Tabs t inner join
MainTabs on MainTabs.TabId = t.ParentTabId
)
select ParentTabId, TabId, Title, Link, tabindex from MainTabs order by tabid, tabindex

Mark - SQL

You can generate hierarchical XML using this

CREATE FUNCTION dbo.SubTree(@TabId int)
RETURNS XML
WITH RETURNS NULL ON NULL INPUT
BEGIN RETURN
(SELECT ParentTabId as "@ParentTabId",
TabId as "@TabId",
Title as "@Title",
Link as "@Link",
tabindex as "@tabindex",
dbo.SubTree(TabId)
FROM Tabs
WHERE ParentTabId=@TabId
ORDER BY TabId
FOR XML PATH('Tabs'),TYPE)
END

GO

SELECT ParentTabId as "@ParentTabId",
TabId as "@TabId",
Title as "@Title",
Link as "@Link",
tabindex as "@tabindex",
dbo.SubTree(TabId)
FROM Tabs
WHERE parenttabid = 1
ORDER BY TabId
FOR XML PATH('Tabs') , ROOT('MyRoot'), TYPE

gswartz

Wonderful, thank you so much!!! Now, if I can just figure out what it's all doing:)

viral@excellenceinfonet.com

SQL Server 2005 has a maximum limit of 32 recursively nested function invocations. If your parts hierarchy exceeds the limit, you will need to use the old approach of getting the XML in flat form and applying an XSLT style sheet to create the hierarchy.

Its mentioned here : http://msdn2.microsoft.com/en-us/library/ms345137.aspx

Can you suggest any alternate option

problem with recursive cte query

Re: problem with recursive cte query

Re: problem with recursive cte query

Re: problem with recursive cte query