2018 年 10 月 30 日更新。为了以防万一有人感兴趣，我已经开始在这篇文章的末尾记录我为解决这个问题所做的实际工作，只是因为我发现了一些有趣的事情。
免责声明！我对此很陌生。我对 SQL Server、VBA、XPATH、XSD、XML 的自学接触相当有限，并且接受了一年的逻辑 (Java) 正式培训。大约一年前，我的工作使我进入了一个独特的位置，开始接触 SQL 和 XML，所以我仍然是一个新手。我发布/询问的内容可能非常天真，如果是这样，我道歉并欢迎坦率、严厉、批评、关于功能和形式适当性的建议以及良好的硬拷贝参考（我不是指 Microsoft 文档，因为我很少能理解他们的头或尾）。所以开始吧！
我正在处理的项目中遇到了这个错误，虽然这里和其他地方有一些关于它的帖子，但似乎没有任何帮助。我的猜测是我遇到了 XPATH 表达式问题……最初。
XQuery [XMLTestTable.DATA.value()]：无法隐式原子化或将“fn:data()”应用于复杂内容元素，在推断类型“元素”中找到类型“xs:anyType”({urn:MyFile-schema}:SUBUNITPRICE ,xs:anyType) *'.
正如您可能猜到的那样，这是一个 XML 导入/转换到关系表项目。我在网上发现了很多关于这类事情的信息，但对其中大部分含义的解释却很少（尽管我对其中的大部分内容都有一些有根据的猜测）。
我将从这里开始。模式：
有时人们会先导入一个模式，有时他们不会，只是顺其自然。我理解模式的方式是：1：根据某种标准验证 xml 文档，以确保一切顺利运行/导入/导出 2：可能提高导入/导出/编辑文件的效率。虽然我发现验证文档本身会增加导入时间（我认为这只是额外的步骤），但下游查询可能更有效，尽管我还没有走那么远。无论哪种方式，我认为理所当然并实践它是一个好主意。所以这是我的模式（这是手写的，所以如果你看到不好的地方，请对我大喊大叫！）。另外，我使用了一个名为 SUBUNITPRICE 的节点，请理解这实际上不是货币单位，我 我们更改了一些节点名称以使事情更加机密。只知道这个节点值是一个文本值，可以包含数字和符号。
<?xml version="1.0" encoding="UTF-8"?>
<xs:schema xmlns:xs= "http://www.w3.org/2001/XMLSchema" targetNamespace="urn:MyFile-schema" xmlns="urn:MyFile-schema" elementFormDefault="qualified">
<xs:element name="MyFile">
<xs:complexType>
<xs:sequence>
<xs:element name="FIELD1" type="xs:double"/>
<xs:element name="FIELD2" type="xs:string"/>
<xs:element name="FIELD3" type="xs:string"/>
<xs:element name="FIELD4" type="xs:string"/>
<xs:element name="FIELD5" type="xs:string"/>
<xs:element name="FIELD6" type="xs:dateTime"/>
<xs:element name="GROUP" maxOccurs="unbounded">
<xs:complexType>
<xs:sequence>
<xs:element name="GROUPID" type="xs:string"/>
<xs:element name="GROUPCATEGORY" type="xs:string"/>
<xs:element name="UNIT" maxOccurs="unbounded">
<xs:complexType>
<xs:sequence>
<xs:element name=" UNITNAME" type="xs:string"/>
<xs:element name="REVIEWER" type="xs:string"/>
<xs:element name="DATEANDTIME" type="xs:dateTime"/>
<xs:element name="SUBUNIT" maxOccurs="unbounded">
<xs:complexType>
<xs:sequence>
<xs:element name=" SUBUNITPRICE" type="xs:string"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
<xs:attribute name="MULTIENTRY" type="xs:string"/>
<xs:attribute name="PARTIALUNIT" type="xs:string"/>
<xs:attribute name="KIT" type="xs:string"/>
</xs:complexType>
</xs:element>
</xs:sequence>
<xs:attribute name="FULL" type="xs:string"/>
<xs:attribute name="VER" type="xs:string"/>
</xs:complexType>
</xs:element>
</xs:sequence>
<xs:attribute name="MyFile" type="xs:string"/>
</xs:complexType>
</xs:element>
</xs:schema>
我的 XML。注释位于 GROUP、UNITNAME 和 SUBUNIT 节点之后，用于引用节点长度、结构和潜在的 XML 文件大小（每个大约 500-600 MB 的文件可能有 100k GROUP 节点）。省略号 (...) 只是标记该特定节点有一个迭代，我希望您能在节点值中找到隐含的迭代次数。在任何给定时间，这些文件中的几个可能必须导入到 SQL Server 数据库中。
<?xml version="1.0" encoding="utf-8"?>
<MyFile xmlns="urn:MyFile-schema">
<FIELD1>FileName</ FIELD1>
<FIELD2>Foo</ FIELD2>
<FIELD3>Bar</ FIELD3>
<FIELD4>Upload</ FIELD4>
<FIELD5>UserName</ FIELD5>
<FIELD6>UploadTime</ FIELD6>
<GROUP VER="Yes" FULL="false"> ‘<---- There can be up to 100K of these (maybe more soon) ---identified by child node GROUPID
<GROUPID>GrName1</ GROUPID>
<GROUPCATEGORY>MyCategory</ GROUPCATEGORY>
<UNIT KIT=”1” PARTIALUNIT ="false" MULTIENTRY=”Yes”> ‘<---- 12 to 35 possible in each GROUP NODE ---identified by child UNITNAME
<UNITNAME>Unit1</ UNITNAME>
<REVIEWER>UserName</ REVIEWER>
<DATEANDTIME>DateTime</ DATEANDTIME>
<SUBUNIT> ‘<---- 2 in each UNIT NODE most of time – will be same tag SUBUNIT
<SUBUNITPRICE>11.50</ SUBUNITPRICE>
</ SUBUNIT>
< SUBUNIT> ‘<-sometimes 3 in intermittent unit nodes but rarely included in file
< SUBUNITPRICE >20.00</ SUBUNITPRICE>
</ SUBUNIT>
</ UNIT>
...
<UNIT KIT=”1” PARTIALUNIT ="false" MULTIENTRY=”Yes”>
<UNITNAME>Unit23</ UNITNAME>
<REVIEWER>UserName</ REVIEWER>
<DATEANDTIME>DateTime</ DATEANDTIME>
<SUBUNIT>
<SUBUNITPRICE>$11.50</ SUBUNITPRICE>
</ SUBUNIT>
< SUBUNIT>
<SUBUNITPRICE>$20.00</ SUBUNITPRICE>
</ SUBUNIT>
</ UNIT>
</GROUP>
<GROUP VER="Yes" FULL="false">
<GROUPID>GroupName100,000</ GROUPID>
...
</GROUP>
</MyFile>
这就是我一直在做的事情。
架构导入：
--Import the schema
IF EXISTS(SELECT * FROM sys.xml_schema_collections where [name] = 'XMLSchema')
DROP XML SCHEMA COLLECTION[XMLSchema]
DECLARE @MySchema XML
SET @MySchema =
(SELECT * FROM OPENROWSET
(BULK 'C:\Path\Schema.xsd', SINGLE_BLOB) AS xmlData
)
CREATE XML SCHEMA COLLECTION[XMLSchema] AS @MySchema
加载文件。一个 50k 的 GROUP 节点文件大约需要 2 分钟，我不知道 100k 需要多长时间。我想加快速度。我向 xml 列添加了一个索引。我不太确定这部分，除了我把它作为一个建议，它可以加快下游查询的速度，而且我知道它运行。我知道我可以通过编辑掉不必要的属性（所有属性）和字段来节省一些时间和资源，但我发现它只是在前端完成删除它们的工作。
我知道如果我将辅助索引添加到 XML 列，它确实可以加快速度。我不知道从哪里开始。如果有人对参考或快速添加有一些建议，我将不胜感激。
CREATE TABLE XMLTestTable
(
ID INT IDENTITY PRIMARY KEY,
DATA xml(CONTENT MyXmlSchema)
)
INSERT INTO XMLTestTable
(DATA)
SELECT CONVERT(XML, BulkColumn) as BulkColumn
--import an xml file into the column
FROM OPENROWSET(BULK 'C:\Path\FileName.XML', SINGLE_BLOB) as x
CREATE PRIMARY XML INDEX PXML_DATA
ON XMLTestTable (DATA)
下一步：我需要将每个 GROUPID 的 GROUPID 和 SUBUNITPRICE 放入一个表中，我将其称为 GROUPTABLE，希望它看起来像这样：
|ID |GROUPID|UNIT1_SUBUNITPRICE_1|UNIT1_SUBUNITPRICE_2|……|UNIT23_SUBUNITPRICE_2|
|1 |GrName1|11.50 |20.00 |……|25.00 |
|2 |GrName2|1.00 |32.41 |……|45.51 |
所以我创建了表：
CREATE TABLE GROUPTABLE
(
ID int IDENTITY(1,1) PRIMARY KEY,
GROUPID varchar(20),
UNIT1_SUBUNITPRICE_1 varchar(7),
UNIT1_SUBUNITPRICE_2 varchar(7),
…
UNIT23_SUBUNITPRICE_1 varchar(7),
UNIT23_SUBUNITPRICE_2 varchar(7)
)
现在是产生错误的部分！出于演示目的，假设我只将 ID 和 GROUPID 字段添加到此处的表中。如果我一开始只是尝试插入索引和 groupid，效果会很好！像这样：
–-migrate the data from the xmlcolumn to the table
WITH XMLNAMESPACES(DEFAULT 'urn:MyFile-schema')
INSERT INTO GROUPTABLE
Select
t.b.value('GROUPID[1]', 'varchar(20)') AS GROUPID
FROM XMLTestTable
CROSS APPLY
DATA.nodes('//MyFile/GROUP) AS t(b)
...然后是问题。这里假设我只将 ID 和 UNIT1_SUBUNITPRICE_1 添加到表中。让我们尝试添加子单位价格：
–-migrate the data from the xmlcolumn to the table
WITH XMLNAMESPACES(DEFAULT 'urn:MyFile-schema')
INSERT INTO GROUPTABLE
Select
t.b.value('UNIT[UNITNAME=“Unit1”]/../SUBUNIT[1]/SUBUNITPRICE[1]', 'varchar(7)') AS SUBUNITPRICE_1
FROM XMLTestTable
CROSS APPLY
DATA.nodes('//MyFile/GROUP) AS t(b)
生成我在开头提到的错误。
XQuery [XMLTestTable.DATA.value()]：无法隐式原子化或将“fn:data()”应用于复杂内容元素，在推断类型“元素”中找到类型“xs:anyType”({urn:MyFile-schema}:SUBUNITPRICE ,xs:anyType) *'.
如果你在这漫长的事情中坚持我，我很感激！如果你走到这一步，我的问题是：
- 查询中的 xpath 表达式我做错了什么？
- 我可以做些什么来加快将 XML 文件导入到色谱柱中的速度？
- 我可以做些什么来加快列到表中的迁移？
- 为每个 UNIT 制作一堆表而不是为所有 UNIT 制作一张表会更好吗？
- 最后，我听说有人使用 XML 对象并一次解析文件说 10k 行并以这样的过程循环遍历它们（将文件的一部分作为对象变量导入，将其放入表中，重复多次行，然后一次迁移 10k 行）。这可能吗？它会有帮助吗？
2018 年 10 月 30 日更新
至于导入 xml 文件，我尝试了几件事。
我搞砸了使用 xml 变量而不是将其转储到表中，我发现变量加载速度稍微快了一点……在加载 xml 时可能会节省一两秒钟。我还比较了直接加载到表中与将 xml 加载到变量中然后将变量插入到列中。他们花费了相同的时间，或者至少可以忽略不计。我还没有比较查询变量或列是否更快以及索引是否有影响，尽管我读过的所有内容都表明索引（特别是具有更多嵌套 xml 的二级索引）大大减少了加载后查询时间。目前，我在此更新中的测试不涉及变量。我不太确定从这里去哪里，但我有一些想法，如果有的话，我会更新。
不验证模式更快。如果文件已经过验证，或者您可以对数据充满信心，那么这样做就不值得了。我暂时将其从进一步测试中删除。
删除 xml 列上的主键可以更快地加载到列中。由于将数据存储为 xml 的开销太大，而我的目标是将其转换为 RDBMS 形式，这可能也不值得，但我不确定。现在，我也已将其从进一步的测试中删除。
我搞砸了加载 GROUP 父节点数量较少的多个文件，它在加载 xml 方面产生了很大的不同（我还没有看到它如何影响查询和转换时间）。我以增量方式创建了 xml 集，最多 10,000 个 GROUP 节点，然后是 50,000 个 GROUP 节点，然后编写动态 sql 查询以批量导入它们（我将在找到结果后发布查询）。查询将 xml 直接放在 table/xml 列中。
我运行的第一个测试是将直接加载到一个表（一个包含 10,000 个 GROUP 节点的 xml 文件）与加载 10,000 个文件（每个文件有一个 GROUP 节点）进行比较。
10,000 个 GROUP 节点的 1 个文件需要 2 秒才能加载到 1 行
1 GROUP 节点的 10,000 个文件需要 19 秒才能加载到 10,000 行中
我运行的第二个测试是比较不同数量的 50,000 个 GROUP 节点。
5,000 个 GROUP 节点的 10 个文件需要 13 秒才能加载到 10 行中
10,000 个 GROUP 节点的 5 个文件需要 6 秒才能加载到 5 行
50,000 个 GROUP 节点的 1 个文件需要 12 秒才能加载到 1 行
显然，一次 10k 比这里的其他任何东西都要好得多，至少对于 50K GROUP 节点而言。如果我正在加载 100K 个节点，这可能会改变。也许我明天会尝试并报告。
注意：我在右下角使用了查询计时器，所以这里的精度是 1 秒。
这是我用来执行此操作的动态 sql 查询：
--variables for dynsql and loop
DECLARE @i int --I named the files with an integer so I could insert them into the loop
DECLARE @dsql varchar(Max)
--create xml table
CREATE TABLE xTable
(
ID INT IDENTITY PRIMARY KEY,
xData XML NOT NULL
);
--loop dynsql to import xml to xTable
Set @i = '1'
WHILE @i <=1 --I changed this depending on how many files I was loading
BEGIN
Set @dsql = 'INSERT INTO xTable(xData)
SELECT CONVERT(XML, BulkColumn) AS BulkColumn
FROM OPENROWSET(Bulk ' + Char(39) + 'C:\MyPath\' + CAST(@i as nvarchar(5)) + '.xml' + CHAR(39) + ', SINGLE_BLOB) x;'
Exec(@dsql)
SET @i = @i + 1
END;
这对我提出了更多问题，我不是在这里寻找答案，而是将它们写下来供任何跟随者思考。
1 行 50k GROUP 节点和 5 行 10k GROUP 节点之间的 xml 到 RDBMS 转换有什么区别？
将 50k GROUP 节点的 1 个 xml 转换为 10k GROUP 节点的 5 个 xml 需要一段时间（我用 Excel VBA 做到了......我知道我可以使用其他东西，但它是我所拥有和知道的）。我猜最明智的做法可能是做一些 XSLT 工作（这是我需要学习的东西）
在这样做的过程中，我想知道是否有一种更快的方法可以通过 sql server 中的 sql server xml 变量将 xml 解析为 10k 节点片段，并且会比在 sql server 之外进行解析更快（我不知道在哪里从这里开始）。
对于仍在关注的任何人，或者如果您碰巧在此线程上
100 个文件，每个文件有 1,000 个 GROUP 节点 - 25 秒
40 个文件，每个文件有 2,500 个 GROUP 节点 - 24 秒
20 个文件，每个文件有 5,000 个 GROUP 节点 - 24 秒
10 个文件，每个文件有 10,000 个 GROUP 节点 - 24 秒
4 个文件，每个文件有 25,000 个 GROUP 节点 - 20 秒
2 个文件，每个文件有 50,000 个 GROUP 节点 - 22 秒
1 个包含 100,000 个 GROUP 节点的文件 - 22 秒
您应该将
UNITNAME元素放在谓词中(UNIT[UNITNAME = "Unit1"]/SUBUNIT/SUBUNITPRICE)[1]一个有效的查询看起来像这样，其中
@X包含整个 XML：据我所知没有。但是，我会将它加载到变量而不是表中。
如果您不需要验证 XML，您可以跳过模式。验证需要时间。有人说使用模式会加快分解 XML 的速度，如果您在使用和不使用模式的情况下尝试上面的查询，那将是正确的。如果您不使用模式，则需要重写查询以指定
text()节点，然后在没有模式的情况下具有相同的分解性能。在我看来，您最终会得到一个返回近 50 列的查询。在 SQL Server 中查询 XML 数据类型的当前实现中，速度会很慢。此处提供更多信息，并在此处投票支持更改。
使用 OPENXML 的替代方法对您来说看起来像这样。
注意：你不应该使用
text()OPENXML 中的节点，它会更慢。我不熟悉您正在使用的特定 XQuery 实现，或者实际上不熟悉 XQuery 和 SQL 的一般混合，但我将从相当无知的角度提供一些建议。
首先，您的路径表达式
UNIT[UNITNAME=“Unit1”]/../SUBUNIT[1]/SUBUNITPRICE[1]看起来不对。SUBUNIT在您的数据中是 的子元素UNIT，因此..向上移动到元素父UNIT元素的步骤是错误的。通常我希望像这样的不正确路径会导致路径表达式什么都不选择；在您的情况下，我认为这可能导致处理器做出错误的类型推断，该类型推断SUBUNITPRICE是类型的xs:anyType，因此不可原子化。这是问题1。我不知道你其他问题的答案。