TSQL如何使用sp_xml_preparedocument读取无前缀命名空间的XML

• 运行环境：SQL Server 2019（版本号15.0.2095.3）
• 业务需求：读取XML文件内容并存入数据表
• 异常表现：
  • XML包含无前缀的默认命名空间时，查询执行无报错，但返回结果集为空
  • 移除XML内的无前缀默认命名空间后，查询可正常返回数据
  • 带自定义前缀的命名空间场景下，查询运行正常
• 约束要求：不修改原始XML内容，直接完成查询

DECLARE @idoc INT, @doc VARCHAR(1000);   
SET @doc =' 
<ROOT xmlns="http://test.com">  
<Customers>  
   <Orders>  
      <nr>5</nr>
      <line>
        <test>55</test>
        <test2>444</test2>
      </line>
   </Orders>  
</Customers>  
<Customers>  
   <Orders>    
      <nr>4</nr>
      <line>
        <test>44</test>
        <test2>444</test2>
      </line>
   </Orders>  
</Customers>  
</ROOT>';   
  
-- 创建XML文档的内存内部表示
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc, '<ROOT xmlns="http://test.com"/>'
  
-- 使用OPENXML行集提供者执行查询
SELECT *
FROM OPENXML (@idoc, '/ROOT/Customers/Orders/nr' ) 

EXEC sp_xml_removedocument @idoc;

核心原因是OPENXML的XPath匹配规则不支持直接引用未绑定前缀的默认命名空间：上述代码在sp_xml_preparedocument的第三个参数里只声明了默认命名空间，没有给它绑定可在路径中引用的前缀，此时查询语句里写的无命名空间前缀的路径，只会匹配不属于任何命名空间的节点，和默认命名空间下的节点完全不匹配，因此返回空结果。

以下两种方案均不需要修改原始XML内容，可直接使用：

方案1：兼容旧OPENXML逻辑 #

在sp_xml_preparedocument的命名空间声明参数中，给默认命名空间绑定一个自定义前缀（示例中用x），后续XPath路径中所有节点都带上这个前缀即可，修正后代码：

DECLARE @idoc INT, @doc VARCHAR(1000);   
SET @doc =' 
<ROOT xmlns="http://test.com">  
<Customers>  
   <Orders>  
      <nr>5</nr>
      <line>
        <test>55</test>
        <test2>444</test2>
      </line>
   </Orders>  
</Customers>  
<Customers>  
   <Orders>    
      <nr>4</nr>
      <line>
        <test>44</test>
        <test2>444</test2>
      </line>
   </Orders>  
</Customers>  
</ROOT>';   
  
-- 为默认命名空间绑定自定义前缀x
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc, '<ROOT xmlns:x="http://test.com"/>'
  
-- XPath路径中所有节点都添加x前缀
SELECT *
FROM OPENXML (@idoc, '/x:ROOT/x:Customers/x:Orders/x:nr' ) 

EXEC sp_xml_removedocument @idoc;

方案2：使用原生XQuery（推荐） #

SQL Server 2005及以上版本原生支持XQuery查询，不需要手动加载、释放XML文档内存，通过WITH XMLNAMESPACES直接声明默认命名空间即可查询，写法更简洁，也能避免OPENXML可能出现的内存泄漏问题，示例代码：

DECLARE @doc XML =' 
<ROOT xmlns="http://test.com">  
<Customers>  
   <Orders>  
      <nr>5</nr>
      <line>
        <test>55</test>
        <test2>444</test2>
      </line>
   </Orders>  
</Customers>  
<Customers>  
   <Orders>    
      <nr>4</nr>
      <line>
        <test>44</test>
        <test2>444</test2>
      </line>
   </Orders>  
</Customers>  
</ROOT>';

-- 声明默认命名空间后，XPath路径无需额外加前缀
WITH XMLNAMESPACES(DEFAULT 'http://test.com')
SELECT nr.value('.','INT') AS nr_value
FROM @doc.nodes('/ROOT/Customers/Orders/nr') AS T(nr)

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