在SQL Server中导入和解析大型XML文件(“正常"方法相当慢时) [英] Importing and parsing a large XML file in SQL Server (when "normal" methods are rather slow)

问题描述

我有一个很大的XML文件，需要将其导入并解析为SQL Server中的表格结构(扁平化"). 大"是指大约450 MB的文件，最多包含6-7个嵌套级别和大约300个元素.

I have a large XML file that I need to import and parse into tabular structure ("flatten") in SQL Server. By "large" I mean a file that is around 450 MB, contains up to 6-7 nested levels and lots of elements, ~300.

我尝试使用 OPENXML 和 Xml.Nodes 解析文件.两种方法都很慢.读取父元素及其嵌套孙子元素的部分查询需要运行几分钟(如果不是几十分钟的话).

I tried parsing the file both with OPENXML and Xml.Nodes. Both of the methods are slow. A partial query which reads a parent element and it's nested grandchildren takes several minutes if not dozens to run.

我尝试使用SQLXML批量加载方法.不幸的是我不能-因为文件的结构不正确.在逻辑上有一个元素是父元素，实际上并没有嵌套为父元素.

I tried using the SQLXML Bulk Load method. Unfortunately I couldn't - because the file isn't structured properly. There is an element which is logically a parent element which isn't nested as a parent physically.

您认为剩下的唯一解决方案是使用.NET还是Java?有什么我想念的吗?

Do you think the only posiblle solution left is to use .NET or Java? Is there something I'm missing?

在某种程度上，我强烈希望采用动态解决方案.我不希望SQL Server开发人员继续使用他们没有控制/知识的程序化，编译后的代码-如果发生某些更改(在XML结构中).

I would strongly prefer a dynamic solution, to some degree. I don't want the SQL Server developers to relay on a procedural, compiled, code that they have no control/knowledge about - in the event that some changes will occur (in the XML structure).

非常感谢您.

推荐答案

确定.我在XML数据列上创建了XML索引. (目前仅是主要的).

OK. I created an XML Index on the XML data column. (Just a primary one for now).

一个查询花费了大约4:30分钟，现在花费了大约9秒！似乎一个存储XML并带有适当XML索引并使用xml.nodes()函数解析数据的表是可行的解决方案.

A query that took ~4:30 minutes before takes ~9 seconds now! Seems that a table that stores the XML with a proper XML Index and the parsing the data with the xml.nodes() function are a feasible solution.

谢谢大家.

这篇关于在SQL Server中导入和解析大型XML文件(“正常"方法相当慢时)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！