I decided to write a second answer to try to avoid confusion. I will keep my first answer as an example of simple hierarchical XML with attributes derived from a hierarchy of tables joined together. It sounds like you have the following requirements: - single table input - hierarchical XML output - no attributes; use elements for all columns Given those requirements, I believe nested FOR XML PATH queries is your best answer. Here's a simple example based on those assumptions. I'll use the same data I used before but assume all data is in a single, denormalized ProfessorCourseStudents table or view. SELECT ( SELECT DISTINCT ProfessorId, ProfessorName , CourseNames = ( SELECT CourseName , Students = ( SELECT StudentName FROM ProfessorCourseStudents s WHERE s.ProfessorId = c.ProfessorId AND s.CourseId = c.CourseId FOR XML PATH(''), TYPE ) FROM ProfessorCourseStudents c WHERE c.ProfessorId = pcs.ProfessorId FOR XML PATH(''), TYPE ) FROM ProfessorCourseStudents pcs FOR XML PATH ('Professors'), TYPE ) FOR XML PATH('root') ; The output should be something like this: 14 Mary Jenkins Computer Science Bob Smith ... ... As I said before, I can't test this right now, so the syntax may not be 100% accurate, but please let me know whether or not you think that approach would work for you. As I review what I just wrote, I know it's not quite right, but I don't have time to figure it out right now. It will be much easier at home once I have a database to play with. I also have some big performance concerns with this approach using a denormalized table as the source.
The simplest method is to use the "FOR XML AUTO" clause at the end of your SQL SELECT statement, but the key to getting the XML nested correctly lies in the way you join your tables. I don't have access to a SQL instance to test this right now, but I'll try to explain with a simple example. Let's say you have a school database with 3 tables: Professors, Courses, and Students. It's easy to get hierarchical XML output showing each professor with his/her courses and the students taking each course. WITH Courses AS ( SELECT c.Name, cs.ProfessorId, cs.StudentId FROM dbo.Courses c INNER JOIN dbo.CourseSections cs ON cs.CourseId =
c.Id ) SELECT Professors.Name, Courses.Name, Students.Name FROM dbo.Professors INNER JOIN Courses ON Course.ProfessorId =
Professor.Id INNER JOIN dbo.Students on
Students.Id = Courses.StudentId FOR XML AUTO ; There are some key points to notice in that query. First of all, I didn't use table aliases. That is because the table names will be used as XML tags. If I use aliases, those aliases will become the tags. Secondly, I used the CTE to get the course name combined with the pointers to the other tables because I wanted my final query to be a straight hierarchy of 1-to-many and 1-to-many. Many-to-many relationships and reference data lookups skew the XML output. The result of the above query should be something simple like this: I hope that helps. I have a play environment at home I can use to create more complex examples if necessary, but I won't be able to get to that for a few more hours. Of course, there are lots of other people here who might also have good suggestions for you between now and then. Good luck.