C# App to Break Up an XML File and Place the Contents into an SQL Table

Introduction

This tip follows on from the last article, where I placed an XML file and its content into an SQL Table via a bulk insert, this time I break up the XML and place it element by element.

Background

This tip came out of a comment to my previous tip, so I have taken the Gauntlet and placed my XML into the table piece by piece, as opposed to 'en masse'. From all my articles, you might follow a theme of Sports Teams, but that is how my practice rolls since learning to use a database.

Using the Code

Firstly, I will need a table for my XML to populate, and below is the code to create a table. Below is the SQL to create the table and a glimpse on what my XML looks like.

Table

CREATE TABLE [dbo].[Team]
(
    SquadNum INT NOT NULL PRIMARY KEY,
    LName Varchar(24) not null,
    FName varchar(24) not null,
    Position varchar(8) not null,
    Team varchar(24) not null
)

XML

<Players>
    <squadnum>13</squadnum>
    <LName>Forster</LName>
    <Fname>Fraser</Fname>
    <position>G</position>
    <team>Southampton</team>
  </Players>
  <Players>
    <squadnum>12</squadnum>
    <LName>Clyne</LName>
    <Fname>Nathaniel</Fname>
    <position>D</position>
    <team>Southampton</team>
  </Players>

The Form and Player Class

The form is again a button, and a RichTextbox. The button is the main event, and puts everything in motion. As well as a form, there is one Class (Players) which is used for storing and utilizing the broken down XML Data. The Class was chosen so the program could make a List of this Class to use in a Stored Procedure or passing the data into a Service. For the sake of simplicity, I have avoided that in this example and have just placed the Data into a local DB.

Below, I make the Structure for which the XML is extracted. The program uses the XmlDocument as to use the following methods in the code. The first part is to bring the XML into the program, then I open the data to be extracted by creating a XmlNodelist which will extract all the Nodes that constitute each Element.

 private void Transform_Click(object sender, EventArgs e)
  {
            //Instatiating the XmlDocument Class so the method below can be used below to 
            //loop through and extract the pertinent data at each node.
            XmlDocument XML = new XmlDocument(); 

            // Loading the XMLDocument to extract Data
            XML.Load(@"..\Players1.XML");

            // Finding the Root Element and making the NodeList
            XmlElement root = XML.DocumentElement;
            XmlNodeList nodes = root.GetElementsByTagName("Players");

            // Making the list ofr future use
            List<Players> TheTeam = new List<Players>();

          //more code to follow
}

Looping through the XML

In the loop, we are looking to take what is inside the Players element, and pull out the Squad Number, the Players first and last name as well as their current Club, Position and their Squad Number. Whilst the program is looping through the elements, I am using the Player Class to store the Inner Text of each Node within the Element. After the Nodes within the element have been looped through and the Class has its Data.

foreach (XmlElement node in nodes)
{   
   Players SquadMember = new Players();
   SquadMember.squadnum = Int32.Parse(node.SelectSingleNode("squadnum").InnerText);
   SquadMember.LName =  (node.SelectSingleNode("LName").InnerText );
   SquadMember.FName = (node.SelectSingleNode("Fname").InnerText );
   SquadMember.Position = (node.SelectSingleNode("position").InnerText);
   SquadMember.team = (node.SelectSingleNode("team").InnerText);   

//SQL to follow

SQL Insert

The Loop produces an instance of the Player Class with data relating to each Player in the Squad. I am now in a position to store this within my database. To do this, the program uses an SQL connection, SQL Command and the Data for each instance of the Player class. The Player class data is placed within the parameters of the Parameter.AddWithValue method. The Player class data and the tables Column Name of the table are the constituents for these parameters. After the SQL is complete, the instance of the Player Class is added to the List of Players named the Team. The SQL is put on a Try Catch in case of an error. After the SQL, I run a test loop to append certain contents of the List into the RichTextBox.

try
{    
using (SqlConnectionrichTextBox1.AppendText(TheTeam[i].FName + " " + 
TheTeam[i].LName + "\n"); connex = new SqlConnection
(@"Data Source=(LocalDB)\v11.0;Integrated Security=True;
AttachDbFilename=c:\Users\colem2\Documents\Visual Studio 2013\
Projects\DeserializationXML\DeserializationXML\L'Angleterre.mdf"))
 {
 string sql = "insert into Team 
 (SquadNum,LName,FName,Position,Team) values(@sqn, @ln, @fn, @pos, @tm)";
      
      SqlCommand cmd = new SqlCommand(sql, connex);
      connex.Open();
      cmd.Parameters.AddWithValue("@sqn", SquadMember.squadnum);
      cmd.Parameters.AddWithValue("@ln", SquadMember.LName);
      cmd.Parameters.AddWithValue("@fn", SquadMember.FName);
      cmd.Parameters.AddWithValue("@pos", SquadMember.Position);
      cmd.Parameters.AddWithValue("@tm", SquadMember.team);
      cmd.ExecuteNonQuery();
  }
}
catch (SqlException ex)
      {  
          MessageBox.Show(ex.ToString(), "SQL Error");
      }

TheTeam.Add(SquadMember);

}//End of For each Loop

// Checking the List has been filled

for (int i = 0; i <= (TheTeam.Count - 1); i++)

    {
        richTextBox1.AppendText(TheTeam[i].FName + " " + TheTeam[i].LName + "\n");
    }

Points of Interest

What I got form this was learning the difference between the XDocument and the XMLDocument, as well as learning a simple way in which to take some XML and make its contents available in a database by breaking out the elements.

History

• 19^th November, 2015: Initial version