Oracle, regexp_substr - how to match 'string'+regex

Question

I have a column that gives me lines like this:

<?xml version="1.0" encoding="UTF-8"?>
<xSettings>
    <systemPropertyName>BLALBLA</systemPropertyName>
    <minimumAmount>198.00</minimumAmount>
    <closingAmount>198.00</closingAmount>
    <useThisSetting>true</useThisSetting>
    <SystemStep dayAfterPrevious="0">
        <System SystemService="1" minimumAmount="450.00" />
    </SystemStep>
    <SystemStep dayAfterPrevious="8">
        <message />
    </SystemStep>
    <SystemStep dayAfterPrevious="3">
        <block />
    </SystemStep>
    <SystemStep dayAfterPrevious="1">
        <message />
    </SystemStep>
    <SystemStep dayAfterPrevious="7">
        <message />
    </SystemStep>
</xSettings>

All numbers in it are variable and the BLA BLA is variable too. What I want is a select that only gives me

<minimumAmount>198.00</minimumAmount>

though this would be eve better:

198.00

basically, I can't figure out how to use regexp_substr to find a specific string , then return a number just after that may be from 1 to 4 digits and has two decimals after.

As MT0 says - don't use regular expressions to extract values from xml strings. It's like driving screws with a hammer; it can be done, but we should use a screwdriver. — user5683823
– user5683823, Commented Jan 6, 2017 at 13:55

MT0 · Accepted Answer · 2017-01-06 12:23:09Z

2

Do not use regular expressions to parse XML data - use a proper XML parser:

SELECT x.minimumAmount
FROM   your_table t,
       XMLTable(
         '/xSettings',
         PASSING XMLType( t.your_column )
         COLUMNS minimumAmount NUMBER(5,2) PATH './minimumAmount'
       ) x

Or

SELECT TO_NUMBER(
         EXTRACTVALUE( XMLType( your_column ), '/xSettings/minimumAmount' )
       ) AS minimumAmount
FROM   your_table

edited Jan 6, 2017 at 12:23

answered Jan 6, 2017 at 12:16

MT0

173k12 gold badges70 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gurwinder Singh · Accepted Answer · 2017-01-06 19:29:07Z

0

XML data should not be queried using regular expressions, but here:

select
  regexp_substr(col, '<minimumAmount>(.*)</minimumAmount>',1,1,null,1) minimum_amount
from your_table;

One other way is to use regexp_replace like this:

select
  regexp_replace(col, '.*<minimumAmount>(.*)</minimumAmount>.*','\1') minimum_amount
from your_table;

edited Jan 6, 2017 at 19:29

answered Jan 6, 2017 at 11:49

Gurwinder Singh

39.7k6 gold badges62 silver badges87 bronze badges

2 Comments

user5683823 Over a year ago

While this will work, MT0 is right: one should not use regexp functions to query xml data.

user5683823 Over a year ago

it seems we'll "see" each other here often, so be warned: I am not always right; actually I am wrong often enough!

Collectives™ on Stack Overflow

Oracle, regexp_substr - how to match 'string'+regex

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related