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
Best method of importing XML data into SQL 2008
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
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Have you tried this:
http://msdn.microsoft.com/en-us/library/ms171993.aspx
or for 2005:
http://msdn.microsoft.com/en-us/library/ms171993(SQL.90).aspx
http://msdn.microsoft.com/en-us/library/ms171993.aspx
or for 2005:
http://msdn.microsoft.com/en-us/library/ms171993(SQL.90).aspx
problem is that im using SQL Express, I think using Xquery Nodes is the best option that Iv found so far
You might be able to download SQLXML here:
http://www.microsoft.com/downloads/en/details.aspx?FamilyId=228DE03F-3B5A-428A-923F-58A033D316E1&displaylang=en
http://www.microsoft.com/downloads/en/details.aspx?FamilyId=228DE03F-3B5A-428A-923F-58A033D316E1&displaylang=en
>>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.
...
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.
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.
>>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.
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.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial