Loading XML Files
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)


Loading XML Files
===

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)

