使用T-SQL读取XML文件仅返回列名无数据，求排查问题

你的查询之所以只返回列名却没有数据，核心原因是你的XML文档带有默认命名空间，但你的查询完全没处理它！

看你的XML开头这行：

<Xa xmlns="urn:schemas-microsoft-com:xml-data" xmlns:dt="urn:schemas-microsoft-com:datatypes">

这里的xmlns="urn:schemas-microsoft-com:xml-data"定义了整个XML的默认命名空间，所有节点（包括Xa、GrandTotals、GrTValues）都属于这个命名空间。而你的OPENXML查询直接用/Xa/GrandTotals/GrTValues这种路径，相当于在“无命名空间”的范围里找节点，SQL Server根本找不到匹配的内容，自然返回空结果。

方法1：修改OPENXML查询，处理命名空间 #

你需要在初始化XML文档句柄时绑定命名空间前缀，然后在XPath里用前缀引用节点：

DECLARE @xmldata XML
SELECT @xmldata=BulkColumn FROM OPENROWSET (BULK 'C:\my.xml',SINGLE_BLOB) as T1

DECLARE @docHandle int
-- 绑定前缀ns到XML的默认命名空间
EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmldata, '<root xmlns:ns="urn:schemas-microsoft-com:xml-data"/>'

SELECT 
    GrTValTitle, 
    GrTValSales, 
    GrTValReturns, 
    GrTValNet 
FROM OPENXML(@docHandle, '/ns:Xa/ns:GrandTotals/ns:GrTValues') 
WITH (
    GrTValTitle nvarchar(20) '@GrTValTitle',
    GrTValSales nvarchar(20) '@GrTValSales',
    GrTValReturns nvarchar(20) '@GrTValReturns',
    GrTValNet nvarchar(20) '@GrTValNet'
)

EXEC sp_xml_removedocument @docHandle -- 别忘了释放文档句柄，避免内存泄漏

这里的关键变化：

• 调用sp_xml_preparedocument时，第三个参数添加了命名空间绑定声明
• XPath路径里每个节点都加上了ns:前缀，告诉SQL Server要找这个命名空间下的节点
• WITH子句里显式用@属性名指定读取的属性（虽然OPENXML默认会匹配，但显式写更清晰，也避免意外问题）

方法2：用更现代的XQuery方法（强烈推荐） #

OPENXML是比较老旧的XML处理方式，SQL Server现在支持更简洁直观的XQuery语法，不需要手动管理文档句柄，处理命名空间也更方便：

DECLARE @xmldata XML
SELECT @xmldata=BulkColumn FROM OPENROWSET (BULK 'C:\my.xml',SINGLE_BLOB) as T1

-- 声明默认命名空间，和XML里的保持一致
WITH XMLNAMESPACES (DEFAULT 'urn:schemas-microsoft-com:xml-data')
SELECT
    x.value('@GrTValTitle', 'nvarchar(20)') AS GrTValTitle,
    x.value('@GrTValSales', 'nvarchar(20)') AS GrTValSales,
    x.value('@GrTValReturns', 'nvarchar(20)') AS GrTValReturns,
    x.value('@GrTValNet', 'nvarchar(20)') AS GrTValNet
FROM @xmldata.nodes('/Xa/GrandTotals/GrTValues') AS t(x)

用WITH XMLNAMESPACES (DEFAULT '...')直接声明默认命名空间后，就可以像原来那样写XPath路径了，再通过nodes()方法定位到目标节点，value()提取属性值，代码更简洁，也更易维护。

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