在Oracle PL/SQL中从xml中提取值

Question

<s:Envelope xmlns:s="http://schemas.xmlsoap.org/soap/envelope/">
 <s:Body>
  <ShipmentTrackingResponse xmlns="http://ws.aramex.net/ShippingAPI/v1/">
   <Transaction xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
    <Reference1>001</Reference1>
    <Reference2 i:nil="true"/>
    <Reference3 i:nil="true"/>
    <Reference4 i:nil="true"/>
    <Reference5 i:nil="true"/>
  </Transaction>
  <Notifications xmlns:i="http://www.w3.org/2001/XMLSchema-instance"/>
  <HasErrors>false</HasErrors>
  <TrackingResults xmlns:a="http://schemas.microsoft.com/2003/10/Serialization/Arrays"         
  xmlns:i="http://www.w3.org/2001/XMLSchema-instance"/>
  <NonExistingWaybills xmlns:a="http://schemas.microsoft.com/2003/10/Serialization/Arrays"  
  xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
    <a:string>XXXXXXXXXX</a:string>
  </NonExistingWaybills>
 </ShipmentTrackingResponse>
</s:Body>

​

我使用了以下查询来提取值，但是如何提取NonExistingWaybills？

 SELECT EXTRACTVALUE(l_resp_xml,
                    '//ShipmentTrackingResponse/Transaction/Reference1',
                    'xmlns="http://ws.aramex.net/ShippingAPI/v1/"') 
  INTO l_response_result 
   FROM dual;

  DBMS_OUTPUT.put_line ( 'Result> Reference1=' || l_response_result);

  SELECT EXTRACTVALUE(l_resp_xml,
                     '//ShipmentTrackingResponse/TrackingResults',
                     'xmlns="http://ws.aramex.net/ShippingAPI/v1/"') 
    INTO l_response_result 
    FROM dual;

  DBMS_OUTPUT.put_line ( 'Result> TrackingResults=' || l_response_result);

  SELECT EXTRACTVALUE(l_resp_xml,
                     '//ShipmentTrackingResponse/NonExistingWaybills',
                     'xmlns="http://ws.aramex.net/ShippingAPI/v1/"') 
    INTO l_response_result 
    FROM dual;

  DBMS_OUTPUT.put_line ( 'Result> NonExistingWaybills=' || l_response_result);

最后一次查询没有给出任何结果...

Answer 1

它返回结果...

SELECT EXTRACTVALUE(l_resp_xml
                  , '//ShipmentTrackingResponse/NonExistingWaybills/node()'
                  , 'xmlns="http://ws.aramex.net/ShippingAPI/v1/"') 
INTO l_response_result
FROM dual;
DBMS_OUTPUT.put_line ( 'Result> NonExistingWaybills=' || l_response_result);

Answer 2

这将返回相同的结果，但没有select。我猜这就是你要找的：

l_response_result := l_resp_xml.extract(
    '//ShipmentTrackingResponse/NonExistingWaybills/node()'
).getstringval();