Regex expression oracle sql 11g to parse XML

Question

I've got a column in a table which contains xml type of data but is in a varchar format. An example raw of data is:

  <old_template_code>BEVA30D</old_template_code><old_template_name>Beva
 30D France calls capped
 15p</old_template_name><new_template_code>BEVA24M</new_template_code><new_template_name>Beva 24M France Calls Capped 15p</new_template_name>

I wonder what regex expression do I have to use to retrieve for example 'BEVA30D' from <old_template_code>?

I've tried

          REGEXP_SUBSTR(table.column,
            '<old_template_code>*</old_template_code>') "REGEXPR_SUBSTR"

but it doesn't work.

Answer 1

Forget about regexp. Use the native XMLType functionality ...

select extractValue(xmlparse(content T.column), '/old_template_code')
from table T;

In an example based on your sample XML data:

with source$ as (
    select q'{
<old_template_code>BEVA30D</old_template_code><old_template_name>Beva
 30D France calls capped
 15p</old_template_name><new_template_code>BEVA24M</new_template_code><new_template_name>Beva 24M France Calls Capped 15p</new_template_name>
}' as source_xml
    from dual
)
select extractValue(xmlparse(content source_xml), '/old_template_code')
from source$;

gives me the value of

BEVA30D

Enjoy.

PS: Moreover, forget about stackoverflow.com, use Oracle documentation. :-)