ORACLE 12c XML: Extract value from XML string using EXTRACTVALUE

Question

Id appreciate your help on this one..

I have an XML string contained in an XMLType and Im trying to extract a single value from it.. I've spent ages now based on lots of online research, but without success...

the string held in l_resp_xml (xmltype) is:

<ns1:validateAccountResponse 
xmlns:ns1="https://ws.hyphen.co.za/accountvalidation2">
<return xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"     xsi:type="tns:WebService_AccountValidation_Response">
     <reference xsi:type="xsd:string">TLPM165U2876100975</reference>
     <branchCode xsi:type="xsd:string">187646</branchCode>
     <bankName xsi:type="xsd:string">NED:NEDBANK LTD RSA</bankName>
     <branchName xsi:type="xsd:string">NEDBANK BRITS</branchName>
     <accountNumber xsi:type="xsd:string">00000002876100975</accountNumber>
     <accountType xsi:type="xsd:string">2</accountType>
     <errorCode xsi:type="xsd:string">0</errorCode>
     <resultCode xsi:type="xsd:string">0000</resultCode>
     <resultMessage xsi:type="xsd:string"/>
     <checkSum xsi:type="xsd:string">db8d375002ca1e62ab4e8696c480df12d7e10293a29ba1a0dc99868d18    aa2c7e</checkSum>
</return></ns1:validateAccountResponse>

And Ive tried a bunch of different ways to get the value of bankName out. The latest is simply:

         SELECT 
         EXTRACTVALUE(l_resp_xml, '/return/bankName')
         into l_bank_name
     FROM DUAL;

I've tried lots of variations, but cannot get the value from bankName (the contents of l_bank_name is always blank.. if someone could help, Id be grateful

Many Thanks Richard

Alex Poole · Accepted Answer · 2015-11-04 10:11:53Z

3

EXTRACTVALUE has been deprecated for some time. You should be using XQuery, in this case with a single simple path using XMLQuery; plus you need to refer to the namespace ns1, and you are ignoring the top-level node validateAccountResponse:

with t (l_resp_xml) as (
  select xmltype('<ns1:validateAccountResponse 
xmlns:ns1="https://ws.hyphen.co.za/accountvalidation2">
<return xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"     xsi:type="tns:WebService_AccountValidation_Response">
     <reference xsi:type="xsd:string">TLPM165U2876100975</reference>
     <branchCode xsi:type="xsd:string">187646</branchCode>
     <bankName xsi:type="xsd:string">NED:NEDBANK LTD RSA</bankName>
     <branchName xsi:type="xsd:string">NEDBANK BRITS</branchName>
     <accountNumber xsi:type="xsd:string">00000002876100975</accountNumber>
     <accountType xsi:type="xsd:string">2</accountType>
     <errorCode xsi:type="xsd:string">0</errorCode>
     <resultCode xsi:type="xsd:string">0000</resultCode>
     <resultMessage xsi:type="xsd:string"/>
     <checkSum xsi:type="xsd:string">db8d375002ca1e62ab4e8696c480df12d7e10293a29ba1a0dc99868d18    aa2c7e</checkSum>
</return></ns1:validateAccountResponse>') from dual
)
SELECT XMLQuery('declare namespace ns1 = "https://ws.hyphen.co.za/accountvalidation2"; (::)
    /ns1:validateAccountResponse/return/bankName/text()'
  PASSING l_resp_xml RETURNING CONTENT) AS bankName
FROM t;

BANKNAME                                                                       
--------------------------------------------------------------------------------
NED:NEDBANK LTD RSA

Or with your PL/SQL value:

SELECT CAST(XMLQuery('declare namespace ns1 = "https://ws.hyphen.co.za/accountvalidation2"; (::)
    /ns1:validateAccountResponse/return/bankName/text()'
  PASSING l_resp_xml RETURNING CONTENT) AS VARCHAR2(30))
INTO l_bank_name
FROM dual;

You could also use an XMLTable to extract multiple values more easily, particularly when you're getting multiple XMLType values from rows in a table:

with t (l_resp_xml) as (
  select xmltype('<ns1:validateAccountResponse 
xmlns:ns1="https://ws.hyphen.co.za/accountvalidation2">
<return xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"     xsi:type="tns:WebService_AccountValidation_Response">
     <reference xsi:type="xsd:string">TLPM165U2876100975</reference>
     <branchCode xsi:type="xsd:string">187646</branchCode>
     <bankName xsi:type="xsd:string">NED:NEDBANK LTD RSA</bankName>
     <branchName xsi:type="xsd:string">NEDBANK BRITS</branchName>
     <accountNumber xsi:type="xsd:string">00000002876100975</accountNumber>
     <accountType xsi:type="xsd:string">2</accountType>
     <errorCode xsi:type="xsd:string">0</errorCode>
     <resultCode xsi:type="xsd:string">0000</resultCode>
     <resultMessage xsi:type="xsd:string"/>
     <checkSum xsi:type="xsd:string">db8d375002ca1e62ab4e8696c480df12d7e10293a29ba1a0dc99868d18    aa2c7e</checkSum>
</return></ns1:validateAccountResponse>') from dual
)
SELECT x.reference, x.bankname, x.branchCode
FROM t
CROSS JOIN XMLTABLE(
  XMLNAMESPACES('https://ws.hyphen.co.za/accountvalidation2' as "ns1"),
  '/ns1:validateAccountResponse/return'
  PASSING t.l_resp_xml
  COLUMNS reference VARCHAR2(20) PATH 'reference',
    bankName VARCHAR2(30) PATH 'bankName',
    branchCode NUMBER PATH 'branchCode'
) x;

REFERENCE            BANKNAME                       BRANCHCODE
-------------------- ------------------------------ ----------
TLPM165U2876100975   NED:NEDBANK LTD RSA                187646

edited Nov 4, 2015 at 10:11

answered Nov 4, 2015 at 9:56

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

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

4 Comments

Richard Legge Over a year ago

Thanks Alex, The perils of finding sample code on the Net... Does this mean that Extract is also depreciated? Im using l_resp_xml := xmltype.createxml(l_clob_response); -- Clean SOAP header SELECT extract(l_resp_xml ,'Envelope/Body/node()' ,l_namespace_soap) INTO l_resp_xml FROM dual; to retrieve the XML fragment from the SOAP Envelope..

Alex Poole Over a year ago

Yes, it is. You can use XQuery for that too. Or just include the Envelope/Body and SOAP namespace along with the return in a single XQuery.

Alex Poole Over a year ago

@RichardLegge - this answer is extracting data from a SOAP envelope; very similar idea and shows the two namespaces.

Richard Legge Over a year ago

Great stuff thanks. Just got that working.. Thanks a lot for your input. appreciated.

Wernfried Domscheit · Accepted Answer · 2015-11-04 10:40:06Z

2

Although EXTRACTVALUE has been deprecated, this would be the version with EXTRACTVALUE:

SELECT 
    EXTRACTVALUE(l_resp_xml, '/ns1:validateAccountResponse/return/bankName', 'xmlns:ns1="https://ws.hyphen.co.za/accountvalidation2"')
INTO l_bank_name
FROM dual;

But XMLTable or XMLQuery is the preferred way of doing it.

answered Nov 4, 2015 at 10:40

Wernfried Domscheit

60.4k10 gold badges93 silver badges132 bronze badges

Collectives™ on Stack Overflow

ORACLE 12c XML: Extract value from XML string using EXTRACTVALUE

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related