代码之家 › 专栏 › 技术社区 › junkone

Oracle extractValue返回无效令牌

oracle xml sql

junkone · 技术社区 · 7 年前

我有一个查询,它在这个提取值上失败

EXTRACTVALUE(xmltype(REQUEST), '/soapenv:Envelope/soapenv:Body/ns2:getCustomerRequestRetrieve/ns2:requestHeader/ns5:referenceID', 'xmlns:soapenv:http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns8="ca/bell/oms/autotype/customerrequestretrieve" xmlns:ns2="ca/bell/oms/autotype/customerrequestretrieve"') RequestReferenceID

在某个地方我走错了路。你能告诉我哪里出了错吗实际的XML是

<soapenv:Envelope
    xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
    <soapenv:Body>
        <ns2:getCustomerRequestRetrieve xmlns = "ca/bell/oms/autotype/omscommonresponse"
            xmlns:ns5 = "sa/cell/oms/autotype/omscommonrequest"
            xmlns:ns2 = "sa/cell/oms/autotype/customerrequestretrieve"
            xmlns:ns3 = "sa/cell/oms/autotype/omscommon"
            xmlns:ns4 = "sa/cell/oms/customerprofile">
            <ns2:requestHeader>
                <ns5:customerInteractionType>CustomerNotification</ns5:customerInteractionType>
                <ns5:serviceRequestUserId>null</ns5:serviceRequestUserId>
                <ns5:serviceConsumer>khg</ns5:serviceConsumer>
                <ns5:serviceRequestTimestamp>2019-02-05T03:50:12.000-05:00</ns5:serviceRequestTimestamp>
                <ns5:language>English</ns5:language>
                <ns5:referenceID>Tjutrf7T78H4</ns5:referenceID>
                <ns5:transactionIdentifier>eed7ffe0-da22-498f-9913-c2279d1549356612606</ns5:transactionIdentifier>
            </ns2:requestHeader>
            <ns2:searchCriteria>
                <ns2:requestIdentifier>
                    <ns2:orderNumber>TTjutrf7T8H4</ns2:orderNumber>
                </ns2:requestIdentifier>
            </ns2:searchCriteria>
            <ns2:filterCriteria>
                <ns2:fullOrderDetail>ContactOnly</ns2:fullOrderDetail>
            </ns2:filterCriteria>
        </ns2:getCustomerRequestRetrieve>
    </soapenv:Body>
</soapenv:Envelope>

在这里输入代码

0 回复 | 直到 7 年前

Arkadiusz Åukasiewicz 7 年前

名称空间声明中有错误。

EXTRACTVALUE(REQUEST, '/soapenv:Envelope/soapenv:Body/ns2:getCustomerRequestRetrieve/ns2:requestHeader/ns5:referenceID'
,'xmlns:soapenv=http://schemas.xmlsoap.org/soap/envelope/" 
xmlns:ns2="sa/cell/oms/autotype/customerrequestretrieve"
xmlns:ns5="sa/cell/oms/autotype/omscommonrequest"
') RequestReferenceID

此外,EXTRACTVALUE函数已被弃用。替换它的是xmlquery,xmltable。

select xmlcast(xmlquery('declare namespace soapenv="http://schemas.xmlsoap.org/soap/envelope/";
declare namespace ns2="sa/cell/oms/autotype/customerrequestretrieve";
declare namespace ns5="sa/cell/oms/autotype/omscommonrequest";
/soapenv:Envelope/soapenv:Body/ns2:getCustomerRequestRetrieve/ns2:requestHeader/ns5:referenceID/text()' passing xmltype(request) returning content) as varchar2(100)) from ....;