SQL Tree to XML

Question

Thanks for the help, I am fairly new to XML.

I want to create a solid query that will turn this table structure into an xml document with hardcoding the ammount of levels this could go.

DECLARE @menugroups TABLE (
	groupid INT,
	parentid INT,
	grouptext VARCHAR(30)
)
DECLARE @menuitems TABLE(
	menuid INT,
	groupid INT,
	menutext VARCHAR(50),
	url VARCHAR(50)
)

INSERT INTO @menugroups (groupid,parentid,grouptext) VALUES ( 	/* groupid - INT */ 1,/* parentid - INT */ 0,/* grouptext - VARCHAR(30) */ 'Customer Service' ) 
INSERT INTO @menugroups (groupid,parentid,grouptext) VALUES ( 	/* groupid - INT */ 2,/* parentid - INT */ 1,/* grouptext - VARCHAR(30) */ 'Reports' ) 
INSERT INTO @menugroups (groupid,parentid,grouptext) VALUES ( 	/* groupid - INT */ 3,/* parentid - INT */ 1,/* grouptext - VARCHAR(30) */ 'Operations' )

INSERT INTO @menuitems (menuid,groupid,menutext,url) VALUES (	/* menuid - INT */ 1,/* groupid - INT */ 2,/* menutext - VARCHAR(50) */ 'Report1',/* url - VARCHAR(50) */ 'report1.com' ) 
INSERT INTO @menuitems (menuid,groupid,menutext,url) VALUES (	/* menuid - INT */ 2,/* groupid - INT */ 2,/* menutext - VARCHAR(50) */ 'Report2',/* url - VARCHAR(50) */ 'report2.com' ) 

INSERT INTO @menuitems (menuid,groupid,menutext,url) VALUES (	/* menuid - INT */ 3,/* groupid - INT */ 3,/* menutext - VARCHAR(50) */ 'Form1',/* url - VARCHAR(50) */ 'report2.com' ) 
INSERT INTO @menuitems (menuid,groupid,menutext,url) VALUES (	/* menuid - INT */ 4,/* groupid - INT */ 3,/* menutext - VARCHAR(50) */ 'Form2',/* url - VARCHAR(50) */ 'report2.com' ) 
/*
Customer Service
	Reports
		Report1
		Report2
		
	Operations
		Form1
		Form2
*/	
	

Answer 1

Here is a tutorial that shows how to generate an XML tree structure using TSQL. 

http://www.sqlservercentral.com/articles/61821/

---

Beyond Relational
SyntaxHelp.com

Answer 2

I want to do it without hardcoding the amount of levels this could go.

Without Unions

Answer 3

In most cases, you can figure out a maximum hierarchy level you need to support and apply it in your code. The concept of 'unlimited' hierarchies are not very common.

If you still want to go ahead with unlimited-unknown-number of hierarchies, I would suggest you use a recursive function - but you might have performance problems if the hierarchy is too deep.

Why do you want to do it 'without unions'? Is it a school assignment where you are not supposed to use certain keywords? 

---

Beyond Relational
SyntaxHelp.com