sql server openrowset一步读取巨大的xml文件
这是我的第一篇文章......我真的需要这方面的帮助，所以任何对此主题有一定了解的人 - 请帮助！
我需要做的是将 xml 文件读入 sql server 数据表。我一遍又一遍地寻找这个问题的解决方案，实际上已经找到了一些。问题是正在加载的 xml 的大小。它的重量为 2GB（也有 10GB 的）。我已经设法做到了这一点，但我看到了一个特定的解决方案，在我看来这是一个很好的解决方案，但我无法弄清楚。
好吧，让我们进入正题。目前我这样做：
- 我使用 openrowset 将整个 XML 读取到变量中。 （这需要整个内存...）
- 接下来我使用 .node() 来提取数据并用它们填充表。
这是一个两步过程。我想知道我是否可以只一步完成它。我看到有诸如格式文件之类的东西，并且有很多关于如何使用它以基于记录的方式从平面文件甚至 Excel 文档中提取数据的示例（而不是将整个东西吸入变量中），但我找不到任何示例来展示如何将巨大的 XML 读入表中，动态解析数据（基于格式文件）。有可能吗？我真的很感激一些帮助，或者关于在哪里找到一个好的例子的指导。
请原谅我的英语——我已经有一段时间没有用这种语言写这么多了:-) 提前致谢！
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!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
对于非常大的文件，您可以使用 SSIS： 将 XML 数据加载到 SQL Server 2008
它使您可以灵活地转换 XML 数据，并减少非常大的文件的内存占用。当然，与在 BULK 模式下使用 OPENROWSET 相比，它可能会慢一些。
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.