Introduction to XML2OL2OLDB

Introduction to XML2OL2OLDB

XML is the best way to share data in the Internet. The 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? XML2OL2OLDB will explain to the OLEDB database, such as SQL Server, Access, Excel, Visual FoxPro, FoxPro, and DBase, etc. Insert XML files, etc.

First, load the XML file into the DataSet and get the first table. This table is the DataTable we have to join the database; Next, remove the extension of the XML file, the file name removes the extension will be the table name used in our database . If the XML format is inaccurate, an error will be reported, and an example in the source code has an example of XML file format. See Authors.xml

// Load the XML file we submit to DataSetDataSetXml.ReadXML (httpcontext.current.server.mappath (textboxxml.text)); // Get the first table DataTableXML = DataSetXml.tables [0]; // Generate Table Name TableName = TextBoxxml.text.substring (0, TextBoxxml.text.length -4); once XML loading is successful, first check if there is data (ROWS), then check if the database exists, if you don't exist, create one The data is then inserted from XML into the database.

/ / Check if there is data (ROWS) if (DataTableXML.Rows.count> 0) Create a database table

Create a database connection to get the database schema information we have to add a table.

// create a database connection, open the database, the database table schema information obtained 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 IF in DataTable, SChem T.Rows.count <1) sqlcmd = "CREATE TABLE" TABLENAME "("; for (int i = 0; i

// Traverse the RowsForeach (DataRow Dr in DataTableXML.Rows) {string sqlcmd = "Insert Into [" TableName "] ("; // traversing the column for the DataTable (INT i = 0; i

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