如何在SQL Server中从XML文件读取主数据？附XML示例

嘿，我来帮你搞定在SQL Server里读取这个XML主数据的问题！首先得注意你的XML带有命名空间（包括默认命名空间和omismsg前缀的命名空间），这是很多人踩坑的关键点——如果不声明这些命名空间，SQL Server会找不到对应的节点。下面我给你两种常用的实现方法：

方法一：使用XQuery（推荐，简洁且现代） #

XQuery是SQL Server处理XML的首选方式，语法更清晰，也不需要手动管理文档句柄。首先你需要用WITH XMLNAMESPACES声明XML里的所有命名空间，然后通过nodes()定位主节点，再用value()提取数据：

-- 替换成你XML里实际的命名空间URL
WITH XMLNAMESPACES (
    DEFAULT 'http://...',
    'http://...' AS omismsg
)
SELECT
    -- 提取主节点的核心字段
    Invoice.value('(status/text())[1]', 'INT') AS Status,
    Invoice.value('(transNo/text())[1]', 'VARCHAR(50)') AS TransNo,
    Invoice.value('(transType/text())[1]', 'INT') AS TransType,
    Invoice.value('(externalRefId/text())[1]', 'VARCHAR(50)') AS ExternalRefId,
    Invoice.value('(billExternalRef/text())[1]', 'VARCHAR(50)') AS BillExternalRef,
    -- 如果需要同时读取子节点invoiceDetails的字段，可添加以下内容
    InvoiceDetails.value('(seqNo/text())[1]', 'VARCHAR(10)') AS SeqNo
FROM
    -- 假设你的XML存储在变量@XmlData中，如果是表的XML列，替换成 YourTable.XmlColumn
    @XmlData.nodes('/invoice') AS MainNode(Invoice)
-- 关联子节点（如果不需要子节点数据可去掉这行）
OUTER APPLY Invoice.nodes('invoiceDetails') AS SubNode(InvoiceDetails);

关键点说明： #

• WITH XMLNAMESPACES必须放在SELECT语句的最开头，要严格匹配XML里的命名空间URL，不能有拼写错误
• nodes('/invoice')用来定位到XML的根主节点，返回每个主节点的行集
• value()方法里的[1]是为了确保只返回单个值（避免多个匹配时出错），text()用来提取元素的文本内容

方法二：使用OPENXML（兼容旧版本场景） #

如果你需要兼容SQL Server旧版本，或者习惯用OPENXML的方式，也可以用这种方法，但需要手动创建和释放XML文档句柄：

-- 定义XML变量，替换成你的完整XML内容
DECLARE @XmlData XML = '<invoice xmlns:xsi="http://......" xmlns="http://..." xmlns:omismsg="http://..." omismsg:action="update">
    <status>1</status>
    <transNo>17AUAU0000118N</transNo>
    <transType>5</transType>
    <externalRefId/>
    <billExternalRef/>
    <invoiceDetails omismsg:action="update">
        <transNo>17AUAU0000118N</transNo>
        <transType>5</transType>
        <seqNo>001</seqNo>
    </invoiceDetails>
</invoice>';

DECLARE @DocHandle INT;

-- 创建XML文档句柄，同时声明命名空间（这里给默认命名空间起了个前缀default）
EXEC sp_xml_preparedocument @DocHandle OUTPUT, @XmlData,
    '<root xmlns:default="http://..." xmlns:omismsg="http://..."/>';

-- 读取主节点数据
SELECT
    status, transNo, transType, externalRefId, billExternalRef
FROM
    OPENXML(@DocHandle, '/default:invoice', 2)
    WITH (
        status INT 'default:status',
        transNo VARCHAR(50) 'default:transNo',
        transType INT 'default:transType',
        externalRefId VARCHAR(50) 'default:externalRefId',
        billExternalRef VARCHAR(50) 'default:billExternalRef'
    );

-- 必须释放文档句柄，避免内存泄漏
EXEC sp_xml_removedocument @DocHandle;

关键点说明： #

• OPENXML不能直接使用DEFAULT关键字，所以需要给默认命名空间手动指定一个前缀（比如default）
• sp_xml_preparedocument和sp_xml_removedocument必须成对使用，防止内存泄漏
• 第三个参数是命名空间声明的XML片段，要和原XML的命名空间完全匹配

额外提示： #

• 如果你的XML是存储在表的XML类型列中，只需要把变量@XmlData替换成表的列名即可（比如YourTable.InvoiceXml.nodes(...)）
• 对于空元素（比如<externalRefId/>），两种方法都会返回NULL，符合预期
• 如果XML中有多个<invoice>节点，nodes()或OPENXML会自动返回多行数据，每个节点对应一行

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