XML Creation using LINQ to XML

There are many different techniques to use by which you can create an XML document in C#. One of them is LINQ to XML which we are going to discuss in this article.

Let’s say we need to create an XML as below:

<?xml version="1.0" encoding="UTF-8"?>
<Parent>
<Header>
<FileDetails>
<FileName>RandomFile</FileName>
<FileVersion>1.0</FileVersion>
</FileDetails>
</Header>
<Body>
<Infos>
<Info Type="Information1">This is Information1</Info>
<Info Type="Information2">This is Information2</Info>
</Infos>
<Users>
<UserDetails>
<Name>
<FirstName>Vipul</FirstName>
<MiddleName/>
<LastName>Malhotra</LastName>
</Name>
<DateOfBirth>12-Apr-1990</DateOfBirth>
</UserDetails>
</Users>
</Body>

Let’s break the creation of the file in two parts so as to be able to see more features.

We will first create the below Xml:

<?xml version="1.0" encoding="UTF-8"?>
<Parent>
<Header>
<FileDetails>
<FileName>RandomFile</FileName>
<FileVersion>1.0</FileVersion>
</FileDetails>
</Header>
<Body>
<Infos>
<Info Type="Information1">This is Information1</Info>
<Info Type="Information2">This is Information2</Info>
</Infos>
</Body>

In order to create this, we will first define an XDocument with the parent root as below:

XDocument doc = new XDocument(new XElement("Parent"));

After this, we will use this “doc” as the root of the file and will writing nested XElement to it.

Let’s first create the Header portion of the xml.

Please notice that the XElement “Header “ is added as a new element and the further elements are added as nested to this “Header” element. It is due to the reason that the elements are sub-elements of “Header”. Further “FileName” and “FileVersion” element is a sub-element of “FileDetails”

In the same way, we would add another section to the root of the doc. This section would be “Body”.

The code for the same would be as:

This follows the same logic that “Body” is also sub-node of the root “parent” and so it is added directly to the root. Whereas , the element “Infos” is sub-element of “Body” and is so added in the way above. Same goes for “Info” which is a further sub-element of “Infos”.

Also notice how an attribute is added to each of the “Info” element using XAttribute.

After this, we further need to add the below section as sub-nodes of “Body” and not the root of the application:

<Users>
<UserDetails>
<Name>
<FirstName>Vipul</FirstName>
<MiddleName/>
<LastName>Malhotra</LastName>
</Name>
<DateOfBirth>12-Apr-1990</DateOfBirth>
</UserDetails>
</Users>

In order to do that, we would make sure that the code starts appending the code inside the “Body” tag of the already created xml.

Using XDocument, we can search for the node “Body” and then start adding node XElements to it .

Searching a node Is done using:

Further adding more elements to it is done using the below code:

The logic behind the hierarchy is the same as that discussed above.

The code can also be used inside a loop in case we need to add many similar sections to a particular node. Like in this case there can be many users and all of their details would have to be added in different UserDetails section inside the “Body” node.

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_ID	Contact_Number	Contact_Name	Contact_Location
001	088888888	Mohan	Chennnai
002	099999999	Abilash	Bangalore

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

SELECT * FROM Contact_Details FOR XML AUTO

This query gives the output like,

< Contact_Details Contact_ID=’001’ Contact_Number=’ 088888888’ Contact_Name=’Mohan’ Contact_Location=’Chennai’/>
< 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.

SELECT * FROM Contact_Details FOR XML AUTO, ELEMENTS

This query gives the output like,

< Contact_Details>
<Contact_ID>001</Contact_ID>
<Contact_Number>088888888</Contact_Number>
<Contact_Name>Mohan</Contact_Name>
<Contact_Location>Chennai</Contact_Location>
</ Contact_Details>
< Contact_Details>
<Contact_ID>002</Contact_ID>
<Contact_Number>099999999</Contact_Number>
<Contact_Name>Abilash</Contact_Name>
<Contact_Location>Bangalore</Contact_Location>
</ 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.

XmlReader XMLReader = new XmlReader();
XmlDocument XmlDoc = new XmlDocument();
SqlCommand Cmd = new SqlCommand(Query, Conn);
XMLReader = cmd.ExecuteXmlReader();
XPathDocument xPathDoc = new XPathDocument(XMLReader);
XPathNavigator xPathNavi = xPathDoc.CreateNavigator();
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

