Convert xml string to DataTable or DataSet in C#

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;

  }

}

Post a Comment

Please do not post any spam link in the comment box😊

Previous Post Next Post

Blog ads

CodeGuru