Introduction to xml2oledb

Introduction to xml2oledb

XML is the best way to share data on the Internet. Data in XML format can be easily integrated into different web applications. But what if you want to insert an XML file into the database? Xml2oledb describes how easy it is to insert XML files into oledb databases, such as SQL Server, access, Excel, Visual FoxPro, Foxpro, and DBASE.

First, load the XML file into dataset and obtain the first table. This table is the datatable to be added to the database. Next, remove the extension of the XML file, remove the file name. The extension name is the table name used in our database. If the XML format is incorrect, an error is reported. The Source Code contains an example of the XML file format. See authors. xml

// Load the submitted XML file to Dataset
Datasetxml. readxml (httpcontext. Current. server. mappath (textboxxml. Text ));
// Obtain the first table in dataset.
Datatablexml = datasetxml. Tables [0];
// Generate the table name
Tablename = textboxxml. Text. substring (0, textboxxml. Text. Length-4 );
Once the XML file is loaded successfully, first check whether the data table has data (rows), then check whether the database has a table, create one if it does not exist, and then insert the data from XML to the database.

// Check whether data exists (rows)
If (datatablexml. Rows. Count> 0)
Create a database table

Create a database connection and obtain the database architecture information of the table to be added.

// Create a database connection, open the database, and obtain the schema information of the database table.
Oledbconnection oledbconn = new oledbconnection (textboxoledb. Text );
Oledbconn. open ();
Datatable schematable =
Oledbconn. getoledbschematable (oledbschemaguid. Tables,
New object [] {null, null, tablename, "table "});

// Check whether the table exists. If it exists, there will be a record in the datatable.
If (schematable. Rows. Count <1)


Sqlcmd = "create table" + tablename + "(";
For (INT I = 0; I <datatablexml. Columns. Count; I ++)
{
// Add the text/string type column with a length of 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 ();
Add XML data to the database

// Traverse the rows in the datatable
Foreach (datarow DR in datatablexml. Rows)
{
String sqlcmd = "insert into [" + tablename + "] (";
// Traverse the datatable Column
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 (";
// Traverse 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 ();
}
Notes:

Download the source code before testing, create an empty data database, set the permission to modify database.mdb,database.xls, and create an empty directory for Foxpro/DBASE

Example of a connection string:

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 =;