Inserting an XML file into a database with Xml2oledb

xml| Insert | data | Database XML2OLEDB Introduction
XML is the best way to share data on the Internet, and XML-formatted data can be easily integrated into different Web applications. But what if you want to insert the XML file into the database? Xml2oledb will show you how easy it is to insert XML files into an OLE DB database, such as SQL Server, Access, Excel, Visual FoxPro, FoxPro, and dBASE.

First, load the XML file into the dataset and get the first table, which is the DataTable we want to add to the database; Next, remove the extension of the XML file, and the name of the file to remove the extension is the name of the table used in our database. If the XML format is inaccurate, an error is reported and an example of an XML file format is available in the source code. See Authors.xml

Loading the XML file we submitted to the dataset
Datasetxml.readxml (HttpContext.Current.Server.MapPath (Textboxxml.text));
Get the first table in the dataset
Datatablexml = Datasetxml.tables[0];
Generate table Name
TableName = textBoxXml.Text.Substring (0,textboxxml.text.length-4);

Once the XML is loaded successfully, first check that the data table has data (rows), then check the database for tables, create one if it does not exist, and then insert the data from XML into the database.

Check for data presence (rows)
if (DataTableXml.Rows.Count > 0)

Create a database table
Create a database connection, and get the database schema information we want to add the table to.

Create a database connection, open a database, and get schema information for a database table
OleDbConnection oledbconn = new OleDbConnection (Textboxoledb.text);
Oledbconn.open ();
DataTable schematable =
Oledbconn.getoledbschematable (OleDbSchemaGuid.Tables,
New object[] {null, NULL, tablename, "TABLE"});

Check if the table exists, if there is a record in the DataTable
if (SchemaTable.Rows.Count < 1)


SQLCMD = "CREATE TABLE" + tablename + "(";
for (int i = 0;i < datatablexml.columns.count;i++)
{
Add column text/string type length 100
sqlcmd = sqlcmd + datatablexml.columns[i]. Columnname.tostring () + "char (100),";
}
sqlcmd = sqlcmd. Substring (0,sqlcmd.length-1) + ");";
OleDbCommand oledbcmd = new OleDbCommand (sqlcmd,oledbconn);
Oledbcmd.executenonquery ();

Adding XML data to a database

Traverse rows in a DataTable
foreach (DataRow Dr in Datatablexml.rows)
{
String sqlCmd = "INSERT into [+ tablename +"] (";
Columns traversing a DataTable
for (int i = 0;i < datatablexml.columns.count;i++)
{
Add Column Name
sqlcmd = sqlcmd + datatablexml.columns[i]. Columnname.tostring () + ",";
}
SQLCMD = sqlcmd.substring (0,sqlcmd.length-1) + ") VALUES (";
Traversing the DataTable columns
for (int x = 0;x < datatablexml.columns.count;x++)
{
Add column value to row
sqlcmd = sqlcmd + "'" + dr[x]. ToString (). Replace ("'", "" ") +" ', ";
}
SQLCMD = sqlcmd.substring (0,sqlcmd.length-1) + ");";
OleDbCommand oledbcmd = new OleDbCommand (sqlcmd,oledbconn);
Oledbcmd.executenonquery ();
}

Issues to be noted:
When testing, first download the source code, create an empty database, set modifiable permissions Database.mdb,database.xls, create an empty directory for Foxpro/dbase

Connection String Example:
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=;