[SQL] ORACLE SQL - Extract Text From Between Two Points in a String of Text In a Column?

I'm wondering if someone maybe able to help me out with an ORACLE SQL query issue i'm currently facing,

I have an ORACLE SQL database which contains thousands of rows and multiple columns, one of the columns contains an XML message in text format put there via an internal process.

Below is a single row extract from said column containing the XML string:

<XXXAlertMessage>
<XXXAlertCode>XXX</XXXAlertCode>
<XXXAlert>
<XXXaaaXXXaaaXXX>
<XXXXXXaaaXXXaaaXXX>
<XXXID>XX-XX-XX-XX-XX-XX-XX-XX</XXXID>
<XXXType>GXXXXXE</XXXType>
<MPxNs>
<ImportMPxN>7432921709</ImportMPxN>
</MPxNs>
</XXXXXXaaaXXXaaaXXX>
</XXXaaaXXXaaaXXX>
</XXXAlert>
</XXXAlertMessage>


I am trying to update my ORACLE SQL query to extract from the XML column, just the value between the two <ImportMPxN> tags, the value is a different length in each row on the database, so looking for the first <ImportMPxN> and then counting / extracting a set number of characters to the right, unfortunately doesn't work for this (

The table is called "Transaction" and the column is called "XML",

My basic ORACLE SQL query currently is:

Select XML from Transaction;

I have tried a number of options however currently I am at a loss for a solution ...

I've done some googling and found the following thread:

Oracle SQL: Extracting text between two characters

However I've tried a few things which are suggested but I'm still drawing a blank ...

I've also tried:

with test_data (raw_text) as (select xml from transaction) select regexp_substr(raw_text,'<ImportMPxN>([^]+)<ImportMPxN>',1,1,null,1) as xml from test_data;

The query runs ok, however it returns an empty column (no results) (

I'm kind of at a loss now and require some help, advise or assistance from the good people on Stack Overflow to hopefully help me in my quest )

Many thanks in advance to anyone who can help,

Really much appreciated

Regards

TheSurfCrusader

Continue reading...