Importing ~1Gb XML into MSAccess
I've tried different approaches for importing a ~1Gb XML file into MSAccess unsuccessfully.
The approaches I've taken are:
1. Using the default ImportXml method. Problem: Since the XML file has multiple levels of nesting, data are imported into different tables without a common ID, so I can't relate the records between tables.
2. Importing first into Excel. Problem: Never finish loading.
3. Applying a transformation using the MSXML library. Problem: After 15 hours I stopped the process. Never finished loading.
4. Applying the same transformation using Saxon-B. Crashes while creating the tree.
5. Using SAX inside MSAccess. RAM consumption very low (~38Mb). Problem: The database bloated very fast up to 1.9 Gb in ~10min. After compact and repair the size returned to 24Mb. 5% of the records effectively imported up to that point.
It seems that no matter what approach I take, I'm going to have to split the xml file into several pieces and process it in chunks.
My next step will be using the reader and the writer of the MSXML library for splitting the file.
However, I'm kind of exhausted and would prefer not to think too much anymore for doing this.
Can somebody point me to a free tool, method, or language that can handle the partition effectively with the simplicity of couple clicks or couple lines of code?
Thanks in advance.