How to use REGEXP_REPLACE

Question

I have string/xml tag like this below. Please see the code below...

<filterCriteriaRow>
  <filterCriteriaItem>
    <attributeName>abcd</attributeName>
    <columnName>SEGMENT3</columnName>
    <operator>EQUALTO</operator>
    <conjunction>OR</conjunction>
    <valueDataType>STRING</valueDataType>
    <value>000000</value>
  </filterCriteriaItem>
  <filterCriteriaItem>
    <attributeName>abcd</attributeName>
    <columnName>SEGMENT3</columnName>
    <operator>BETWEEN</operator>
    <conjunction>OR</conjunction>
    <valueDataType>STRING</valueDataType>
    <value>101001</value>
    <value>503099</value>
  </filterCriteriaItem>
  <filterCriteriaItem>
    <attributeName>abcd</attributeName>
    <columnName>SEGMENT3</columnName>
    <operator>EQUALTO</operator>
    <conjunction>OR</conjunction>
    <valueDataType>STRING</valueDataType>
    <value>000000</value>
  </filterCriteriaItem>
  <filterCriteriaItem>
    <attributeName>abcd</attributeName>
    <columnName>SEGMENT3</columnName>
    <operator>BETWEEN</operator>
    <conjunction>OR</conjunction>
    <valueDataType>STRING</valueDataType>
    <value>101001</value>
    <value>503099</value>
  </filterCriteriaItem>
  <conjunction>AND</conjunction>
</filterCriteriaRow>

I want to manipulate that string to become like this:

<filterCriteriaRow>
  <filterCriteriaItem>
    <attributeName>abcd</attributeName>
    <columnName>SEGMENT3</columnName>
    <operator>EQUALTO</operator>
    <conjunction>OR</conjunction>
    <valueDataType>STRING</valueDataType>
    <value>000000</value>
  </filterCriteriaItem>
  <filterCriteriaItem>
    <attributeName>abcd</attributeName>
    <columnName>SEGMENT3</columnName>
    <operator>BETWEEN</operator>
    <conjunction>OR</conjunction>
    <valueDataType>STRING</valueDataType>
    <value>101001</value>
    <value2>503099</value2>
  </filterCriteriaItem>
  <filterCriteriaItem>
    <attributeName>abcd</attributeName>
    <columnName>SEGMENT3</columnName>
    <operator>EQUALTO</operator>
    <conjunction>OR</conjunction>
    <valueDataType>STRING</valueDataType>
    <value>000000</value>
  </filterCriteriaItem>
  <filterCriteriaItem>
    <attributeName>abcd</attributeName>
    <columnName>SEGMENT3</columnName>
    <operator>BETWEEN</operator>
    <conjunction>OR</conjunction>
    <valueDataType>STRING</valueDataType>
    <value>101001</value>
    <value2>503099</value2>
  </filterCriteriaItem>
  <conjunction>AND</conjunction>
</filterCriteriaRow>

As you can see there is a double tag column "value" in one parent tag I want to create first value tag become "value" but second value tag become "value2"

How to manipulate that string using regexp_replace?

Please help

'value' tag will always be inside the 'filterCriteriaItem' tag ? Also can it have multiple occurences(i mean more than 2) ? — Aritro Sen, Feb 08 '19 at 11:29
Yes, value tag always in filterCriteriaItem tag and multiple occurences of value tag max 2 only @AritroSen — Thery, Feb 08 '19 at 11:31
Don't know about PLSQL but this can be done using native language(Java/C#/Python) libraries. Basically you have to iterate over each 'filterCriteriaItem' and find the 2nd occurance of 'value' tag and edit it accordingly. — Aritro Sen, Feb 08 '19 at 11:47
Yes, i have thought about that using iterate. Maybe there are simple ways to achieve this. Thanks in advanced @AritroSen — Thery, Feb 08 '19 at 11:52
[Obligatory link](https://stackoverflow.com/a/1732454/62576) about futility of trying to parse [X]HTML with regular expressions instead of using a DOM parser. — Ken White, Feb 08 '19 at 13:51

score 0 · Answer 1 · answered Feb 08 '19 at 15:31

There are a number of XML options in Oracle, many of which are deprecated in recent versions.

One current option would be to use xquery update, the following examples assume your XML is in a table stored as XMLTYPE.

Assuming two 'value' nodes per 'filterCriteriaItem'...

SELECT XMLQUERY (
          'copy $copy := . 
           modify (
             for $fci in $copy/filterCriteriaRow/filterCriteriaItem/value[2]
                return rename node $fci as "value2")
           return $copy'
          PASSING xml_doc 
          RETURNING CONTENT) new_xml_doc
FROM   table_name;

Or perhaps more generally...

SELECT XMLQUERY (
         'copy $copy := . 
          modify (
            for $fci in $copy/filterCriteriaRow/filterCriteriaItem
               for $val at $pos in $fci/value
                  return rename node $val as 
                     fn:concat("value", if ($pos = 1) then "" else $pos))
          return $copy'
         PASSING xml_doc 
         RETURNING CONTENT) new_xml_doc
FROM   table_name;

How to use REGEXP_REPLACE

1 Answers1