C# and SQL Server – Importing Data (XML)

In order to import data into an SQL Server database, it must be in a suitable format. XML or eXtensible Markup Language files are an alternative file format to CSV, that can sometimes be used for this purpose. XML is a metalanguage which allows users to define their own customised markup.

Below is some sample XML that contains person information, the same as that used in the previous example for importing data from CSV.

<?xml version="1.0" encoding="utf-8"?>
<people>
	<person>
		<firstname>Zoe</firstname>
		<lastname>Davis</lastname>
		<title>Miss</title>
		<dob>1979-07-11</dob>
	</person>
	<person>
		<firstname>Tom</firstname>
		<lastname>Ingram</lastname>
		<title>Mr</title>
		<dob>1971-10-04</dob>
	</person>
	<person>
		<firstname>Karen</firstname>
		<lastname>Thomas</lastname>
		<title>Mrs</title>
		<dob>1969-03-08</dob>
	</person>
	<person>
		<firstname>Samantha</firstname>
		<lastname>Yates</lastname>
		<title>Miss</title>
		<dob>1995-08-27</dob>
	</person>
</people>

The following example shows how C# can be used to import this data, from a file called 'personimport.xml', into a table called ‘person’, which was used in the examples for selecting, inserting, updating, deleting and importing data (CSV and text).

Firstly, a connection to the database is established, the XML file path is set and a check is made to see if it actually exists. If it does, it gets assigned to a reader object and the 'person' elements, along with their contents, are processed one by one. The individual data elements for a person are extracted and a check is made to see if they contain data, before being inserted into the database, using an SQL ‘Insert‘ statement. Finally, feedback is given as to the number of records added.

It should be noted that in order for this to work, there must be a ‘using‘ statement for the ‘System.Xml’ namespace.

// Database connection variable.
SqlConnection connect = new SqlConnection(
    "Server=MSSQLSERVERDEMO; Database=Demo;" +
    "User Id=DemoUN; Password=DemoPW");

try
{

    // Connect to database.
    connect.Open();

}
catch (Exception e)
{

    // Confirm unsuccessful connection and stop program execution.
    Console.WriteLine("Database connection unsuccessful.");
    System.Environment.Exit(1);

}

// File path.
string filePath = @"C:\demo\personimport.xml";

// Check if the XML file exists.
if (!File.Exists(filePath))
{

    // Message stating XML file could not be located.
    Console.WriteLine("Could not locate the XML file.");

    // Stop program execution.
    System.Environment.Exit(1);

}

// Variables for person details.
string personFName;
string personLName;
string personTitle;
string personDob;

// Record count.
int recordCount = 0;

// SQL variables.
string sqlPersonInfo;
SqlCommand sqlInsert;

try
{

    // Assign the XML file to a reader object.
    using var reader = XmlReader.Create(filePath);

    // Read the person elements.
    while (reader.ReadToFollowing("person"))
    {

        // Extract the first name.
        reader.ReadToFollowing("firstname");
        personFName = reader.ReadElementContentAsString();

        // Extract the last name.
        reader.ReadToFollowing("lastname");
        personLName = reader.ReadElementContentAsString();

        // Extract the title.
        reader.ReadToFollowing("title");
        personTitle = reader.ReadElementContentAsString();

        // Extract the date of birth.
        reader.ReadToFollowing("dob");
        personDob = reader.ReadElementContentAsString();

        // Check that there is data to insert.
        if (!String.IsNullOrEmpty(personFName) && 
            !String.IsNullOrEmpty(personLName) && 
            !String.IsNullOrEmpty(personTitle) && 
            !string.IsNullOrEmpty(personDob))
        {

            // Construct the insert statement.
            sqlPersonInfo = @"
                INSERT INTO person 
                    (firstname, lastname, title, dob)
                VALUES 
                    (@firstname, @lastname, @title, @dob)
            ";

            // Query text incorporated into SQL command.
            sqlInsert = new SqlCommand(sqlPersonInfo, connect);

            // Bind the parameters to the query.
            sqlInsert.Parameters.AddWithValue("@firstname", personFName);
            sqlInsert.Parameters.AddWithValue("@lastname", personLName);
            sqlInsert.Parameters.AddWithValue("@title", personTitle);
            sqlInsert.Parameters.AddWithValue("@dob", personDob);

            // Execute SQL.
            sqlInsert.ExecuteNonQuery();

            // Increment the record count.
            recordCount += 1;

        }

    }

    // Provide feedback on the number of records added.
    if (recordCount == 0)
    {

        Console.WriteLine("No new person records added.");

    }
    else if (recordCount == 1)
    {

        Console.WriteLine(recordCount + " person record added.");

    }
    else
    {

        Console.WriteLine(recordCount + " person records added.");

    }

}
catch (Exception e)
{

    // Confirm error adding person information and exit.
    Console.WriteLine("Error adding person information.");
    System.Environment.Exit(1);

}
finally
{

    // Close the database connection.
    connect.Close();

}

The contents of the ‘person’ table now looks as follows.

id firstname lastname title dob
1 Bob Smith Mr 1980-01-20
3 Fred Bloggs Mr 1975-05-07
4 Alan White Mr 1989-03-20
5 Fiona Bloggs Mrs 1985-05-19
6 Zoe Davis Miss 1979-07-11
7 Tom Ingram Mr 1971-10-04
8 Karen Thomas Mrs 1969-03-08
9 Samantha Yates Miss 1995-08-27

Further Resources