Read XML Files and Insert into Database Table

Introduction

The code snippet allows to insert the huge XML file along with its entire details into the SQL table.

Background

The code snippet can be used in operation with XML when you need to find various details like node levels and XML node data, whether XML node has any children, etc.

• Finding the parent of each node
• Assigning the levels to each node
• Getting value of each XML node
• Highlighting whether the XML node has its child node

Using the Code

The code takes the XML file as the input and returns the output in the specified database table.

Please find below the assemblies used in conjunction with the code.

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Runtime.InteropServices;
using System.Web;
using System.Web.Services;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Xml;
using System.Xml.Linq;

The following code consists of all the declaration related to the level of the node and the node list values.

public partial class XMLReader : System.Web.UI.Page
{
    DataTable dtTable = null;
    List<KeyValuePair<string, string>> 
    kvpList = new List<KeyValuePair<string, string>>();
    List<KeyValuePair<string, int>> 
    kvpLevelList = new List<KeyValuePair<string, int>>();
    List<KeyValuePair<string, bool>> 
    kvpLevelList1 = new List<KeyValuePair<string, bool>>();
    List<KeyValuePair<string, int>> 
    kvpLevelListforparent = new List<KeyValuePair<string, int>>();
    static XmlNodeList ParentnodeList = null;
    static int Level = 1;
    static int parentid = 0;
    static string nodeparent = String.Empty;
    protected void Page_Load(object sender, EventArgs e)
    {
        XmlDocument xmlDoc = new XmlDocument();
        xmlDoc.Load(@"xmlpath");
        if (xmlDoc.FirstChild.NodeType == XmlNodeType.XmlDeclaration)
            //removes the xml document declaration xml doctype
            xmlDoc.RemoveChild(xmlDoc.FirstChild);
            if (xmlDoc.FirstChild.Name == "some root")
            {
                // adds the root element at the start
                XmlElement elem = xmlDoc.CreateElement("ROOT");
                xmlDoc.FirstChild.ReplaceChild(elem, xmlDoc.FirstChild);
            }
        foreach (XmlNode item in xmlDoc.ChildNodes)
        {
            XmlNodeList nodeList;
            XmlNodeList childnode;
            nodeList = item.ChildNodes;
            childnode = nodeList[0].ChildNodes;
            for (int i = 0; i <= childnode.Count - 1; i++)
            {
                //Please specify the node or the node element needed to iterate through the method 
                myTest(childnode[i]);
            }
        }
    }
}

The below code part is used to iterate through the node present inside the root node, you can also use the if condition in order to iterate any of the specific values.

for (int i = 0; i <= childnode.Count - 1; i++)
{
    //Please specify the node or the node element needed to iterate through the method
    myTest(childnode[i]);
}

In the below part of code, you will find the code which provides the level of the xmlnode, node value and whether the node has any child elements inside it, the value will be stored inside the database table.

