top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

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

0 votes
261 views

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.

 

  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.

posted Jan 10, 2018 by Shivaranjini

  Promote This Article
Facebook Share Button Twitter Share Button LinkedIn Share Button


Related Articles

Transform XML Output Of SQL Query To HTML Using "For XML" AUTO Statement With XSLT In C#

This article describes how to transform the XML output of SQL query statements using FOR XML AUTO to HTML using XSLT.

Normally, we execute the query using ExecuteNonQuery statement and fill the output into a data table. We can access the data in the data table by giving column name and row number in the looping method. This makes us work on extra coding and takes a lot of time to get the correct data from executed query. So, I moved to XML because, in XML, we can fetch the values easily and quickly without writing too much code.     

FOR XML Clause in SQL

Basically, we have four types of FOR XML clause in SQL.

  • FOR XML RAW
  • FOR XML AUTO
  • FOR XML EXPLICIT
  • FOR XML PATH

Each of the above types satisfies its own need. When you want full control over the produced XML, you use FOR XML EXPLICIT, but it is difficult to understand, read, or maintain. FOR XML AUTO produces the most readable SELECT statement. The RAW option is rarely used and therefore not discussed. The PATH option allows you to mix attributes and elements easier. Here, I gona use FOR XML AUTO.

Table Name - Contact_Details

Contact_IDContact_NumberContact_NameContact_Location
001088888888MohanChennnai
002099999999AbilashBangalore


If we want the output as in XML format, we need to add the FOR XML AUTO statement.

 

  1. SELECT * FROM Contact_Details FOR XML AUTO  

This query gives the output like,

  1. < Contact_Details Contact_ID=’001’ Contact_Number=’ 088888888’ Contact_Name=’Mohan’ Contact_Location=’Chennai’/>  
  2. < Contact_Details Contact_ID=’002’ Contact_Number=’ 099999999’ Contact_Name=’Abilash’ Contact_Location=’Bangalore’/>  

The XML which we got in output is not in user-friendly format. We need each field as an attribute so, that we can access the values using XPATH. To achieve that, we need to add the ELEMENTS parameter with the query.

 

  1. SELECT * FROM Contact_Details FOR XML AUTO, ELEMENTS  

This query gives the output like,

  1. < Contact_Details>  
  2.     <Contact_ID>001</Contact_ID>  
  3.     <Contact_Number>088888888</Contact_Number>  
  4.     <Contact_Name>Mohan</Contact_Name>  
  5.     <Contact_Location>Chennai</Contact_Location>  
  6. </ Contact_Details>  
  7. < Contact_Details>  
  8.     <Contact_ID>002</Contact_ID>  
  9.     <Contact_Number>099999999</Contact_Number>  
  10.     <Contact_Name>Abilash</Contact_Name>  
  11.     <Contact_Location>Bangalore</Contact_Location>  
  12. </ Contact_Details>  

So, this is the exact XML format we need.

Now, we are going to create XML DOCUMENT and write this output XML into that XML document in coding. To read the query in XML format, we need to execute the query using ExecuteXmlReader() function. 

  1. XmlReader XMLReader = new XmlReader();  
  2. XmlDocument XmlDoc = new XmlDocument();  
  3. SqlCommand Cmd = new SqlCommand(Query, Conn);  
  4. XMLReader = cmd.ExecuteXmlReader();  
  5. XPathDocument xPathDoc = new XPathDocument(XMLReader);  
  6. XPathNavigator xPathNavi = xPathDoc.CreateNavigator();  
  7. XmlDoc.LoadXml(XpathNavi.OuterXml);  

Now, we got the full structured XML Document. Using this, we will transform the data into HTML format using XSLT.

Before transforming, we need to create one XSLT file to assign the values from XMLDocument to HTML format.

