Inserting an XML file into a database with Xml2oledb

Source: Internet
Author: User
Tags add format empty sql string table name tostring access
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=;




Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.