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





4.00/5 (5 votes)
I recently posted an article on how to place the data in an XML into an SQL table with a bulk Insert; this is an alternative way to do it by breaking up the XML before placing it into the 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
- 19th November, 2015: Initial version