Skip to Main Content
  • Questions
  • No data found extracting information from xml

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Manuel.

Asked: March 27, 2020 - 12:15 pm UTC

Answered by: Chris Saxon - Last updated: March 30, 2020 - 10:13 am UTC

Category: PL/SQL - Version: 12C

Viewed 100+ times

You Asked

Why can't you find data in an XML when I have proof of its existence?

with LiveSQL Test Case:

and we said...

The XMLNamespaces clause has a default:

XMLNAMESPACES( DEFAULT 'http://tempuri.org/', 


So it's assuming this namespace if none other is provided.

Remove this default and all should be good:

SELECT T.MYCODE 
FROM XMLTABLE(
  XMLNAMESPACES( 
    'http://schemas.xmlsoap.org/soap/envelope/' AS "SOAP-ENV", 
    'http://schemas.xmlsoap.org/soap/encoding/' AS "SOAP-ENC", 
    'http://www.w3.org/2001/XMLSchema'          AS "xsd", 
    'http://www.w3.org/2001/XMLSchema-instance' AS "xsi", 
    'http://scxgxtt.phx-dc.dhl.com/euExpressRateBook/RateMsgResponse' AS "rateresp"
  ), 
  '/SOAP-ENV:Envelope/SOAP-ENV:Body/rateresp:RateResponse/Provider' 
  PASSING  xmltype('<?xml version="1.0" encoding="UTF-8"?>
<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <SOAP-ENV:Header />
  <SOAP-ENV:Body>
    <rateresp:RateResponse xmlns:rateresp="http://scxgxtt.phx-dc.dhl.com/euExpressRateBook/RateMsgResponse">
      <Provider code="DHL">
        <Notification code="0">
          <Message />
        </Notification>
        <Service type="N">
          <TotalNet>
            <Currency>EUR</Currency>
            <Amount>7.34</Amount>
          </TotalNet>
          <Charges>
            <Currency>EUR</Currency>
            <Charge>
              <ChargeType>EXPRESS DOMESTIC</ChargeType>
              <ChargeAmount>6.46</ChargeAmount>
            </Charge>
            <Charge>
              <ChargeCode>FF</ChargeCode>
              <ChargeType>FUEL SURCHARGE</ChargeType>
              <ChargeAmount>0.88</ChargeAmount>
            </Charge>
          </Charges>
          <DeliveryTime>2020-01-22T23:59:00</DeliveryTime>
          <CutoffTime>2020-01-21T16:00:00</CutoffTime>
          <NextBusinessDayInd>N</NextBusinessDayInd>
        </Service>
      </Provider>
    </rateresp:RateResponse>
  </SOAP-ENV:Body>
</SOAP-ENV:Envelope>') 
  COLUMNS MYCODE VARCHAR2(20) PATH '@code'
) t;

MYCODE   
DHL  

and you rated our response

  (1 rating)

Reviews

Thanks

March 30, 2020 - 10:50 am UTC

Reviewer: A reader

many thanks Chris!!

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.