如何在SQL Server 2014中读取HTTP XML响应指定节点值

方法一：使用XQuery（推荐） #

SQL Server 2014支持XQuery，处理带命名空间的XML更简洁。核心是通过WITH XMLNAMESPACES声明XML中的命名空间，再用.value()方法定位节点提取值。

示例代码：

DECLARE @XmlResponse XML = '
<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Body>
    <ns2:requestTopupResponse xmlns:ns2="http://external.interfaces.ers.seamless.com/">
        <return>
            <ersReference>2022051811593846801000016</ersReference>
            <resultCode>0</resultCode>
            <resultDescription>You have topped up 6.00</resultDescription>
            <!-- 省略其他节点 -->
        </return>
    </ns2:requestTopupResponse>
</soap:Body>
</soap:Envelope>';

-- 声明XML命名空间
WITH XMLNAMESPACES (
    'http://schemas.xmlsoap.org/soap/envelope/' AS soap,
    'http://external.interfaces.ers.seamless.com/' AS ns2
)
-- 提取目标节点值到变量
DECLARE @ErsReference VARCHAR(50), @ResultCode INT, @ResultDescription VARCHAR(100);
SELECT
    @ErsReference = @XmlResponse.value('(soap:Envelope/soap:Body/ns2:requestTopupResponse/return/ersReference)[1]', 'VARCHAR(50)'),
    @ResultCode = @XmlResponse.value('(soap:Envelope/soap:Body/ns2:requestTopupResponse/return/resultCode)[1]', 'INT'),
    @ResultDescription = @XmlResponse.value('(soap:Envelope/soap:Body/ns2:requestTopupResponse/return/resultDescription)[1]', 'VARCHAR(100)');

-- 验证提取结果
SELECT @ErsReference AS ErsReference, @ResultCode AS ResultCode, @ResultDescription AS ResultDescription;

方法二：修正OPENXML用法 #

你之前使用OPENXML失败，大概率是未正确处理XML命名空间。以下是修正后的实现：

DECLARE @XmlResponse XML = '
<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Body>
    <ns2:requestTopupResponse xmlns:ns2="http://external.interfaces.ers.seamless.com/">
        <return>
            <ersReference>2022051811593846801000016</ersReference>
            <resultCode>0</resultCode>
            <resultDescription>You have topped up 6.00</resultDescription>
            <!-- 省略其他节点 -->
        </return>
    </ns2:requestTopupResponse>
</soap:Body>
</soap:Envelope>';

DECLARE @DocHandle INT;
-- 创建XML文档句柄并指定命名空间
EXEC sp_xml_preparedocument @DocHandle OUTPUT, @XmlResponse, 
'<root xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns2="http://external.interfaces.ers.seamless.com/"/>';

-- 提取目标节点值到变量
DECLARE @ErsReference VARCHAR(50), @ResultCode INT, @ResultDescription VARCHAR(100);
SELECT
    @ErsReference = ersReference,
    @ResultCode = resultCode,
    @ResultDescription = resultDescription
FROM OPENXML(@DocHandle, '/soap:Envelope/soap:Body/ns2:requestTopupResponse/return', 2)
WITH (
    ersReference VARCHAR(50) 'ersReference',
    resultCode INT 'resultCode',
    resultDescription VARCHAR(100) 'resultDescription'
);

-- 释放XML文档句柄
EXEC sp_xml_removedocument @DocHandle;

-- 验证提取结果
SELECT @ErsReference AS ErsReference, @ResultCode AS ResultCode, @ResultDescription AS ResultDescription;

关键注意事项 #

• 必须完整声明XML中的所有命名空间（soap和ns2），否则SQL无法定位到目标节点。
• XQuery方法无需手动释放资源，比OPENXML更高效易维护，优先推荐使用。
• 节点路径中的[1]确保只提取第一个匹配的节点值，避免多节点场景下的取值错误。

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