<?xml version=”1.0” encoding=”utf-8”?>
<xsl:stylesheet version=”1.0” xmlns:xsl=”http://www.w3.org/1999/XSL/Tranform”>
<xsl:output method=”xml” indent=”yes” omit-xml-declaration=”yes”>
<xsl:template match=”/*”>
<table>
<tr>// Column heading
<td>ContactID</td>
<td>ContactNumber</td>
<td>ContName</td>
<td>ContactLocation</td>
</tr>
<xsl:for-each select=”/ Contact_Details”>
<tr>// inserting XML data into HTML table
<td>
<xsl:value-of select=”/Contact_details/Contact_ID” </td> // using XPath we can access the value from XML Document
<td>
<xsl:value-of select=”/Contact_details/Contact_Number” </td>
<td>
<xsl:value-of select=”/Contact_details/Contact_Name” </td>
<td>
<xsl:value-of select=”/Contact_details/Contact_Location” </td>
</tr>
</xsl:for-each>
</table>
</xsl:template>
</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.

Public static string TransformXMLTOHTML(XmlDocument XMLDoc, String XSLTFilename) {
StringWriter HTMLString = new StringWriter();
XslCompiledTransform Xmltransform = new XslCompiledTransform();
XmlTransform.Load(XSLTFilename); // Load XSLT File
Xmltranform.Transform(XMLDoc, null, Result); // transform XML into HTML
Return HTMLString.ToString();
}

Export An Excel Using Closed XML

Introduction

In today's tech world, most of the applications being developed under Logistics, Inventory, Internal Transaction and other domains require day-to-day data in excel files and prefer excel file operations in their applications.

I will be sharing one of the Nuget Package tools which, with very minimal lines of code, will export an excel file for us.

The Tool is Closed XML.

Just write a few lines of code and done! It is developer friendly. If we have used the Open XML, there are a lot of lines of code which are required to export an excel from data. We can create an excel file of 2007/2010 configuration without an Excel application.

To add the closed XML package, we add it directly through the user interface from the Nuget Gallery and also, we can use the Package Manager console to add the package using the below command

PM> Install-Package ClosedXML

Snippet

DataTable dt = new DataTable();
dt.Columns.AddRange(new DataColumn[3]
{
new DataColumn("Id", typeof(int)), new DataColumn("Name", typeof(string)), new DataColumn("Country", typeof(string))
});
dt.Rows.Add(1, "C Sharp corner", "United States");
dt.Rows.Add(2, "Suraj", "India");
dt.Rows.Add(3, "Test User", "France");
dt.Rows.Add(4, "Developer", "Russia"); //Exporting to Excel
string folderPath = "C:\\Excel\\";
if (!Directory.Exists(folderPath))
{
Directory.CreateDirectory(folderPath);
}
//Codes for the Closed XML
using (XLWorkbook wb = new XLWorkbook())
{
wb.Worksheets.Add(dt, "Customers");
//wb.SaveAs(folderPath + "DataGridViewExport.xlsx");
string myName = Server.UrlEncode("Test" + "_" + DateTime.Now.ToShortDateString() + ".xlsx");
MemoryStream stream = GetStream(wb);
// The method is defined below
Response.Clear();
Response.Buffer = true;
Response.AddHeader("content-disposition", "attachment; filename=" + myName);
Response.ContentType = "application/vnd.ms-excel";
Response.BinaryWrite(stream.ToArray());
Response.End();
}

The above code instantiates a data table, with few data initializations.

public MemoryStream GetStream(XLWorkbook excelWorkbook)
{
MemoryStream fs = new MemoryStream();
excelWorkbook.SaveAs(fs);
fs.Position = 0;
return fs;
}

We are using this method, so as to return a stream in order to download the file in response to using the stream. The save as method of the Closed XML helps create the stream.

Downloaded file looks like below,

file

XML Using Xpath Program

XML and XPath Program: Xpath is called for finding the information within the document.

What is XPath?

XPath is a syntax to define the parts of XML document.
XPath uses the path expressions to navigate in XML documents.
XPath contains a library of the standard functions.
XPath is a major element in XSLT.
XPath is also used in XQuery, XPointer and XLink.
XPath is a W3C recommendation.

Softwares

XML copy Editor, XEditor.

Program

<book>
<title>XML</title>
<author>Erik T. Ray</author>
<year>2003</year>
<price>300</price>
</book>

Xpath

/book

output

XML

Erik T. Ray

2003

300

Xpath

/book/author

output

Erik T. Ray

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

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.

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.

< Student_Details>
< Student_ID>001
< /Student_ID>
< Contact_Number>088888888
< /Contact_Number>
< Student_Name>Mohan
< /Student_Name>
< Student_Location>Chennai
< /Student_Location>
< ExamType>
< ExamType>AnnualExam</ ExamType>
< Marks>
< Marks>1100</ Marks>
< Percentage>
< Percentage>86</ Percentage>
</ Percentage>
</ Marks>
</ ExamType>
</ Student_Details>
< Student_Details>
< Student_ID>002
< /Student_ID>
< Contact_Number>099999999
< /Contact_Number>
< Student_Name>Abilash
< /Student_Name>
< Student_Location>Bangalore
< /Student_Location>
< ExamType>
< ExamType>AnnualExam</ ExamType>
< Marks>
< Marks>1050</ Marks>
< Percentage>
< Percentage>80</ Percentage>
</ Percentage>
</ Marks>
</ ExamType>
</ 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.

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.

< Student>
< Student_ID>001< /Student_ID>
< Contact_Number>088888888< /Contact_Number>
< Student_Name>Mohan< /Student_Name>
< Student_Location>Chennai< /Student_Location>
< ExamType>AnnualExam</ ExamType>
< Marks>1100</ Marks>
Percentage>86</ Percentage>
</ Student>
< Student>
< Student_ID>002< /Student_ID>
< Contact_Number>099999999< /Contact_Number>
< Student_Name>Abilash< /Student_Name>
< Student_Location>Bangalore< /Student_Location>
< ExamType>AnnualExam</ ExamType>
Marks>1050</ Marks>
< Percentage>80</ Percentage>
</ 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.

XML Creation using LINQ to XML

Your comment on this post:

Related Articles