Load large xml files to database tables by reading tags quickly

Question

I'm using SQL server 2005 loading XML data.

currently I'm loading large XML files to tables via Sp's it took whole XML to a XML type sql variable and prepare document.there after i read tags i want and then insert in to table variable and finally after finishing all processing i insert all the processing records from table variable to actual table in the DB

I'm encountering in two problems while I'm loading XML's to the table

1. when file size exceeds about 1 GB it gives following error

Msg 7119, Level 16, State 1, Procedure sp_xml_preparedocument, Line 1 Attempting to grow LOB beyond maximum allowed size of 2,147,483,647 bytes. Msg 8179, Level 16, State 5, Procedure GETXMLDATA, Line 59 Could not find prepared statement with handle 0.

1. even file size can be handle it took so long time to process server memory size is 4GB it took 7 hours to insert 72000 records

any suggestions to fast loading?

Answer 1

I think your best option is to look at SQL Server CLR integration. This means that you write a stored procedure or function not using T-SQL but using C# or VB.NET.

In any of these two programming languages you have a lot more options for reading your xml file. You can use XmlTextReader for streaming access to your xml file. This means that you don't have to load the entire file into memory.