Generate XML from SQL Server 2005

Question

Generate XML from SQL Server 2005

pprasadravi

Grasshopper

Points: 13
More actions
December 28, 2009 at 11:17 pm

#136155

Hi,
I have four tables(Course, group, unit and lesson). Each table contains ID ,Name and URL Coliumn and etc. The realation between tables are 1 to many. I want to generate the XML like below. The pupose of this XML is to map my Menu.
Could you please help me to write this query?
<Course Name ='Course1' ID ='100' URL='~/temp.aspx'>
<Group Name ='Group1' ID ='100' URL='~/Grouptemp.aspx'>
<Unit Name ='Unit1' ID ='100' URL='~/Unittemp.aspx'>
<Lesson Name ='Lesson1' ID ='100' URL='~/Lessontemp.aspx' />
<Lesson Name ='Lesson2' ID ='101' URL='~/Lessontemp.aspx' />
</Unit>
<Unit Name ='Unit1' ID ='101' URL='~/Unittemp.aspx'>
<Lesson Name ='Lesson1' ID ='102' URL='~/Lessontemp.aspx' />
<Lesson Name ='Lesson2' ID ='103' URL='~/Lessontemp.aspx' />
</Unit>
</Group>
<Group Name ='Group1' ID ='104' URL='~/Grouptemp.aspx'>
<Unit Name ='Unit1' ID ='105' URL='~/Unittemp.aspx'>
<Lesson Name ='Lesson1' ID ='106' URL='~/Lessontemp.aspx' />
<Lesson Name ='Lesson2' ID ='107' URL='~/Lessontemp.aspx' />
</Unit>
<Unit Name ='Unit1' ID ='105' URL='~/Unittemp.aspx'>
<Lesson Name ='Lesson1' ID ='106' URL='~/Lessontemp.aspx' />
<Lesson Name ='Lesson2' ID ='107' URL='~/Lessontemp.aspx' />
</Unit>
</Group>
</Course>
I tried the below query to get the above output but it didn't work.
select 1 as Tag,
NULL as parent,
Name as [Course!1!CourseName],
Null as [Group!2!Name],
Null as [Unit!3!Name],
Null as [Lesson!4!Name]
From tblCourse
WHERE tblCourse.CourseID = 2
UNION ALL
(select 2,
1,
tblCourse.Name as [Course!1!CourseName],
tblGroup.Name as [Group!2!Name],
NULL as [Unit!3!Name],
NULL as [Lesson!4!Name]
From tblGroup INNER JOIN tblCourse ON tblGroup.CourseID = tblCourse.CourseID
WHERE tblCourse.CourseID = 2)
UNION ALL
(Select 3,
2,
tblCourse.Name as [Course!1!CourseName],
tblGroup.Name as [Group!2!Name],
tblUnit.Name as [Unit!3!Name],
Null as [Lesson!4!Name]
FROM tblUnit Inner join tblGroup ON tblUnit.GroupID = tblGroup.GroupID
INNER join tblCourse ON tblCourse.CourseID = tblGroup.CourseID
WHERE tblCourse.CourseID = 2)
UNION ALL
(Select 4,
3,
tblCourse.Name as [Course!1!CourseName],
tblGroup.Name as [Group!2!Name],
tblUnit.Name as [Unit!3!Name],
tblLesson.Name as [Lesson!4!Name]
From tblLesson inner join tblUnit on tblUnit.UnitID = tblLesson.UnitID
INNER join tblGroup on tblGroup.GroupID = tblUnit.GroupID
Inner join tblCourse on tblCourse.CourseID = tblCourse.CourseID
WHERE tblCourse.CourseID = 2)
order by [Group!2!Name], [Unit!3!Name],[Lesson!4!Name],parent
FOR XML EXPLICIT

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Mike01 SSChampion Points: 11395 More actions · Answer 1

Can you provide Table Layouts and sample data? See the first link in my signature block to see how to post to this forum

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Mauve SSChampion Points: 11316 More actions · Answer 2

Since you are just starting out using XML I suggest that you read Jacob Sebastian's articles on working with XML.

The article directly related to your problem would be:

XML Workshop I - Generating XML output using FOR XML with AUTO and RAW http://www.sqlservercentral.com/articles/SS2K5+-+XML/3022/

In addition to the documentation in the Books Online (BOL), and Jacob's nice tutorials, here are links to a number of Microsoft Technical articles that describe how SQL Server 2005 supports XML:

XML Support in Microsoft SQL Server 2005

http://msdn.microsoft.com/en-us/library/ms345117(SQL.90).aspx

XML Options in Microsoft SQL Server 2005

http://msdn.microsoft.com/en-us/library/ms345110(SQL.90).aspx

What's New in FOR XML in Microsoft SQL Server 2005

http://msdn.microsoft.com/en-us/library/ms345137(SQL.90).aspx

XML Indexes in SQL Server 2005

http://msdn.microsoft.com/en-us/library/ms345121(SQL.90).aspx

XML Best Practices for Microsoft SQL Server 2005

http://msdn.microsoft.com/en-us/library/ms345115(SQL.90).aspx

[font="Arial Narrow"](PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.[/font]