Importing data from XML file to SQL database

Question

Is it possible to import data from XML file to a SQL database, and if yes, how would that be done. I have a XML file that contains about 50 000 entries and I have to make an application that would manipulate that data (mostly reading and comparing) - so my concern is that the manipulation with that amount of data (and there is a very likely possibility that in the future there will be even more) would be very slow and inefficient. If there is some other option that you think would be better, please advise. Thanks

Answer 1

You can use SQL Server Import and Export Wizard. You can also look in SQL Server Integration Services. If you want to use C# then SQL server does support XML data type. You can make use of that.

You can also try to read the data in data set and then use BulkInsert to insert data in SQL Server

DataSet reportData = new DataSet();
reportData.ReadXml(Server.MapPath("yourfile.xml"));
SqlConnection connection = new SqlConnection("DB ConnectionSTring");
SqlBulkCopy sbc = new SqlBulkCopy(connection);
sbc.DestinationTableName = "yourXMLTable";

EDIT: For SQL Server 2005 check SQL Server 2005 Import / Export Wizard