Post
Ask Question

Construct Readable XML Output From SQL Query From Two Or More Tables Using “For XML PATH” Statement

Before you read this article, please go through the following links -

In my previous article, I discussed how to get an XML format output from query and process that by using XML AUTO statement, and I gave different types of “for XML”.

In this article, I'm going to use XML RAW type to get XML format output from the data of two tables. Here, a question may rise, why can’t we do that by XML AUTO type. Yes, we can. But when we using XML AUTO, the output XML will consist too many XML nodes and there is no order in the XML nodes when we use JOIN statement in the query when we use two tables in single query. You can understand this in the following explanation.

Table: Student_Details

Student_IDContact_NumberStudent_NameStudent_Location
001088888888MohanChennai
002099999999AbilashBangalore

Table: Student_Marks

Student_IDExam_TypeScored_MarkScored_Percentage
001AnnualExam110086
002AnnualExam105080

So, these are my tables. The first table has the basic information about students while the second table has the marks details.

Here, our task is to get the information about the students and attach the marks details with each student, based on the StudentID because here, StudentID is the primary key for both tables. This can be done in different ways. I am going to do that using XML Raw elements.

Let’s write the query to select the values using XML AUTO elements.

  1. SELECT Student_ID,Contact_Number,Student_Name,Student_Location,MarkDetails.Exam_Type AS ExamType,MarkDetails.Scored_Mark AS Marks,MarkDetails.Scored_Percentage AS Percentage FROM Student_Details LEFT JOIN Student_Marks AS MarkDetails ON MarkDetails.Student_ID=Student_ID for XML AUTO,ELEMENT  

This query will give output like the following.

  1. < Student_Details>  
  2.     < Student_ID>001  
  3.         < /Student_ID>  
  4.             < Contact_Number>088888888  
  5.                 < /Contact_Number>  
  6.                     < Student_Name>Mohan  
  7.                         < /Student_Name>  
  8.                             < Student_Location>Chennai  
  9.                                 < /Student_Location>  
  10.                                     < ExamType>  
  11.                                         < ExamType>AnnualExam</ ExamType>  
  12.                                         < Marks>  
  13.                                             < Marks>1100</ Marks>  
  14.                                             < Percentage>  
  15.                                                 < Percentage>86</ Percentage>  
  16.                                             </ Percentage>  
  17.                                         </ Marks>  
  18.                                     </ ExamType>  
  19.                                 </ Student_Details>  
  20.                                 < Student_Details>  
  21.                                     < Student_ID>002  
  22.                                         < /Student_ID>  
  23.                                             < Contact_Number>099999999  
  24.                                                 < /Contact_Number>  
  25.                                                < Student_Name>Abilash  
  26.                                               < /Student_Name>  
  27.                                            < Student_Location>Bangalore  
  28.                                          < /Student_Location>  
  29.                                       < ExamType>  
  30.                                     < ExamType>AnnualExam</ ExamType>  
  31.                                   < Marks>  
  32.                                < Marks>1050</ Marks>  
  33.                             < Percentage>  
  34.                          < Percentage>80</ Percentage>  
  35.                      </ Percentage>  
  36.                </ Marks>  
  37.        </ ExamType>  
  38.  </ Student_Details>  

The output XML format is not what we have expected. To overcome this, we are moving to XML RAW type method to get the well designed XML with root node.

Let's try the same query with XML RAW Type.

  1. SELECT Student_ID,Contact_Number,Student_Name,Student_Location,MarkDetails.Exam_Type AS ExamType,MarkDetails.Scored_Mark AS Marks,MarkDetails.Scored_Percentage AS Percentage FROM Student_Details LEFT JOIN Student_Marks AS MarkDetails ON MarkDetails.Student_ID=Student_ID for XML RAW(‘Student’),ELEMENTS,TYPE  

This query will produce the output like following.

  1. < Student>  
  2.       < Student_ID>001< /Student_ID>  
  3.          < Contact_Number>088888888< /Contact_Number>  
  4.             < Student_Name>Mohan< /Student_Name>  
  5.                < Student_Location>Chennai< /Student_Location>  
  6.                   < ExamType>AnnualExam</ ExamType>  
  7.                      < Marks>1100</ Marks>  
  8.                      Percentage>86</ Percentage>  
  9.                   </ Student>  
  10.                   < Student>  
  11.                < Student_ID>002< /Student_ID>  
  12.             < Contact_Number>099999999< /Contact_Number>  
  13.          < Student_Name>Abilash< /Student_Name>  
  14.       < Student_Location>Bangalore< /Student_Location>  
  15.       < ExamType>AnnualExam</ ExamType>  
  16.       Marks>1050</ Marks>  
  17.      < Percentage>80</ Percentage>  
  18. </ Student>  

The output XML which we got now is well-formatted XML Document and in more user understandable form.

By using this, we can do more process in coding. Please refer my previous article where I have given how to transform the constructed XML into HTML document using XSLT.

I hope this article is useful.


Similar Articles