ExtractValue Xml in Pl/Sql

Question

I try to parse value in this xml in pl/sql.

I want to get <tax:resultCode>1</tax:resultCode> tag value so that its '1'

Can you help me please :( ?

<?xml version="1.0" encoding="UTF-8"?>
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
    <soap:Header xmlns:wsa="http://schemas.xmlsoap.org/ws/2004/08/addressing"
                 xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
                 xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" 
                 xmlns:wsu="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd" 
                 xmlns:wsse="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd" 
                 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
         <soap:Header>
             <wsa:Action>http://tempuri.org/GetSerialNoDealerIsValidResponse</wsa:Action> 
             <wsa:To>http://schemas.xmlsoap.org/ws/2004/08/addressing/role/anonymous</wsa:To>
             <wsse:Security>
                 <wsu:Timestamp wsu:Id="Timestamp-d434622e-2bcd-4e80-a258-e5e503a964b1">
                     <wsu:Created>2016-07-20T17:08:58.379+03:00</wsu:Created> 
                 </wsu:Timestamp>
             </wsse:Security>
         </soap:Header>
     </soap:Header>
     <soap:Body xmlns:wsa="http://schemas.xmlsoap.org/ws/2004/08/addressing" 
     xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
     xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" 
     xmlns:wsu="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd" 
     xmlns:wsse="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd" 
     xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
         <tax:checkInventoryResponse xmlns:tax="http://www.avea.com.tr/TAXIM_INVENTORY_OPERATIONS">
             <ave:ResponseHeader xmlns:ave="http://www.avea.com.tr/aveaCommonTypes">
                 <ave:Tid>stringstringstringstring</ave:Tid>
             </ave:ResponseHeader>
             <tax:ResponseBody>
                 <tax:response>
                     <tax:ResponseItem>
                         <tax:resultCode>1</tax:resultCode>
                         <tax:responseValue>0000</tax:responseValue><tax:logID>stringstringstringstring</tax:logID> 
                     </tax:ResponseItem>
                 </tax:response>
             </tax:ResponseBody>
         </tax:checkInventoryResponse>
     </soap:Body>
 </soapenv:Envelope>

Answer 1

something like this

with xml as (select xmltype('<here you xml from question>') x from dual)

select EXTRACTVALUE(xml.x,'//tax:resultCode','xmlns:tax="http://www.avea.com.tr/TAXIM_INVENTORY_OPERATIONS"')
 from xml

here output

EXTRACTVALUE(XML.X,'//TAX:RESULTCODE','XMLNS:TAX="HTTP://WWW.AVEA.COM.TR/TAXIM_I
--------------------------------------------------------------------------------
1                                                             

"//" Used to identify all descendants of the current node. For example, PurchaseOrder//ShippingInstructions matches any ShippingInstructions element under the PurchaseOrder element. https://docs.oracle.com/cd/B19306_01/appdev.102/b14259/xdb04cre.htm#g1038245

or you can do like this (this expression would find all nodes, even ones bound to namespaces in which you are not interested.)

with xml as (select xmltype('<here you xml from question>') x from dual)
select EXTRACTVALUE(xml.x,'//*[local-name()="resultCode"]') from xml

or you can go long way from root element:

    with xml as (select xmltype('<here you xml from question>') x from dual)

select 
       extract(soapbody.column_value,
      '/tax:checkInventoryResponse/tax:ResponseBody/tax:response/tax:ResponseItem/tax:resultCode/text()',
      'xmlns:tax="http://www.avea.com.tr/TAXIM_INVENTORY_OPERATIONS"').getNumberVal()
from xml,
     TABLE(XMLSequence(extract(xml.x, '/soapenv:Envelope/*','xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"'))) soapenv,
     TABLE(XMLSequence(extract(soapenv.column_value, '/soap:Body/*','xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"'))) soapbody

for pl/sql statement it's must look like:

declare
 l_value varchar2(1);
begin

with xml as (select xmltype('<here you xml from question>') x from dual)

select EXTRACTVALUE(xml.x,'//tax:resultCode','xmlns:tax="http://www.avea.com.tr/TAXIM_INVENTORY_OPERATIONS"')
into l_value
 from xml;

end;