如何加速此SQL Server XML查询的执行速度？

针对你提供的XML查询慢的问题，以下是几个实用的优化方向，附修改后的代码示例：

1. 避免不必要的动态SQL，参数化文件路径 #

原代码通过字符串拼接生成动态SQL，既存在注入风险，也无法重用执行计划。可以将文件路径作为参数传递给sp_executesql，同时固定命名空间（既然你的命名空间是固定值，无需动态拼接）：

DECLARE @Filepath NVARCHAR(100) = N'C:\Test\Test.xml';

DECLARE @sql NVARCHAR(MAX) = N'
;WITH XMLNAMESPACES (
    ''http://www.kadaster.nl/schemas/lvbag/imbag/datatypennen3610/v20200601'' AS DatatypenNEN361,
    ''http://www.kadaster.nl/schemas/lvbag/imbag/objecten/v20200601'' AS Objecten
)
SELECT
    r.x.value(''(Objecten:identificatie)[1]'', ''NVARCHAR(50)'') AS Identifictie,
    r.x.value(''(Objecten:oorspronkelijkBouwjaar)[1]'', ''NVARCHAR(10)'') AS Bouwjaar
FROM (  
    SELECT * FROM OPENROWSET(BULK @FilePath, SINGLE_XML) AS T(MY_XML)  
) AS T(MY_XML)
CROSS APPLY T.MY_XML.nodes(''/Objecten:bagStand/Objecten:standBestand/Objecten:stand/Objecten:bagObject/Objecten:Pand'') r(x)
';

EXEC sp_executesql @sql, N'@FilePath NVARCHAR(100)', @FilePath;

2. 直接读取XML类型，避免CAST转换 #

使用SINGLE_XML代替SINGLE_BLOB，让OPENROWSET直接返回XML类型数据，省去后续的CAST转换步骤，减少CPU开销。

3. 明确命名空间前缀，避免通配符遍历 #

原查询中使用*:通配符匹配命名空间，SQL Server需要遍历所有已定义的命名空间才能找到对应节点。明确指定前缀（比如Objecten:）后，查询引擎可以直接定位节点，大幅提升XPath解析速度。

4. 缓存XML数据到临时表（针对多次查询场景） #

如果需要重复查询该XML文件，先将数据加载到临时表，后续查询直接从临时表读取，避免重复读取磁盘文件：

-- 加载XML到临时表
CREATE TABLE #TempXML (XMLData XML);
INSERT INTO #TempXML
SELECT * FROM OPENROWSET(BULK N'C:\Test\Test.xml', SINGLE_XML) AS T(MY_XML);

-- 创建主XML索引（如果XML数据量大）
CREATE PRIMARY XML INDEX IX_TempXML_XMLData ON #TempXML(XMLData);
-- 可选：创建路径索引优化节点查询
CREATE XML INDEX IX_TempXML_Path ON #TempXML(XMLData)
USING XML INDEX IX_TempXML_XMLData FOR PATH;

-- 执行查询
;WITH XMLNAMESPACES (
    ''http://www.kadaster.nl/schemas/lvbag/imbag/datatypennen3610/v20200601'' AS DatatypenNEN361,
    ''http://www.kadaster.nl/schemas/lvbag/imbag/objecten/v20200601'' AS Objecten
)
SELECT
    r.x.value(''(Objecten:identificatie)[1]'', ''NVARCHAR(50)'') AS Identifictie,
    r.x.value(''(Objecten:oorspronkelijkBouwjaar)[1]'', ''NVARCHAR(10)'') AS Bouwjaar
FROM #TempXML
CROSS APPLY XMLData.nodes(''/Objecten:bagStand/Objecten:standBestand/Objecten:stand/Objecten:bagObject/Objecten:Pand'') r(x);

-- 清理临时表
DROP TABLE #TempXML;

5. 合理使用XML索引 #

当XML数据体积较大时，创建主XML索引和路径索引可以显著提升节点查询效率。注意：索引会增加数据写入的开销，仅在查询性能需求高于写入性能时使用。

6. 检查磁盘IO与文件位置 #

确保XML文件存储在高速存储介质（如SSD）上，避免磁盘IO成为瓶颈。如果是网络共享路径，尽量将文件复制到本地磁盘后再查询。

内容的提问来源于stack exchange，提问作者user2275634