Extracting values from xml in Oracle PL/SQL

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>

I have used the following queries to extract values, but how do i extract the 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);

The last query gives no result...

I tried this and it returned the XXXX value SELECT EXTRACTVALUE(xdata, '//ShipmentTrackingResponse/NonExistingWaybills/*', 'xmlns="ws.aramex.net/ShippingAPI/v1/"') from x — OldProgrammer
– OldProgrammer, Commented Jul 29, 2013 at 18:24

APC · Accepted Answer · 2013-07-30 12:19:05Z

2

It returned results...

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);

edited Jul 30, 2013 at 12:19

APC

147k20 gold badges180 silver badges289 bronze badges

answered Jul 30, 2013 at 9:32

Novice

5571 gold badge7 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gerrit Griebel · Accepted Answer · 2017-07-30 16:33:34Z

1

This will return the same result, but without select. I guess this is what you were looking for:

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

edited Jul 30, 2017 at 16:33

answered Jul 29, 2017 at 13:15

Gerrit Griebel

4634 silver badges11 bronze badges

3 Comments

help-info.de Over a year ago

From review queue: May I request you to please add some more context around your answer. Code-only answers are difficult to understand. It will help the asker and future readers both if you can add more information in your post. See also Explaining entirely code-based answers.

Gerrit Griebel Over a year ago

Just found here this note: "Prior to Oracle Database 11g Release 2, some users employed Oracle SQL functions extract and extractValue to do some of what can be done better using SQL/XML functions XMLQuery and XMLCast. SQL functions extract and extractValue are deprecated in Oracle Database 11g Release 2."

Pancho Over a year ago

Annoyingly, extract.getstringval() behaviour is subtly different from extractvalue in that if the node does not exist, extract returns "ORA-30625: method dispatch on NULL SELF argument is disallowed", whereas extractvalue returns null

Collectives™ on Stack Overflow

Extracting values from xml in Oracle PL/SQL

2 Answers 2

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related