Importing large / complex XML
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:
http://support.microsoft.com/kb/316005
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. http://msdn.microsoft.com/en-us/library/ms186918.aspx
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.
It seems like XML Bulk Load should be one of the fastest:
http://support.microsoft.com/kb/316005
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. http://msdn.microsoft.com/en-us/library/ms186918.aspx
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.
oleggold
http://social.msdn.microsoft.com/Forums/pl/sqlxml/thread/f504a15d-b498-4da3-a64a-7d6298b84355
ASKER
Oleggold - I tried the import. I am getting this error:
Msg 6365, Level 16, State 1, Line 5
An XML operation resulted an XML data type exceeding 2GB in size. Operation aborted.
Is there a 2GB limit on XML Files?
Msg 6365, Level 16, State 1, Line 5
An XML operation resulted an XML data type exceeding 2GB in size. Operation aborted.
Is there a 2GB limit on XML Files?
ASKER
Does anyone know the best way to Import 4-6GB XML Files?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.