Home » Developer & Programmer » JDeveloper, Java & XML » Help ! Extract data from XML result in PL/SQL
Help ! Extract data from XML result in PL/SQL [message #348878] Thu, 18 September 2008 03:43 Go to next message
Messages: 15
Registered: February 2008
Junior Member

I need some help urgently on how to extract the result (which is
'helloTestingConcat' from the following XML. I am getting the following result from a web service and I need to extract the result of the web service from the XML. How do I do it?

The below code is not working.

v_lob clob;
v_xml xmltype;
v_ret_val varchar2(1000);

v_lob := '<env:Envelopexmlns:env="http://schemas.xmlsoap.org/soap/envelope/"><env:Header/><env:Body><Simple_test1ProcessResponse xmlns="http://xmlns.oracle.com/Simple_test1">
<result xmlns="http://xmlns.oracle.com/Simple_test1">helloTestingConcat</result>

v_xml := xmltype(v_lob);


-- extractvalue needs to be used in select statement

select extractvalue(v_xml,'//Envelope/Body/Simple_test1ProcessResponse/result') into v_ret_val from dual;

dbms_output.put_line('Value is'||v_ret_val);

Re: Help ! Extract data from XML result in PL/SQL [message #348889 is a reply to message #348878] Thu, 18 September 2008 04:07 Go to previous message
Michel Cadot
Messages: 68601
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

What does "is not working" mean? What is the expected result?

Previous Topic: problem while searching for files in a directory
Next Topic: I Can't launch sqlplus from JDeveloper
Goto Forum:

Current Time: Mon Mar 04 13:26:14 CST 2024