CODEWITHSUNDEEP
c#.netxmldatabaseetl
Ronnie Overby
Ronnie Overby

Reputation: 46470

Processing Large XML file into SQL Server with C#

First, what I describe here is a small part of a larger ETL process that is already in place. So, please no suggestions to port to SSIS or some other environment because I can't.

In this ETL process, for each table in the SQL server database that is being inserted into, I am:

  1. loading all of the relevant xml into an XElement object
  2. then transforming the xml into a typed dataset datatable
  3. then using a SqlBulkCopy object to quickly insert the data into the sql server table.

But, for one table, when I create the XElement, I get an OutOfMemory exception.

So, I now need to iteratively process the data in chunks, but I'm not sure of the best way to do this. The xml file is stored on the same machine that is running the ETL process.

Thanks for any help.

UPDATE

I'm getting started reading about the XmlReader class, which I've never used. If someone thinks this is the answer, please say so and provide any guidance that you will.

Upvotes: 0

Views: 1742

Answers (2)

nayato
nayato

Reputation: 83

In addition to plain use of XmlReader it could be useful to know about method XNode.ReadFrom. It works particularly well if XML is more like a very long list of entities as opposed to deep-nested hierarchy.

Upvotes: 0

TomTom
TomTom

Reputation: 62101

Don't use XmlElement - use the .NET SAX based parser to parse the XML stream. NEVER materialize the objects in memory. Simple. There is an API for that.

Basically, use an XmlTextReader.

Upvotes: 1

Related Questions