Help with C#, XML and SQL

[toekramp]

I need to parse an XML file with C# and then insert the data into MS SQL. There are dozens of tables and hundreds of elements to deal with. And of course the names of the elements in the XML do not correspond directly with the names in MS SQL. Any chance you guys could point me toward a tutorial on something similar? Tried googling it and couldn't find very much.

 

[Markbnj]

I'm not sure about a tutorial, but there are many possibilities. Personally, I would probably write an XSLT to transform the schema into something that matches my database schema, and then import it directly. That's probably the long-term easiest and most maintainable way to handle it. Check out SAXON, which is a command-line XSLT processor. The transform for what you want probably won't be too complex, and you can easily automate the whole thing from code.

 

[toekramp]

Originally posted by: Markbnj
I'm not sure about a tutorial, but there are many possibilities. Personally, I would probably write an XSLT to transform the schema into something that matches my database schema, and then import it directly. That's probably the long-term easiest and most maintainable way to handle it. Check out SAXON, which is a command-line XSLT processor. The transform for what you want probably won't be too complex, and you can easily automate the whole thing from code.

I'm looking at that as well as DataSet.ReadXML

although at this point I'm having trouble figuring out exactly how to map my tables/columns in my DS to SQL

 

[Markbnj]

That's why I like the XSLT approach. By the time you have a mapping that works for the database there will be a lot of transformed names. You could bury all that in code, but it seems much more maintainable to me if it is in a static XSLT and part of the automated process. If the database schema changes you edit the XSLT and you're done.

 

[imported_Dhaval00]

As an alternative, look at SqlBulkCopy - specifically, the ColumnMappings property. If you're lucky and have a straightforward mapping from your XML to the SQL table, you can do something like:

using (DataSet reportData = new DataSet())
{
reportData.ReadXml(Server.MapPath(?report.xml?));

using (SqlConnection connection = new SqlConnection(connectionString))
using (SqlBulkCopy sbc = new SqlBulkCopy(connection))
{
sbc.DestinationTableName = ?report_table?;

// if your DB col names don?t match your XML element names 100%
// then relate the source XML elements (1st param) with the destination DB cols
sbc.ColumnMappings.Add(?campaign?, ?campaign_id?);
sbc.ColumnMappings.Add(?cost?, ?cost_USD?);

connection.Open();

//table 0 is the main table in this dataset
sbc.WriteToServer(reportData.Tables[0]);

connection.Close();

//remove the xml file
} // dispose SqlConnection and SqlBulkCopy
} // dispose DataSet

 

[JasonCoder]

Originally posted by: Dhaval00
As an alternative, look at SqlBulkCopy - specifically, the ColumnMappings property. If you're lucky and have a straightforward mapping from your XML to the SQL table, you can do something like:

using (DataSet reportData = new DataSet())
{
reportData.ReadXml(Server.MapPath(?report.xml?));

using (SqlConnection connection = new SqlConnection(connectionString))
using (SqlBulkCopy sbc = new SqlBulkCopy(connection))
{
sbc.DestinationTableName = ?report_table?;

// if your DB col names don?t match your XML element names 100%
// then relate the source XML elements (1st param) with the destination DB cols
sbc.ColumnMappings.Add(?campaign?, ?campaign_id?);
sbc.ColumnMappings.Add(?cost?, ?cost_USD?);

connection.Open();

//table 0 is the main table in this dataset
sbc.WriteToServer(reportData.Tables[0]);

connection.Close();

//remove the xml file
} // dispose SqlConnection and SqlBulkCopy
} // dispose DataSet

this.

I thought there was something out there like that when I first read this post but I wasn't sure so I kept quiet lest I be exposed for the ignoramus I am.