asked
SQL For Xml Path query to get nested data
I now have create an XML file but I’m currently having problems producing a nested XML format like below: 123 str1234 str1234 str1234 str1234 str1234 str1234 str1234 str1234 str1234 str1234 str1234 str1234 str1234 str1234 str1234 str1234 str1234 str1234 str1234 str1234 str1234 str1234 str1234 str1234 str1234 str1234 str1234 str1234 str1234 str1234 str1234 str1234 str1234 str1234 str1234 str1234 str1234 str1234 str1234 str1234 str1234 str1234 str1234 str1234 str1234 str1234 str1234 str1234 str1234 str1234 str1234 true str1234 123 str1234 str1234 str1234 str1234 str1234 str1234 str1234 str1234 str1234 str1234 str1234 123 123 123 Thank you!
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.
@Tom Staab. Thank you so much Tom. Your example and explanations are easy to follow.I wish the database will be set up the way you explain it because it will be much simpler for me. The problem is that I have only one table and I need to select the columns as different names to create the XML file as requested, thus the need to use XML path and root. I do have a schema also, but at no use since my table is different than what the schema shows. I have been working on this for days and waiting for you for a day it will not be a problem. Here is a link to the schema:
http://www.flhsmv.gov/FRManualFTP/TransactionReport.xsd THANK YOU SOOOOO MUCH!
@Tom I posted below the testing query to get the XML file first and then start creating my case statements and so on. Your second example works great. A little work around type and parenthesis but I made it work. The only problem I get now is to make the second select statement to appear at the bottom not top. Sorry for the variables it is easier for me to use them until I create the xml file. Thank you!!! ----THIS PART HERE NEEDS TO APPEAR ON THE BOTTOM -- str1234 123 123 123 --------------------------------------------------------------
15 People are following this question.
