Import large XML file into SQL Server CE

Question

I try to import data from a XML file into SQL Server CE database. I use ErikEJ SQL Server Compact Bulk Insert Library (from NuGet) this library on codeplex. I create database and table. Then I read XML to DataTable and import this DataTable to DB table.

DataSet ds = new DataSet();
ds.ReadXml("myxml.xml");
DataTable table = new DataTable();
table = ds.Tables[0];
String connString = @"Data Source = test.sdf";
SqlCeBulkCopy bulkInsert = new SqlCeBulkCopy(connString);
bulkInsert.DestinationTableName = "testtable";
bulkInsert.WriteToServer(table);

It works on a small xml, but when I use large xml (more then 1gb) I get this error on ReadXml :

"System.OutOfMemoryException" in mscorlib.dll

How to fix this?

update: I know that this error because I use large xml - question is how optimize this algorithm, mayby using buffer or read xml part by part, any idea?

possible duplicate of ['System.OutOfMemoryException' was thrown when there is still plenty of memory free](http://stackoverflow.com/questions/1153702/system-outofmemoryexception-was-thrown-when-there-is-still-plenty-of-memory-fr) — Chuck Savage, Apr 03 '15 at 06:48

score 1 · Accepted Answer · edited May 23 '17 at 11:43

1

There is no simple libary that will solve this for you.

You need to read the XML file in a streaming fashion ( Reading Xml with XmlReader in C# ) to avoid loading the entire XML file, and then for each element read add these to a List or DataTable, up to say 100,000 entries, then BulkInsert those, dispose/clear all unused objects and go on, until the entire file has been read.

In addition, calls to SqlCeBulkCopy should be wrapped in usings to dispose unmanaged resources:

using (SqlCeBulkCopy bulkInsert = new SqlCeBulkCopy(connString))
{
   bulkInsert.DestinationTableName = "testtable";
   bulkInsert.WriteToServer(table);
}

edited May 23 '17 at 11:43

Community

1
1

answered Apr 03 '15 at 07:26

ErikEJ

40,951
5
75
115

Thk, but if I use `DataTable.ReadXml()` how to limit for examle 100000 entries? And then begin from 100001? – e1s Apr 03 '15 at 07:54
Dont use DataTable.ReadXml, but use a xmlreader and add 1 row per read to a DataTable or List, and count how many you have added. When you reach 100000, then run blukcopy, and continue... – ErikEJ Apr 03 '15 at 08:23

Import large XML file into SQL Server CE

1 Answers1