How to insert content of large xml file into tables in sql server 2005?

Question

Hi all,

I need to insert the content of an xml file of size 350 Mb into tables in sql server 2005.

How to insert?

I have tried to use bulk insert but got out of memory exception.

Please help.

Answer 1

Your best bet is to write an application to read in the XML and then do the inserts. It may be a little slower, but that way you have full control over how the data in inserted and can easily make the minor transformations to the data that are almost inevitable in these kinds of operations.

Your other option is to look at DTS or SSIS. Not sure how well they deal with XML though.

Answer 2

Take a look at this example http://msdn.microsoft.com/en-us/library/ms191184(SQL.90).aspx

You can try using OPENROWSET

INSERT INTO T(XmlCol)
SELECT * FROM OPENROWSET(
   BULK 'c:\SampleFolder\SampleData3.txt',
   SINGLE_BLOB) AS x

Answer 3

SSIS has specific tools designed for this. That said, I think SSIS is a trap, and I would opt to write my own application to handle the job.

But I know plenty of people who have no problems doing this sort of thing regularly through SSIS, so that's where I would recommend starting if you aren't up to home-brewing a solution.