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 form
// Get the first table in the DataSet
dataTableXml = dataSetXml.Tables;
// 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)