Insert XML Files into Databases Using Xml2OleDb

Introduction to Xml2OleDb

[Download Code] Have you ever wanted to add your XML data files into a database? Xml2OleDb will demonstrate how easy ASP.NET makes it to add XML data to any OleDb database including SQL Server, Access, Excel, Visual FoxPro, FoxPro, and dBase. XML is definitely the best way to share data via the World Wide Web. XML data can easily be integrated into web applications for many uses. However, at some point you (or someone you work for) will want XML data added to a database. Hopefully this will be an easy task for you by the time you finish reading this article and looking at the code sample. Now let’s get started. The first step is to load the XML file into a DataSet and get the first table. This table is the DataTable we will be adding to the database. Next remove the extension from the XML file. The file name minus the extension will be used as the table name in the database. An error message will be displayed if the XML file is not in the proper format. Look at the Authors.xml file include in the download to see the proper format. // Load the XML file into a DataSet using the // file name submitted from the web formdataSetXml.ReadXml(HttpContext.Current.Server.MapPath(textBoxXml.Text));// Get the first table in the DataSetdataTableXml = dataSetXml.Tables[0];// Remove the extension from the XML file. The file name minus// the extension will be used as the table name in the database.tableName = textBoxXml.Text.Substring(0,textBoxXml.Text.Length -4); Once the XML file has been loaded, check to see if the DataTable contains any data (rows). Next call two routines: the first checks to see if the table exists in the database and creates the table if it does not exist, and the second inserts the data from the XML file into the database. Finally, if there were no errors, display a message stating that the XML file was successfully inserted into the database. // Check to see if table one contains any data (rows)if(dataTableXml.Rows.Count > 0) Creating the Database Table This next routine checks to see if the table exists in the database and creates the table if it does not exist. Start by creating an OleDb database connection using the connection string provided when the web form is submitted. Open the database connection and retrieve database schema information for only the table we are looking for and place it in a DataTable. In this example the table name is the name of the XML file without the extension. // Create an OleDb database connection using the connection string// provided when the web form is submittedOleDbConnection oledbConn = new OleDbConnection(textBoxOleDb.Text);// Open the database connectionoledbConn.Open();// Retrieve database schema information for only the table we are looking for// and place it in a DataTable.// In this example the table name is the name of the XML file without the extensionDataTable schemaTable =          oledbConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,    new object[] {null, null, tableName, "TABLE"}); Using the DataTable that contains the database schema check to see if the table exists. If the table exists in the schema there will be one row in the DataTable. If the table does not exist in the schema, the DataTable row count will be zero. // Check to see if the table exists in the database schema// If the table exists in the schema there will be 1 row in the DataTable// If the table does not exist in the schema the DataTable row count will be zeroif(schemaTable.Rows.Count < 1) If the table does not exist in the database, create it. Make the create table SQL command by iterating through the XML file's columns. This way the database columns will have the same name as the XML file. In this example we create all columns as CHAR (text/string) data type with a length of 100. This simplifies the code, because not all data types are supported by all OleDb databases. Once the SQL command is created, it is executed against the database using the ExecuteNonQuery method of the OleDbCommand. // Make the create table SQL command by iterating through the XML file's// columns. This way the database columns will have the same name as the XML file.sqlCmd = "create table " + tableName + " (";for(int i = 0;i < dataTableXml.Columns.Count;i++){    // This adds each column as a text/string type with a length of 100    sqlCmd = sqlCmd + dataTableXml.Columns[i].ColumnName.ToString() + "         char(100),";}// Remove the last ","sqlCmd  = sqlCmd .Substring(0,sqlCmd.Length - 1) + ");";// Create and execute the create table commandOleDbCommand oledbCmd = new OleDbCommand(sqlCmd,oledbConn);oledbCmd.ExecuteNonQuery();

Adding the XML Data to the Database

[Download Code] Now that we know the database has a table that can hold the XML file we start inserting records. Again we create and open an OleDbConnection to the database and iterate through the rows in the DataTable from the XML file, creating a SQL insert command for each row of data. Do this by iterating through the DataTable columns to get the column names and values for the current row. Finally, use the OleDbCommand ExecuteNonQuery method to insert each row into the database. // Iterate rows in the DataTableforeach(DataRow dr in dataTableXml.Rows){    // Create the sql insert command for each row    string sqlCmd = "insert into [" + tableName + "] (";    // Iterate the datatable columns    for(int i = 0;i < dataTableXml.Columns.Count;i++)    {        // Add the column name        sqlCmd = sqlCmd + dataTableXml.Columns[i].ColumnName.ToString() + ",";    }    sqlCmd  = sqlCmd.Substring(0,sqlCmd.Length - 1) + ") values (";    // Iterate the DataTable columns    for(int x = 0;x < dataTableXml.Columns.Count;x++)    {        // Add the column value for this row        sqlCmd = sqlCmd + "'" + dr[x].ToString().Replace("'","''") + "',";    }    sqlCmd = sqlCmd.Substring(0,sqlCmd.Length - 1) + ");";    // Create and execute the insert command    OleDbCommand oledbCmd = new OleDbCommand(sqlCmd,oledbConn);    oledbCmd.ExecuteNonQuery();}

Code Sample Instructions and Conculsion
Now let’s discuss using the sample code. Place all of the files from the download into a virtual directory. You do not need to compile the source, because I have included the assembly (.dll) file in the download. This sample creates tables in databases, but it does not create the database. You will need to create the database before adding XML data to it. I have included an empty Access database (Database.mdb) and an empty Excel database/workbook (Database.xls) for you to use. You need to give the ASP.NET process account (default is MACHINENAME/ASPNET) write permissions on the database file. For FoxPro/dBase this could be just an empty directory.

Navigate to the virtual directory you created. Enter the name of the XML file (must be in the same virtual directory). Don’t enter the path, just the file name (Authors.xml). I have included a sample XML file (Authors.xml) that you can use. Next, enter the OleDb connection string and click the submit button.

Example connection strings:
Access: Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:/data/database.mdb;
Excel: Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:/data/database.xls;Extended Properties=Excel 8.0;
FoxPro/dBase: Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:/data;Extended Properties=dBASE IV;
SQL Server: Provider=SQLOLEDB; Data Source=localhost; Initial Catalog=database;User ID=sa;Password=;

When I started thinking about writing this article I imagined that code would be very complicated. I was very pleased to find out how few lines of code it took. That’s how easy ASP.NET makes it to add XML data into the OleDb database of your choice.