Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Import of huge XML file


Import of huge XML file

Author
Message
Edvard Korsbæk
Edvard Korsbæk
SSC-Enthusiastic
SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)

Group: General Forum Members
Points: 154 Visits: 298
Hi All!

I have an xml file of 44 Gb (Not Meg, its really GB)
Delivered by the Danish custom authorities.

My problem is simple - How to import such a beast?

I have seen a limit of 2.1 Gb everywhere

Best regards

Edvard Korsbæk
SQL_NuB
SQL_NuB
SSC-Enthusiastic
SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)

Group: General Forum Members
Points: 184 Visits: 364
Is there a way to break it up? If not maybe doing the import out of SQL, such as using a .NET app or something to do the import.


that's a HUGE XML file. The largest XML file I ever imported was 8 gb, and I created an .NET app do to the importing into the SQL database
Edvard Korsbæk
Edvard Korsbæk
SSC-Enthusiastic
SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)

Group: General Forum Members
Points: 154 Visits: 298
No, i can't even open it in any text editor i have come across.
Its big!
I really do not think that anybody is using it for anything - But its the only solution Danish Custom gives me.

Can you tell me something about that .net app you made?

Best regards

Edvard Korsbæk
SQL_NuB
SQL_NuB
SSC-Enthusiastic
SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)

Group: General Forum Members
Points: 184 Visits: 364
since you can't open it, I would kick it back to the sender requesting them to separate the data some how so it can be more manageable to work with.

As for the .NET app, I made it read the XML file and parse out the data based on the xml tags and inserted into the tables. it was something like SqlBulkCopy to do the process.
Bhaskar.Shetty
Bhaskar.Shetty
Right there with Babe
Right there with Babe (778 reputation)Right there with Babe (778 reputation)Right there with Babe (778 reputation)Right there with Babe (778 reputation)Right there with Babe (778 reputation)Right there with Babe (778 reputation)Right there with Babe (778 reputation)Right there with Babe (778 reputation)

Group: General Forum Members
Points: 778 Visits: 509
Use .NET App for parsing this xml file into a DataSet hen you can use this Dataset for saving in SQL
Eugene Elutin
Eugene Elutin
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3042 Visits: 5478
1. SSIS - use XMLSource
2. SQLXMLBulkLoad:
http://msdn.microsoft.com/en-us/library/ms171993.aspx
http://msdn.microsoft.com/en-us/library/ms171806.aspx

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Ed Wagner
Ed Wagner
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10322 Visits: 9600

I don't have experience with SSIS, but I've seen bulk load do some amazing things. The throughput is simply amazing. It takes some time to set up, but it's worth it in the end, especially if you're going to have to use the same source file layout again.


Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
brianchristopherbrown
brianchristopherbrown
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 40
If you can identify an XML tag immediately under the root that is repeated for each major section of the text data, then you can bulk import the data with that tag as a delimiter, clean up the dangling header and footer tags, and then you'll have more manageable chunks of the overall text. If those chunks are too big to be manageable, then repeat. And so on.
Ed Zann
Ed Zann
Mr or Mrs. 500
Mr or Mrs. 500 (507 reputation)Mr or Mrs. 500 (507 reputation)Mr or Mrs. 500 (507 reputation)Mr or Mrs. 500 (507 reputation)Mr or Mrs. 500 (507 reputation)Mr or Mrs. 500 (507 reputation)Mr or Mrs. 500 (507 reputation)Mr or Mrs. 500 (507 reputation)

Group: General Forum Members
Points: 507 Visits: 1391
edvard 19773 (5/23/2013)
No, i can't even open it in any text editor i have come across.
Its big!
I really do not think that anybody is using it for anything - But its the only solution Danish Custom gives me.


I would probably start with a VBscript or .Net script to read in the first 1000 or so lines (assuming it has line breaks) then write those out to a text file to get some sense of the schema and data contained in the file. Then go from there to determine how the file might be split up and processed.



XML Guy
XML Guy
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 3
There are xml split tools available. Some GUI, some command-line. Some free, some commercial. Just Google "xml splitter tool"
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search