Insert XML Files into Databases Using Xml2OleDb
page 3 of 4
by Andrew Mooney
Feedback
Average Rating: 
Views (Total / Last 10 Days): 43383/ 49

Adding the XML Data to the Database

[Download Code]
Now that we know the database has a table that can hold the XML file we start inserting records. Again we create and open an OleDbConnection to the database and iterate through the rows in the DataTable from the XML file, creating a SQL insert command for each row of data. Do this by iterating through the DataTable columns to get the column names and values for the current row. Finally, use the OleDbCommand ExecuteNonQuery method to insert each row into the database.

// Iterate rows in the DataTable
foreach(DataRow dr in dataTableXml.Rows)
{
    // Create the sql insert command for each row
    string sqlCmd = "insert into [" + tableName + "] (";
    // Iterate the datatable columns
    for(int i = 0;i < dataTableXml.Columns.Count;i++)
    {
        // Add the column name
        sqlCmd = sqlCmd + dataTableXml.Columns[i].ColumnName.ToString() + ",";
    }
    sqlCmd  = sqlCmd.Substring(0,sqlCmd.Length - 1) + ") values (";
    // Iterate the DataTable columns
    for(int x = 0;x < dataTableXml.Columns.Count;x++)
    {
        // Add the column value for this row
        sqlCmd = sqlCmd + "'" + dr[x].ToString().Replace("'","''") + "',";
    }
    sqlCmd = sqlCmd.Substring(0,sqlCmd.Length - 1) + ");";
    // Create and execute the insert command
    OleDbCommand oledbCmd = new OleDbCommand(sqlCmd,oledbConn);
    oledbCmd.ExecuteNonQuery();
}


View Entire Article

User Comments

No comments posted yet.

Product Spotlight
Product Spotlight 





Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2021 ASPAlliance.com  |  Page Processed at 2021-03-02 11:42:46 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search