public void myTest(XmlNode inXmlNode)
{
    XmlNode xNode;
    XmlNodeList nodeList;
    bool HasChildren = false;
    string Node = "";
    string Value = "";
 
    if (inXmlNode.HasChildNodes)
    {
        if (inXmlNode.Name.ToString().Replace("ITRForm:", "") == "ScheduleMATC")
        {
            ParentnodeList = inXmlNode.ChildNodes;
        }
        nodeList = inXmlNode.ChildNodes;
        for (int i = 0; i <= nodeList.Count - 1; i++)
        {
            //code for setting level 
            if (nodeList[i].Name != "#text")
            {
                bool flag = false;
                for (int j = 0; j <= ParentnodeList.Count - 1; j++)
                {
                    if (ParentnodeList[j].Name == nodeList[i].Name) 
                    //IF THERE IS MATCH IN THE PARENT NODE LIST AND THE CURRENT NODE THEN THE
                    //LEVEL WILL BE ZERO AND FLAG WILL BE TRUE
                    {
                        Level = 1;
                        flag = true;
                        parentid = 0;
                        //HasChildren = true;
                    }
                }
                if (flag == false) 
                // IF CURRENT NODE IS NOT A PARENT NODE THEN THE LEVEL WILL BE CHANGED
                // BASED ON THE FOLLOWING CODITION AND FLAG WILL BE FALSE
                {
                    if (nodeList[i].PreviousSibling == null && nodeList[i].ParentNode != null) 
                    // IF PREVIIOUS SIBLING IS NULL AND IT HAS PARENT NODE MEANS FIRST CHILD 
                    // HENCE THE LEVEL SHOULD BE CHANGED 
                    {
                        Level = Level + 1;
                    }
                    else
                    {
                        foreach (var item in kvpLevelList.Where
				(kvp => kvp.Key == nodeList[i].PreviousSibling.Name))
                        // THIS LINE INDICATES IF THE NODE IS IN THE SAME LEVEL THEN THE SAME LEVEL 
                        // NO WILL BE ASSIGNED
                        {
                        if (item.Value != null)
                        {
                            int val = item.Value;
                            Level = item.Value;
                        }
                    }
                    foreach (var item in kvpLevelListforparent.Where(
                        kvp => kvp.Key == nodeList[i].PreviousSibling.Name)) 
                    // THIS LINE INDICATES IF THE NODE IS IN THE SAME LEVEL
                    // THEN THE SAME LEVEL NO WILL BE ASSIGNED
                    {
                        if (item.Value != null)
                        {
                            int val = item.Value;
                            parentid = item.Value;
                        }
                    }
                }
            }
            if (nodeList[i].ChildNodes.Count > 1)
            {
                HasChildren = true;
            }
            else
            {
                if (nodeList[i + 1] != null)
                {
                    if (nodeList[i + 1].InnerText == "#text")
                    {
                        HasChildren = false;
                    }
                }
                HasChildren = false;
            }
 
            if (HasChildren)
            {
                Node = nodeList[i].Name.ToString;
                // THIS STATEMENT IS BECAUSE IT PROVIDES 
                // IRREGULAR VALUE OF ALL THE CONTAINING NODES MEANS 
                // IT CONTAINS ALL THE CONTENT NODES VALUE.
                Value = String.Empty;    
            }
            else
            {
                Node = nodeList[i].Name.ToString;
                Value = nodeList[i].InnerText;
            }

            kvpList.Add(new KeyValuePair<string, string>(Node, Value));
            // lIST CONTAINING NODE AND VALUE PAIR

            kvpLevelList.Add(new KeyValuePair<string, int>(nodeList[i].Name, Level)); 
            //LIST CONTAINING NODE AND LEVEL PAIR

            kvpLevelList1.Add(new KeyValuePair<string, bool>(nodeList[i].Name, HasChildren));
            //LIST CONTAINING NODE AND CHILDREN PAIR

            kvpLevelListforparent.Add(new KeyValuePair<string, int>(nodeList[i].Name, parentid));
            //LIST CONTAINING NODE AND PARENT PAIR

            parentid = InsertNode(Node,Value,Convert.ToString(HasChildren),
			Convert.ToString(Level),parentid);
        }
        //
        string value = nodeList[i].Name;
        xNode = nodeList[i];
        myTest(xNode);
 
    }
}
else
{
    return;
}

In the above code snippet, you can find the code highlighted below which creates the list of key value pair which holds the Level List for parent and Level list for child node, and then the insert statement is fired which returns the id and that can again be used as parent id if the parent node has child present within. Please refer below.

kvpList.Add(new KeyValuePair<string, string>(Node, Value)); 
// lIST CONTAINING NODE AND VALUE PAIR

kvpLevelList.Add(new KeyValuePair<string, int>(nodeList[i].Name, Level)); 
//LIST CONTAINING NODE AND LEVEL PAIR

kvpLevelList1.Add(new KeyValuePair<string, bool>(nodeList[i].Name, HasChildren));
//LIST CONTAINING NODE AND CHILDREN PAIR

kvpLevelListforparent.Add(new KeyValuePair<string, int>(nodeList[i].Name, parentid));
//LIST CONTAINING NODE AND PARENT PAIR

parentid = InsertNode(Node,Value,Convert.ToString(HasChildren),Convert.ToString(Level),parentid);

Points of Interest

The interesting learning in this code is that I am able to find the child level, parent node level and its values collaboratively using this code. It would be helpful in finding the XML node levels and values and inserting it into the database. It's easy to understand recursive function which can read XML nodes up to nth level.

Developer can modify the code according to their requirements and use the levels for further customization of their logic. The code provides the levels and the parent of each node.

Happy coding!! Thanks for reading!! Please vote if you like the tip !