How to import XML data into a table

Question

How to import XML data into a table

johnnycash

Ten Centuries

Points: 1172
More actions
February 23, 2010 at 4:49 pm

#138300

Hello everyone!
I need some assistance and guidance. I have an XML file that I need to bring it into a SQL 2005 table. The problem is that it is so darn huge that VS2008 cannot open it, nor any other XML viewers/editors out that I have found on the internet; and therefore I do not know the structure. Is there something within SQL 2005 that can help me get all this data into a table?
Once its there, I can do what I need to do in order to properly organize it into our DB.
As usual, any help is extremely highly appreciated.
Regards,

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95461 More actions · Answer 1

johnnycash (2/23/2010)
Hello everyone!
I need some assistance and guidance. I have an XML file that I need to bring it into a SQL 2005 table. The problem is that it is so darn huge that VS2008 cannot open it, nor any other XML viewers/editors out that I have found on the internet; and therefore I do not know the structure. Is there something within SQL 2005 that can help me get all this data into a table?
Once its there, I can do what I need to do in order to properly organize it into our DB.
As usual, any help is extremely highly appreciated.
Regards,

Have you tried using OPENROWSET(BULK N'YourFileName', SINGLE_BLOB) to load the file into a temp table?

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

johnnycash Ten Centuries Points: 1172 More actions · Answer 2

Hi Wayne!

I was reading about it in BOL but I got confused cause it says that one needs to create the receiving table with the corresponding structure. I do not know the structure of the XML file.

Will your code work?

LutzM SSC Guru Points: 107049 More actions · Answer 3

The max size is for data stored with xml data type is 2GB. I don't know if your file would exceed those limit...

Regarding extracting node names and values I would recommend to have a look at the following link:

http://www.sqlservercentral.com/Forums/FindPost513350.aspx

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]