在SQL Server中使用XQuery读取XML HTTP响应指定节点

需要从存储在变量的HTTP XML响应里，用XQuery把cmn:GeneralResponse节点内的值读取到表中。

XML示例 #

DECLARE @xml XML = 
N'<soapenv:Envelope
    xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
    <soapenv:Header
        xmlns:wsse="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd"
        xmlns:v1="http://xmlns.fl.com/GetSubscriberInfoRequest/V1"
        xmlns:cor="http://soa.fl.co/coredata_1"
        xmlns:v3="http://xmlns.fl.com/RequestHeader/V3"
        xmlns:v2="http://xmlns.fl.com/ParameterType/V2">
        <cor:SOATransactionID>424b89ab-5d1c-4f51-9aee-63b9f7fdca13</cor:SOATransactionID>
    </soapenv:Header>
    <soapenv:Body
        xmlns:v1="http://xmlns.fl.com/GetSubscriberInfoRequest/V1"
        xmlns:cor="http://soa.mic.co.af/coredata_1"
        xmlns:v3="http://xmlns.fl.com/RequestHeader/V3"
        xmlns:v2="http://xmlns.fl.com/ParameterType/V2">
        <soapenv:Fault>
            <faultcode
                xmlns:env="http://schemas.xmlsoap.org/soap/envelope">env:Server
            </faultcode>
            <faultstring>Service Authentication Failed.</faultstring>
            <detail>
                <ns1:GetSubscriberInfoFault
                    xmlns:ns1="http://xmlns.fl.com/GetSubscriberInfoFault/V1"
                    xmlns:cmn="http://xmlns.fl.com/ResponseHeader/V3">
                    <cmn:ResponseHeader>
                        <cmn:GeneralResponse>
                            <cmn:correlationID>28589788267412344000</cmn:correlationID>
                            <cmn:status>ERROR</cmn:status>
                            <cmn:code>getsubscriberinfo-1055-2505-F</cmn:code>
                            <cmn:description>Service Authentication Failed.</cmn:description>
                        </cmn:GeneralResponse>
                    </cmn:ResponseHeader>
                </ns1:GetSubscriberInfoFault>
            </detail>
        </soapenv:Fault>
    </soapenv:Body>
</soapenv:Envelope>';

失败的尝试代码 #

;WITH XMLNAMESPACES(DEFAULT 'http://schemas.xmlsoap.org/soap/envelope/'
,'http://xmlns.fl.com/GetSubscriberInfoFault/V1' AS ns1
, 'http://xmlns.fl.com/ResponseHeader/V3' AS cmn)
SELECT r.value('(cmn:status/text())[1]','varchar(100)') AS [status]
,r.value('(cmn:description/text())[1]','varchar(100)') AS [description]
FROM @XML.nodes('/Envelope/Body/Fault/ns1:GetSubscriberInfoFault/cmn:ResponseHeader/cmn:GeneralResponse') AS t1(r);

问题分析与解决 #

失败原因是XPath路径缺失了<detail>节点——ns1:GetSubscriberInfoFault是嵌套在Fault节点下的detail节点内的，原路径直接跳过了这个节点，导致无法定位到目标元素。

修改后的正确代码如下，补充了detail节点的路径，同时可以提取所有需要的字段：

;WITH XMLNAMESPACES(
    DEFAULT 'http://schemas.xmlsoap.org/soap/envelope/',
    'http://xmlns.fl.com/GetSubscriberInfoFault/V1' AS ns1,
    'http://xmlns.fl.com/ResponseHeader/V3' AS cmn
)
SELECT 
    r.value('(cmn:correlationID/text())[1]', 'bigint') AS correlationID,
    r.value('(cmn:status/text())[1]', 'varchar(100)') AS status,
    r.value('(cmn:code/text())[1]', 'varchar(100)') AS code,
    r.value('(cmn:description/text())[1]', 'varchar(200)') AS description
FROM @xml.nodes('/Envelope/Body/Fault/detail/ns1:GetSubscriberInfoFault/cmn:ResponseHeader/cmn:GeneralResponse') AS t1(r);

执行后会返回如下结构化结果：

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