4

It's my first post ever... and I really need help on this one so any one who has some knowlege on the subject - please help!

What I need to do is to read an xml file into sql server data tables. I was looking over and over for solutions to this one and have found a few actualy. The problem is the size of the xml which is being loaded. It weights 2GB (and there will 10GB ones). I have managed to do this but I saw one particular solution which seems to me to be a great one but I cannot figure it out.

Ok lets get to the point. Currently I do it this way:

  • I read an entire XML using the openrowset into a variable. (this takes the whole ram memory...)
  • next I use the .node() thing to pull out the data and fill the tables with them.

Thats a two-step process. I was wondering if I could do it in only one step. I saw that there are things like format files and there are numerus examples on how to use that to pull out data from flat files or even excel documents in a record-based maner (in stead of sucking the whole thing into a variable) but I CANNOT find any example which would show how to read that huge XML into a table parsing the data on the fly (based on the format file). Is it even possible? I would really appreciate some help, or guidence on where to find a good example.

Pardon my English - it's been a while since I had to write so much in that language :-) Thanks in advance!

AirPoland
  • 53
  • 3
  • I would be also interested if I can read the xml in such a way that each element would take one row in the destination table. ie. aaabbbb would be inserted into a table in two rows one would contain aaa and the other one would hold bbb??? – AirPoland Jan 19 '11 at 00:41

1 Answers1

5

For very large files, you could use SSIS: Loading XML data into SQL Server 2008

It gives you the flexibility of transforming the XML data, as well as reducing your memory footprint for very large files. Of course, it might be slower compared to using OPENROWSET in BULK mode.

Jens Mühlenhoff
  • 14,565
  • 6
  • 56
  • 113
Mitch Wheat
  • 295,962
  • 43
  • 465
  • 541
  • hmm... I will take a look at that tomorrow - its really late here now. Thank you very much for a quick response. – AirPoland Jan 19 '11 at 00:46
  • 1
    This approach will not be good. The process looks like this: 1) outer system asks over a web service to send the data in 2) if allowed it sends a zip with the big xml. Once done such request is marked with a flag. 3) A windows service periodicaly checks for requests to be processed. It picks it up and runs an sp which sucks the data in from the xml into buffer tables and then does all sorts of stuff with it. Now the problem is that the file they send will get really big pretty soon - measured in GB. Is there a way to define a formatfile for the openrowset toread in the data to proper columns? – AirPoland Jan 19 '11 at 16:35
  • [Link Fixed](http://www.sql-server-performance.com/2008/loading-xml-data-into-sql-server-2008/) But that isn't a good article to demonstrate SSIS XML Source. Here is a [better one](http://blog.hoegaerden.be/2011/04/07/loading-xml-using-ssis/). Anyway, I don't think using SSIS is a good idea. Here is one [example](http://www.sqlservercentral.com/Forums/Topic1239597-364-1.aspx) from the trenches that should serve as a warning. And [another](http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/6fd67b69-444e-4d29-b325-8e09659861ed/) – Amit Naidu Jun 10 '13 at 21:17