Доступ к значениям в XML из переменной в Oracle SQL

ProgramBox

Доступ к значениям в XML из переменной в Oracle SQL

Post author:admin
Запись опубликована:19 июля, 2022
Post category:Вопросы по программированию

#oracle #oracle11g #oracle-sqldeveloper

#Oracle #oracle11g #oracle-sqldeveloper

Вопрос:

У меня есть XML-данные, как показано ниже, в переменной p_val:

 <env:Envelope xmlns:env="http://schemas.xmlsoap.org/soap/envelope/"
    xmlns:wsa="http://www.w3.org/2005/08/addressing">
    <env:Header>
        <wsa:MessageID>urn:bda29066-5961-11ec-87ec-0242c5d8b376</wsa:MessageID>
        <wsa:ReplyTo>
            <wsa:Address>http://www.w3.org/2005/08/addressing/anonymous</wsa:Address>
            <wsa:ReferenceParameters>
                <instra:tracking.ecid xmlns:instra="http://xmlns.oracle.com/sca/tracking/1.0">9b657011-42e4-4cf4-a78e-537551de4cc0-0057ca4a</instra:tracking.ecid>
                <instra:tracking.FlowEventId xmlns:instra="http://xmlns.oracle.com/sca/tracking/1.0">27818206</instra:tracking.FlowEventId>
                <instra:tracking.FlowId xmlns:instra="http://xmlns.oracle.com/sca/tracking/1.0">169007626</instra:tracking.FlowId>
                <instra:tracking.CorrelationFlowId xmlns:instra="http://xmlns.oracle.com/sca/tracking/1.0">0000NqYCMWFDGfdLxeG7yW1X_Nsm0003Yk</instra:tracking.CorrelationFlowId>
                <instra:tracking.quiescing.SCAEntityId xmlns:instra="http://xmlns.oracle.com/sca/tracking/1.0">1830036</instra:tracking.quiescing.SCAEntityId>
            </wsa:ReferenceParameters>
        </wsa:ReplyTo>
        <wsa:FaultTo>
            <wsa:Address>http://www.w3.org/2005/08/addressing/anonymous</wsa:Address>
        </wsa:FaultTo>
    </env:Header>
    <env:Body>
        <LaunchSpreadSheetWorkFlowResponse xmlns="http://xmlns.oracle.com/bpmn/bpmnCloudProcess/ISV_ROYALTIES/ISVSpreadSheetMainProcess">
            <instanceNumber xmlns:def="http://www.w3.org/2001/XMLSchema" xsi:type="def:long"
                xmlns=""
                xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">79630772</instanceNumber>
        </LaunchSpreadSheetWorkFlowResponse>
    </env:Body>
</env:Envelope>

Я хочу получить доступ к значению тега: instanceNumber, которое равно 79630772.

Если это прямая полезная нагрузка вместо переменной, я попытался получить к ней доступ с помощью:

 select *  
