具有级联名称空间的SQL Server Xml查询

健次郎

如何在SQL Server上使用SQL从下面的xml中选择“ pagekey”的值？我尝试使用命名空间使用.nodes，但是找不到正确的语法。

谢谢，

<?xml version='1.0' encoding='UTF-8'?>
<S:Envelope xmlns:S="http://schemas.xmlsoap.org/soap/envelope/">
    <S:Header>
        <WorkContext xmlns="http://oracle.com/weblogic/soap/workarea/">rO03ZWJsb2dpYy5hcHAuU0JNLVJhcG9yV1MAAADWAAAAI3dlYmxvZ2ljLndvcmthcmVhL0cmluZ1dvcmtDb250ZXh0ABIyMDIwLE4LjE0MjIuNDAAAA==</WorkContext>
    </S:Header>
    <S:Body>
        <ns0:getReportOutputResponse xmlns:ns0="http://report_xml.org">
            <return>
                <pagekey>i6161140E964FF7A072CD2E3F2BB9C0</pagekey>
                <report>&lt;?xml version="1.0"?>&lt;dataSet xmlns="http://report_xml.org/dataSet/201006">&lt;dataTable>&lt;id>C1&lt;/id>&lt;/dataTable>&lt;/dataSet></report>
            </return>
        </ns0:getReportOutputResponse>
    </S:Body>
</S:Envelope>

marc_s

像这样吗？

DECLARE @data XML = '<S:Envelope xmlns:S="http://schemas.xmlsoap.org/soap/envelope/">
    <S:Header>
        <WorkContext xmlns="http://oracle.com/weblogic/soap/workarea/">rO03ZWJsb2dpYy5hcHAuU0JNLVJhcG9yV1MAAADWAAAAI3dlYmxvZ2ljLndvcmthcmVhL0cmluZ1dvcmtDb250ZXh0ABIyMDIwLE4LjE0MjIuNDAAAA==</WorkContext>
    </S:Header>
    <S:Body>
        <ns0:getReportOutputResponse xmlns:ns0="http://report_xml.org">
            <return>
                <pagekey>i6161140E964FF7A072CD2E3F2BB9C0</pagekey>
                <report>&lt;?xml version="1.0"?>&lt;dataSet xmlns="http://report_xml.org/dataSet/201006">&lt;dataTable>&lt;id>C1&lt;/id>&lt;/dataTable>&lt;/dataSet></report>
            </return>
        </ns0:getReportOutputResponse>
    </S:Body>
</S:Envelope>';

-- define the two relevant XML namespaces
WITH XMLNAMESPACES('http://schemas.xmlsoap.org/soap/envelope/' AS soap, 
                   'http://report_xml.org' AS RP)
SELECT
    -- get the "pagekey" element from the "body"
    XC.value('(RP:getReportOutputResponse/return/pagekey/text())[1]', 'VARCHAR(100)')
FROM 
    -- get the <s:Body> part as XML fragment
    @data.nodes('/soap:Envelope/soap:Body') AS XT(XC)

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。