1

I have large XML file (2 Gb) and I need import data to sql ce db. XML file have a root tag and many same tags with 20 attributes (like a 2d table):

<Objects>
<Object ID="" name="" level="" />
<Object ID="" name="" level="" />

For load data from XML to sql ce db I use SqlCeBulkCopy (on codeplex). I try use DataTable, but have this error (becouse XML is more then 2 Gb):

"System.OutOfMemoryException"

I use XmlReader to read information from XML file:

XmlReader r = XmlReader.Create("file:////" + PathToFile);
  while (r.Read())
    {
        if ((r.Name == "Object") && (r.HasAttributes))
        {

        }
    }

And how use IDataReader with XmlReader for SqlCeBulkCopy in this situation (any example)?

e1s
  • 335
  • 4
  • 22

1 Answers1

1

xmlReader and DbDataReader are not related. But SqlCeBulkcopy supports both List and DataTable objects as well as DbDataReader

Use a List, and only load say 10000 rows/records, then bulk copy those and continue. You cannot fit a 2 GB XML file into memory!

So outside your while loop add (pseudo code):

var list = new List<MyObjects>();

And inside your while loop add:

var myObject = new MyObject();
myObject.Property1 = value from xmlreader;
myObject.Property2 = anotherValue from xmlreader;

list.Add(myObject);

if (list.Count == 10000)
{
   //run BulkCopy
   list.Clear();
}
ErikEJ
  • 40,951
  • 5
  • 75
  • 115
  • I about `IDataREader` for example `public class CustomObjectDataReader : IDataReader` and in this use `XmlReader` – e1s Apr 10 '15 at 12:46
  • Why make it so difficult for yourself? – ErikEJ Apr 10 '15 at 12:52
  • @ErikEJ, hi there. It is 2019. Do you still think that your implementation is better than implementing the `IDataReader`? I am just faced with the same issue and can not decide what is better - to implement the `IDataReader` or to follow your answer. – hellouworld Feb 17 '20 at 15:46
  • What do you think my answer to your question would be? – ErikEJ Feb 17 '20 at 16:18
  • @ErikEJ, I thought that your answer posted above is still relevant. So, I used it in my current implementation. Is it the case here, is your answer still relevant? :) – hellouworld Feb 17 '20 at 16:25