Read XML and import data to DataTable in ASP.Net

Last Reply one year ago By pandeyism

Posted one year ago

Dear All,

I had a very simple question below is the link:

https://www.aspforums.net/Threads/162380/XML-to-Database

Here's what I wanted in my DataTable:

ContentID  levelid levelguid moduleid parentid id guid Typeof fieldname id firstName lastName Username id dispalyname Name

The above words are rows which I have seperated by space:

Please let me know if it's possible:

Thanks,

Vikash

Posted one year ago

Hi Vikash21,

Please refer below sample

HTML

<div>
    <asp:Button ID="btnSubmit" runat="server" Text="Insert" OnClick="btnSubmit_Click" />
    <asp:GridView ID="gvXML" runat="server">
    </asp:GridView>
</div>

Namespace

C#

using System.Data;

Code

C#

protected void btnSubmit_Click(object sender, EventArgs e)
{
    string FilePath = Server.MapPath("~/XMLFile.xml");
    DataSet ds = new DataSet();
    ds.ReadXml(FilePath);
    DataTable dtRecords = ds.Tables["Record"];
    DataTable dtFields = ds.Tables["Field"];
    DataTable dtListValues = ds.Tables["ListValues"];
    DataTable dtListvalue = ds.Tables["ListValue"];
    DataTable dtUsers = ds.Tables["Users"];
    DataTable dtUser = ds.Tables["User"];

    var lists = (from record in dtRecords.AsEnumerable()
                    join field in dtFields.AsEnumerable() on record["Record_Id"] equals field["Record_Id"]
                    join listValues in dtListValues.AsEnumerable() on field["Field_Id"] equals listValues["Field_Id"]
                    join listValue in dtListvalue.AsEnumerable() on listValues["ListValues_Id"] equals listValue["ListValues_Id"]
                    select new
                    {
                        ID = field["id"],
                        Guid = field["guid"],
                        Type = field["type"],
                        ParentId = record["parentId"],
                        displayName = listValue["displayName"]
                    }).ToList();

    var userList = (from record in dtRecords.AsEnumerable()
                    join field in dtFields.AsEnumerable() on record["Record_Id"] equals field["Record_Id"]
                    join users in dtUsers.AsEnumerable() on field["Field_Id"] equals users["Field_Id"]
                    join user in dtUser.AsEnumerable() on users["Users_Id"] equals user["Users_Id"]
                    select new
                    {
                        ContentId = record["contentId"],
                        levelId = record["levelId"],
                        moduleId = record["moduleId"],
                        ID = field["id"],
                        Guid = field["guid"],
                        Type = field["type"],
                        ParentId = record["parentId"],
                        firstName = user["firstName"],
                        lastName = user["lastName"],
                        userName = user["User_Text"],
                        Users_Id = user["id"]
                    }).ToList();

    DataTable dtUserList = new DataTable();
    dtUserList.Columns.Add("Id", typeof(int));
    dtUserList.Columns.Add("levelguid", typeof(string));
    dtUserList.Columns.Add("Typeof", typeof(string));
    dtUserList.Columns.Add("ParentId", typeof(int));
    dtUserList.Columns.Add("displayName", typeof(string));
    dtUserList.Columns.Add("levelId", typeof(string));
    dtUserList.Columns.Add("moduleId", typeof(string));
    dtUserList.Columns.Add("firstName", typeof(string));
    dtUserList.Columns.Add("lastName", typeof(string));
    dtUserList.Columns.Add("contentId", typeof(string));
    dtUserList.Columns.Add("UserName", typeof(string));
    dtUserList.Columns.Add("Users_Id", typeof(string));
    foreach (var rows in lists)
    {
        dtUserList.Rows.Add(rows.ID, rows.Guid, rows.Type, rows.ParentId, rows.displayName);
    }
    foreach (var rows in userList)
    {
        dtUserList.Rows.Add(rows.ID, rows.Guid, rows.Type, rows.ParentId, "", rows.levelId, rows.moduleId, rows.firstName, rows.lastName, rows.ContentId, rows.userName, rows.Users_Id);
    }

    DataTable dt2 = new DataTable();
    for (int i = 0; i <= dtUserList.Rows.Count; i++)
    {
        dt2.Columns.Add();
    }
    for (int i = 0; i < dtUserList.Columns.Count; i++)
    {
        dt2.Rows.Add();
        dt2.Rows[i][0] = dtUserList.Columns[i].ColumnName;
    }
    for (int i = 0; i < dtUserList.Columns.Count; i++)
    {
        for (int j = 0; j < dtUserList.Rows.Count; j++)
        {
            dt2.Rows[i][j + 1] = dtUserList.Rows[j][i];
        }
    }
    gvXML.DataSource = dt2;
    gvXML.DataBind();
}

Screenshot