I need a little direction on a project. An import is going from PIPE delimited to XML. The Daily files are averaging 4GB. I need to know the fastest way to import this large XML file. Not fast from setup; I have time to do this right, I mean fastest for the actually import.
It seems like XML Bulk Load should be one of the fastest:
My issue is that the file is much more complex. Some records may have 1 detail node, some 2000. The data is Invoices (header info) and the Invoice details (1 to many rows).
The second option is OpenXML
This option seems like a close fit, but I don't have any experience or benchmarks for it. The problem is that XML will be more flexible and better in the long run, but I need the speed I get from Bulk Load mode and BCP.
Third option ? Suggestions. Maybe Third party software.
I have tried SpectralCore's Full Convert Enterprise. It could not handle the file.
Hardware: I cannot get much better hardware. HP DL380G8. 128GB Ram, Netapp Fiber Storage. SQL Server 2012 Enterprise.