SQL Clone
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
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1265 Visits: 369
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
Right there with Babe
Right there with Babe (786 reputation)Right there with Babe (786 reputation)Right there with Babe (786 reputation)Right there with Babe (786 reputation)Right there with Babe (786 reputation)Right there with Babe (786 reputation)Right there with Babe (786 reputation)Right there with Babe (786 reputation)

Group: General Forum Members
Points: 786 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
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1265 Visits: 369
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
Right there with Babe
Right there with Babe (786 reputation)Right there with Babe (786 reputation)Right there with Babe (786 reputation)Right there with Babe (786 reputation)Right there with Babe (786 reputation)Right there with Babe (786 reputation)Right there with Babe (786 reputation)Right there with Babe (786 reputation)

Group: General Forum Members
Points: 786 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
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1138 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
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12662 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
SSC Guru
SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)

Group: General Forum Members
Points: 50135 Visits: 10844

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
SSC Rookie
SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)

Group: General Forum Members
Points: 36 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
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2036 Visits: 1398
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
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 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