Thursday, March 10, 2011

How to extract values from an XML file?

Today someone asked me if I do know of an easy way to parse an html file using pl/sql? 
"I want to extract the values for all the tags in a html document. I’ve got it working by just parsing it and using instr and substr. It works, but I’m wondering if you’ve come across a better way".

I have an answer to this question but I cannot guarantee its performance however, I recommend this PL/SQL function to do it.

create or replace
function  GetParseXMLtoken( XMLString in varchar2  ,   Token_TO_Search in varchar2)  return varchar2 is
l_XMLConverted         xmltype := xmltype(XMLString);
l_rollno                         number;
l_result                         clob;
begin
   select extractvalue(l_XMLConverted,Token_TO_Search
         into l_result
   from dual;
  dbms_output.put_line(' Tag1:['||l_result||']');
return l_result;
end;

Let's test this code

select GetParseXMLtoken(
                        '' ||
                          'Jorge Rios'||
                          ' Me '||
                          'XML email test '||
                          'This is an example of email Body'||
                        '
','/Message/Body') from dual;


The result should be:



Enjoy it!.

No comments:

Post a Comment