CODEWITHSUNDEEP
xmlregexoracle-databaseoracle11g
george
george

Reputation: 3221

Regex expression oracle sql 11g to parse XML

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.

Upvotes: 2

Views: 5237

Answers (2)

Maheswaran Ravisankar
Maheswaran Ravisankar

Reputation: 17920

XML approach is definitely the better one always.

Still, A regular expression approach.

select regexp_replace(regexp_substr(table.column,'<old_template_code>.*</old_template_code>'),
                      '(<old_template_code>)(.*)(</old_template_code>)',
                       '\2')
from table;

Upvotes: 4

peter.hrasko.sk
peter.hrasko.sk

Reputation: 4141

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. :-)

Upvotes: 5

Related Questions