In today’s post, we will explore how to convert XML nodes to a DataTable using C#. XML is a widely used format for data exchange between applications. At times, we may need to parse XML data into our business objects, such as a DataTable. In this tutorial, we will leverage the power of C# to accomplish this task efficiently.
Prerequisites
To follow along with this tutorial, you will need a basic understanding of C# and familiarity with XML structures and data manipulation.
Step 1:
Setting up the Environment To begin, create a new ASP.NET Web Forms page (XmlNodeListToDataTable.aspx) and its code-behind file (XmlNodeListToDataTable.aspx.cs). These files will serve as our working environment.
Step 2:
Defining the XML Structure In this example, we will work with an XML file named “Category.xml” that contains a list of categories. Here is a sample XML structure for reference:
<?xml version="1.0" encoding="utf-8"?>
<CategoryList>
<Category>
<MainCategory ID="1">XML</MainCategory>
<SubCategory>Basic</SubCategory>
<Description>List of XML articles.</Description>
<Active>Yes</Active>
</Category>
<Category>
<MainCategory ID="2">XML1</MainCategory>
<SubCategory>Basic1</SubCategory>
<Description>List of XML articles1.</Description>
<Active>Yes</Active>
</Category>
</CategoryList>
DataSet represents an in-memory cache of data.{alertSuccess}
<%@ Page Language="C#" AutoEventWireup="true"
CodeFile="XmlNodeListToDataTable.aspx.cs" Inherits="XmlNodeListToDataTable" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="GridView1" runat="server"></asp:GridView>
</div>
</form>
</body>
</html>
Step 3:
Loading and Parsing the XML In the Page_Load event of the code-behind file (XmlNodeListToDataTable.aspx.cs), we will load the XML file and retrieve the desired XML nodes. Here’s the code snippet to accomplish this:
public partial class XmlNodeListToDataTable: System.Web.UI.Page {
protected void Page_Load(object sender, EventArgs e) {
XmlDocument doc = new XmlDocument();
doc.Load(Server.MapPath("Category.xml"));
//Get and display all the book titles.
XmlElement root = doc.DocumentElement;
XmlNodeList elemList = root.GetElementsByTagName("Category");
GridView1.DataSource = ConvertXmlNodeListToDataTable(elemList);
GridView1.DataBind();
}
Step 4:
Converting XmlNodeList to DataTable To convert the XmlNodeList to a DataTable, we will implement the ConvertXmlNodeListToDataTable method. This method iterates over the XML nodes, creates the appropriate columns, and fills the DataTable with the node values. Here’s the implementation:
public static DataTable ConvertXmlNodeListToDataTable(XmlNodeList xnl) {
DataTable dt = new DataTable();
int TempColumn = 0;
foreach(XmlNode node in xnl.Item(0).ChildNodes) {
TempColumn++;
DataColumn dc = new DataColumn(node.Name, System.Type.GetType("System.String"));
if (dt.Columns.Contains(node.Name)) {
dt.Columns.Add(dc.ColumnName = dc.ColumnName + TempColumn.ToString());
} else {
dt.Columns.Add(dc);
}
}
int ColumnsCount = dt.Columns.Count;
for (int i = 0; i < xnl.Count; i++) {
DataRow dr = dt.NewRow();
for (int j = 0; j < ColumnsCount; j++) {
dr[j] = xnl.Item(i).ChildNodes[j].InnerText;
}
dt.Rows.Add(dr);
}
return dt;
}
}