Best method of importing XML data into SQL 2008

MrDavidThorn
MrDavidThorn used Ask the Experts™
on
Hi Folks, I have a large XML file (350mb) , I need to import the XML file into SQL. I believe my options are either bulk loading the file into a table and XML field or using OPENXML. The problem that I have with the bulk load is that I then need to use XQuery to return the desired results (I know very little about XQuery or XPath) and I want to import the file in the sameway an access datafile imports an XML file i.e creating tables and importing data for parents and child nodes. Im not sure what method to use and any advice would be appreciated
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
problem is that im using SQL Express, I think using Xquery Nodes is the best option that Iv found so far
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Top Expert 2012

Commented:
>>I believe my options are either bulk loading the file into a table and XML field or using OPENXML.
...
I think using Xquery Nodes is the best option that Iv found so far <<
You are talking about two different methods: OPENXML or XML Data Type Methods.

While OPENXML is fast with small files, you may find that with a file oif that size it may croak.  I have never tried to use OPENXML with an Xml document that big.

The other problem you have is that it sounds like you want to import this Xml document into more than one table and therefore more complex. So unless you have the expertise you may want to hire a consultant to help you on this project.

Author

Commented:
yes I understand that they are two differnet methods and was asking what method would be best for a file that size and complex, the bigger problem i have is that the XML file is not well formed  and while I was trying to use TSQL and xquery to resolve the problem so I could run a stored procedure, the best solution appears to be using the SQLXML in a vbscript of asp.net file.
Top Expert 2012

Commented:
>>was asking what method would be best for a file that size and complex<<
Right and I believe I answered that question.

>> the bigger problem i have is that the XML file is not well formed<<
Than you cannot use any SQL Server Xml method, be it OPENXML, Xml Data Type Method or SQLXML for that matter.  If the Xml file is not well-formed, than it is not an Xml document that SQL can consume as Xml.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial