Avatar of MrDavidThorn
MrDavidThorn

asked on 

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
Microsoft SQL Server 2008Microsoft SQL Server 2005

Avatar of undefined
Last Comment
Anthony Perkins
Avatar of Nico Bontenbal
Nico Bontenbal
Flag of Netherlands image

Avatar of MrDavidThorn
MrDavidThorn

ASKER

problem is that im using SQL Express, I think using Xquery Nodes is the best option that Iv found so far
ASKER CERTIFIED SOLUTION
Avatar of Nico Bontenbal
Nico Bontenbal
Flag of Netherlands image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
>>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.
Avatar of MrDavidThorn
MrDavidThorn

ASKER

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.
Microsoft SQL Server 2005
Microsoft SQL Server 2005

Microsoft SQL Server 2005 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. It includes support for managing XML data and allows a database server to be exposed over web services using Tabular Data Stream (TDS) packets encapsulated within SOAP (protocol) requests.

72K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo