Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Import of huge XML file Expand / Collapse
Author
Message
Posted Wednesday, May 22, 2013 1:01 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 5:21 AM
Points: 100, Visits: 175
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
Post #1455324
Posted Thursday, May 23, 2013 6:20 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 23, 2013 11:21 AM
Points: 176, Visits: 346
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
Post #1455970
Posted Thursday, May 23, 2013 6:27 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 5:21 AM
Points: 100, Visits: 175
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
Post #1455971
Posted Thursday, May 23, 2013 6:33 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 23, 2013 11:21 AM
Points: 176, Visits: 346
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.
Post #1455974
Posted Thursday, May 23, 2013 6:39 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, October 16, 2014 9:28 AM
Points: 512, Visits: 433
Use .NET App for parsing this xml file into a DataSet hen you can use this Dataset for saving in SQL
Post #1455977
Posted Thursday, May 23, 2013 7:44 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 8:55 AM
Points: 2,873, Visits: 5,185
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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1456012
Posted Thursday, May 23, 2013 1:21 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 12:05 PM
Points: 4,240, Visits: 3,674

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
Post #1456195
Posted Thursday, May 23, 2013 2:12 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 3, 2014 8:43 AM
Points: 10, Visits: 28
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.
Post #1456212
Posted Friday, May 24, 2013 7:37 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, September 26, 2014 7:16 AM
Points: 487, Visits: 1,231
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.



Post #1456501
Posted Wednesday, June 5, 2013 12:12 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 5, 2013 12:17 PM
Points: 1, Visits: 3
There are xml split tools available. Some GUI, some command-line. Some free, some commercial. Just Google "xml splitter tool"
Post #1460367
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse