2

I'm currently working with loading a lot (thousands of files from 1KB - 6MB) XML files, and loading them into destination databases. Currently, I'm using the SQLXMLBULKLOAD COM object. One of the biggest problems I'm having is that the COM object doesn't always play nice within our transactional environment. There's other problems too, such as performance; the process really begins choking on files approaching ~2MB, taking several minutes, if not longer in some cases (hours), to load into the tables.

So now I'm looking for an alternative, of which there seems to be two flavors:

1) Something like OPENXML, where XML is inserted as XML data into SQL Server

or

2) Solutions that parse the XML in memory, and load as rowsets into the database.

There's drawbacks to either approach, and I know I'm going to have to start doing some benchmarking of prototype solutions before I jump to any conclusions. The OPENXML approach is very attractive IMO, mainly because it promises some really good performance numbers (others claiming going from hours to milliseconds). But it has the drawback of storing data as XML -not ideal in my particular scenario since the destination tables already exist, and many other processes rely on queries and SPROCS out there that query these tables as normal rowset data.

Whatever solution I choose, I must meet the following requirements:

1) Must accept any XML file. Clients (in a business sense) need only provide an XSD, and an appropriate destination database/table(s) for the data.

2) Individual files (never larger than ~6MB) must be processed in under 1 minute (ideally even much quicker than that).

3) Inserted data must be able to accomodate existing queries and SPROCS (i.e, must ultimately end up as normal rowset data)

So my question is, do you have any experience in this situation, and what are your thoughts and insights?

I am not completely opposed to an OPENXML-like solution, just as long as the data can end up as normal rowset data at some point. I am also interested in 3rd party solutions you may have experience with, this is an important part of our process, and we are willing to spend some $ if it provides us the best and most stable solution.

I'm also not opposed to "roll-your-own" suggestions, or things on Codeplex, etc. I came across the LINQ to XSD project, but couldn't find much documentation about what its capabilitities are (just as an exame of things I am interested in)

Didaxis
  • 8,486
  • 7
  • 52
  • 89
  • Is there any reason you are not using SSIS as this is what it is designed for? – Lloyd Feb 18 '12 at 14:39
  • I'm not opposed to SSIS at all, and that is an alternative we are looking at. I'm not much of an SSIS person, could you tell me what the advantages might be? – Didaxis Feb 18 '12 at 14:51
  • 1
    I prefer SSIS because if the packages or other parameters change there are no config files or recompiling projects simply ammend or update the SSIS Package in the BMO IDE. Heres a couple of SO links I followed up previously... http://stackoverflow.com/questions/7203492/importing-and-validating-xml-file-using-ssis-or-just-plain-t-sql http://stackoverflow.com/questions/6956409/ssis-reading-multiple-xml-files-from-folder/6957825#6957825 – Lloyd Feb 18 '12 at 14:58
  • Thanks for your useful replies. I appreciate your insights. – Didaxis Feb 19 '12 at 03:10

1 Answers1

0

I would revisit the performance issues you are having with the SQLXMLBULKLOAD COM. I have used this component to load 500MB xml files before. Can you post the code you are using to invoke the component?

tom redfern
  • 30,562
  • 14
  • 91
  • 126
  • Can you say roughly how long it took to load that 500MB xml file? Most `SqlXMLBulkload` postings I have seen complain about performance (on the order of several minutes per MB?). So it would be great to get an opposing data point. Not dropping constraints (or not using a staging table) can be one issue I am guessing. – Amit Naidu Jun 07 '13 at 23:10
  • Sorry Amit it was a long time ago. I don't recall having to wait very long though. – tom redfern Jun 10 '13 at 06:57