Contact_Details.xslt

  1. <?xml version=”1.0” encoding=”utf-8”?>  
  2. <xsl:stylesheet version=”1.0” xmlns:xsl=”http://www.w3.org/1999/XSL/Tranform”>  
  3.     <xsl:output method=”xml” indent=”yes” omit-xml-declaration=”yes”>  
  4.         <xsl:template match=”/*”>  
  5.             <table>  
  6.                 <tr>// Column heading  
  7.                     <td>ContactID</td>  
  8.                     <td>ContactNumber</td>  
  9.                     <td>ContName</td>  
  10.                     <td>ContactLocation</td>  
  11.                 </tr>  
  12.                 <xsl:for-each select=”/ Contact_Details”>  
  13.                     <tr>// inserting XML data into HTML table  
  14.                         <td>  
  15.                             <xsl:value-of select=”/Contact_details/Contact_ID” </td> // using XPath we can access the value from XML Document  
  16.                                 <td>  
  17.                                     <xsl:value-of select=”/Contact_details/Contact_Number” </td>  
  18.                                 <td>  
  19.                               <xsl:value-of select=”/Contact_details/Contact_Name” </td>  
  20.                           <td>  
  21.                        <xsl:value-of select=”/Contact_details/Contact_Location” </td>  
  22.                     </tr>  
  23.                 </xsl:for-each>  
  24.             </table>  
  25.         </xsl:template>  
  26. </xsl:stylesheet>  

Now, we have XML Document and XSLT file. In the next step, we are going to transform the XML Document into HTML using XSLT. To do this, we pass the XMLDocument and XSLT file to separate functions and get fully formed HTML strings in return.

  1. Public static string TransformXMLTOHTML(XmlDocument XMLDoc, String XSLTFilename) {  
  2.     StringWriter HTMLString = new StringWriter();  
  3.     XslCompiledTransform Xmltransform = new XslCompiledTransform();  
  4.     XmlTransform.Load(XSLTFilename); // Load XSLT File  
  5.     Xmltranform.Transform(XMLDoc, null, Result); // transform XML into HTML  
  6.     Return HTMLString.ToString();  
  7. }  
 READ MORE
Accessing XML Data From XML Document In C# Console Application

In this article, I am going to give you the best way to access XML data from an XML document and also how to find the common record node count.

Students.xml

  1. <Data>  
  2.     <Student Name="Saravanan"> // Node with same name  
  3.   
  4.   
  5.         <Address>Address1</Address>  
  6.         <SurName> SurName1  
  7.         </ SurName>  
  8.         <City>City1</City>  
  9.         <State>State1</State>  
  10.     </Student>  
  11.     <Student Name="Mohan">  
  12.         <SurName> SurName2  
  13.         </ SurName>  
  14.         <Address>Address2</Address>  
  15.         <City>City2</City>  
  16.         <State>State2</State>  
  17.     </Student>  
  18.     <Student Name="Fayaz">  
  19.         <SurName> SurName3  
  20.         </ SurName>  
  21.         <Address>Address3</Address>  
  22.         <City>City3</City>  
  23.         <State>State3</State>  
  24.     </Student>  
  25.     <Student Name="Siva">  
  26.         <SurName> SurName4  
  27.         </ SurName>  
  28.         <Address>Address4</Address>  
  29.         <City>City4</City>  
  30.         <State>State4</State>  
  31.     </Student>  
  32.     <Student Name="Saravanan"> // Node with same name  
  33.   
  34.   
  35.         <Address>Address5</Address>  
  36.         <SurName> SurName5  
  37.         </ SurName>  
  38.         <City>City5</City>  
  39.         <State>State5</State>  
  40.     </Student>  
  41. </Data>  

Step 1

Before we do anything with the XML document, we have to first load the XML file. The following code is the common way to load a XML document.

Note

Before dealing with XML, initilize the XML Namespace, i.e., using system.xml;

  1. XMLDocument  StudentDocument = new xmlDocument();  
  2. studentDocument("give the XML Documnet Path here e.g C://Studentrecords/Student.xml");  

Step 2

Now, we are going to access the XML data from XML document by using XPath.

XPath

XPath is used to navigate through elements and attributes in an XML document. In other words, XPath uses the path expressions to select nodes or node-sets in an XML Document.

Here is the code:

By using XmlNode keyword, we can access the node value.

 

  1. XmlNode Singlenode=StudentDocument.SelectSingleNode("XPath here E.g /Data/Students/Student[@ Name='Mohan']/Address "); // accessing one student record  

Note

Still, the data is in XML format. So we assign the node value to another string variable so that the value can be used for further purpose.

 

  1. string StudentAddress= Singlenode.InnerText;  

Now, consider the situation of needing to get the correct data from XML document if two students have the same name. Then, by comparing the surname, we can get the correct data. Try the following code.

Note

Consider the above XML document. It has two nodes with the same name "Saravanan".

  1. <Student Name="Saravanan"> // Node with same name  
  2.     <Address>Address1</Address>  
  3.     <SurName> SurName1</ SurName>  
  4.         <City>City1</City>  
  5.         <State>State1</State>  
  6. </Student>  
  7. <Student Name="Saravanan"> // Node with same name  
  8.     <Address>Address5</Address>  
  9.     <SurName> SurName5</ SurName>  
  10.         <City>City5</City>  
  11.         <State>State5</State>  
  12. </Student>  

Eg

User input is---- Student Name - Saravanan and SurName-SurName5

To find the number of nodes with the same value, try the following code.

 

  1. XmlNodeList Lists = Students.SelectNodes(" /Data/Students/Student[@ Name='Saravanan']/Address ");  

Note: For getting a single node value, we use "XmlNode" Keyword, but for finding more than one nodes with the same value, we have to use "XmlNodeList".

  1. if (Lists.count > 1) {  
  2.     //if this loop is executes ,mean there is morethan one node with same value  
  3.     foreach(XmlNode List in Lists) {  
  4.         //this loop is for comparing the surname to get correct node value  
  5.         SingleNodeSurName = StudentDocument.SelectSingleNode("XPath here E.g /Data/Students/Student[@ Name='Mohan']/SurName ").InnerText;  
  6.         //Now compare the Address with user input address  
  7.         if (SingleNodeSurName.toUpper() == UserInputSurName.toUpper()) {  
  8.             //if two Surname is same it get all other data's by the same above method  
  9.             Address,  
  10.             City,  
  11.             State  
  12.         }  
  13.     }  
  14. }  

I hope this article is very useful. Thank You.

 READ MORE
...