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_ID	Contact_Number	Student_Name	Student_Location
001	088888888	Mohan	Chennai
002	099999999	Abilash	Bangalore

Table: Student_Marks

Student_ID	Exam_Type	Scored_Mark	Scored_Percentage
001	AnnualExam	1100	86
002	AnnualExam	1050	80

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.

This query will give output like the following.