sql server openrowset to read huge xml files in one step

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 - 开发者_StackOverflow中文版it's been a while since I had to write so much in that language :-) Thanks in advance!

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.