FROM XMLTABLE('/Envelope/Body/LaunchSpreadSheetWorkFlowResponse'  
         PASSING   
            xmltype('
                <env:Envelope xmlns:env="http://schemas.xmlsoap.org/soap/envelope/"
    xmlns:wsa="http://www.w3.org/2005/08/addressing">
    <env:Header>
        <wsa:MessageID>urn:bda29066-5961-11ec-87ec-0242c5d8b376</wsa:MessageID>
        <wsa:ReplyTo>
            <wsa:Address>http://www.w3.org/2005/08/addressing/anonymous</wsa:Address>
            <wsa:ReferenceParameters>
                <instra:tracking.ecid xmlns:instra="http://xmlns.oracle.com/sca/tracking/1.0">9b657011-42e4-4cf4-a78e-537551de4cc0-0057ca4a</instra:tracking.ecid>
                <instra:tracking.FlowEventId xmlns:instra="http://xmlns.oracle.com/sca/tracking/1.0">27818206</instra:tracking.FlowEventId>
                <instra:tracking.FlowId xmlns:instra="http://xmlns.oracle.com/sca/tracking/1.0">169007626</instra:tracking.FlowId>
                <instra:tracking.CorrelationFlowId xmlns:instra="http://xmlns.oracle.com/sca/tracking/1.0">0000NqYCMWFDGfdLxeG7yW1X_Nsm0003Yk</instra:tracking.CorrelationFlowId>
                <instra:tracking.quiescing.SCAEntityId xmlns:instra="http://xmlns.oracle.com/sca/tracking/1.0">1830036</instra:tracking.quiescing.SCAEntityId>
            </wsa:ReferenceParameters>
        </wsa:ReplyTo>
        <wsa:FaultTo>
            <wsa:Address>http://www.w3.org/2005/08/addressing/anonymous</wsa:Address>
        </wsa:FaultTo>
    </env:Header>
    <env:Body>
        <LaunchSpreadSheetWorkFlowResponse xmlns="http://xmlns.oracle.com/bpmn/bpmnCloudProcess/ISV_ROYALTIES/ISVSpreadSheetMainProcess">
            <instanceNumber xmlns:def="http://www.w3.org/2001/XMLSchema" xsi:type="def:long"
                xmlns=""
                xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">79630772</instanceNumber>
                <name>XYZ</name>
        </LaunchSpreadSheetWorkFlowResponse>
    </env:Body>
</env:Envelope>
            ')
         COLUMNS  
            temp2 varchar2(20)     PATH './instanceNumber'
     ) xmlt  
;

Приведенный выше запрос вернул пустое значение.

Как я должен получить доступ к значению из переменной?

1. В чем ваша актуальная проблема: пустое значение или как использовать переменную? Последнее зависит от того, как связаны эта переменная и SQL-запрос: если они находятся в одном блоке PL / SQL, просто передайте переменную вместо постоянного XML

2. привет, я хочу получить доступ к значению instanceNumber, которое является частью XML. и XML будет присутствовать в переменной p_xml .

Ответ №1:

Вам необходимо указать и указать пространства имен:

 select *  
FROM XMLTABLE(
         XMLNAMESPACES(
            default 'http://xmlns.oracle.com/bpmn/bpmnCloudProcess/ISV_ROYALTIES/ISVSpreadSheetMainProcess',
            'http://schemas.xmlsoap.org/soap/envelope/' AS "env"
         ),
         '/env:Envelope/env:Body/LaunchSpreadSheetWorkFlowResponse'  
         PASSING   
         ...
         COLUMNS  
            temp2 varchar2(20)     PATH '*:instanceNumber'
     ) xmlt  
;

Envelope И Body входят в env пространство имен SOAP. В документе XML LaunchSpreadSheetWorkFlowResponse узел не имеет префикса пространства имен, но определяет пространство имен по умолчанию, поэтому также необходимо указать значение по умолчанию. Тогда становится немного неудобно instanceNumber , поскольку этот узел переопределяет (или очищает) значение по умолчанию и определяет дополнительные пространства имен, но не использует их, что немного ставит этот узел в неопределенность. Я выбрал простой путь и подстановил его с *: помощью префикса.

Я думаю, что менее ленивым способом является обработка LaunchSpreadSheetWorkFlowResponse как отдельного фрагмента XML с помощью вызова seconf XMLTable:

 select xmlt2.instanceNumber
FROM XMLTABLE(
         XMLNAMESPACES(
            default 'http://xmlns.oracle.com/bpmn/bpmnCloudProcess/ISV_ROYALTIES/ISVSpreadSheetMainProcess',
            'http://schemas.xmlsoap.org/soap/envelope/' AS "env"
         ),
         '/env:Envelope/env:Body/LaunchSpreadSheetWorkFlowResponse'  
         PASSING   
         ...
         COLUMNS  
            LaunchSpreadSheetWorkFlowResponse xmlType     PATH '.'
     ) xmlt1
CROSS JOIN XMLTABLE(
         '.'  
         PASSING   
            xmlt1.LaunchSpreadSheetWorkFlowResponse
         COLUMNS  
            instanceNumber varchar2(20)     PATH 'instanceNumber'
     ) xmlt2
;

Я не уверен, что это того стоит, и у меня возникнет соблазн придерживаться подстановочной версии.

db<>скрипка, показывающая оба.