You should parse XML using an XML Parser and in Oracle you can use XMLQUERY:

SELECT XMLQUERY(
         '/root/entry/string[1][text()="NAME2"]/../string[2]/text()'
         PASSING XMLTYPE( xml, NLS_CHARSET_ID('UTF8') )
         RETURNING CONTENT
       ) AS value
FROM   table_name;

Or XMLTABLE:

SELECT value
FROM   table_name
       CROSS APPLY XMLTABLE(
         '/root/entry'
         PASSING XMLTYPE( xml, NLS_CHARSET_ID('UTF8') )
         COLUMNS
           name  VARCHAR2(20) PATH './string[1]',
           value VARCHAR2(20) PATH './string[2]'
       )
WHERE  name = 'NAME2';

Which for the sample data:

CREATE TABLE table_name ( xml BLOB );

DECLARE
  value        CLOB := '<root>
  <entry>
    <string>NAME1</string>
    <string>VALUE1</string>
  </entry>
  <entry>
    <string>NAME2</string>
    <string>VALUE2</string>
  </entry>
  <entry>
    <string>NAME3</string>
    <string>VALUE3</string>
  </entry>
  <entry>
    <string>NAME4</string>
    <string>VALUE4</string>
  </entry>
</root>';

  dest_offset  INTEGER := 1;
  src_offset   INTEGER := 1;
  lang_context INTEGER := DBMS_LOB.DEFAULT_LANG_CTX;
  result       BLOB;
  warning      INTEGER;
  warning_msg  VARCHAR2(50);
BEGIN
  DBMS_LOB.CreateTemporary(
    lob_loc => result,
    cache   => TRUE
  );

  DBMS_LOB.CONVERTTOBLOB(
    dest_lob     => result,
    src_clob     => value,
    amount       => LENGTH( value ),
    dest_offset  => dest_offset,
    src_offset   => src_offset,
    blob_csid    => DBMS_LOB.DEFAULT_CSID,
    lang_context => lang_context,
    warning      => warning
  );

  INSERT INTO table_name ( xml ) VALUES ( result );
END;
/

Both outputs:

| VALUE  |
| :----- |
| VALUE2 |

Can you do it with a regular expression? Yes:

SELECT REGEXP_SUBSTR(
         TO_CLOB( xml ),
         '<entry>\s*<string>NAME2</string>\s*<string>([^<]*)</string>\s*</entry>',
         1,
         1,
         'c',
         1
       ) AS value
FROM   table_name

Which outputs:

| VALUE  |
| :----- |
| VALUE2 |

db<>fiddle here

However, you shouldn’t as the XML parsing functions take an XPATH which specifies where it should look for the data. The regular expression will just treat the value as a string and look for the first match even if it is not in the expected place in the XML hierarchy.

For example, if your data is:

<root>
  <entry>
    <string>NAME1</string>
    <string>VALUE1</string>
    <other><entry><string>NAME2</string><string>NOT THIS</string></entry></other>
  </entry>
  <entry>
    <string>NAME2</string>
    <string>VALUE2</string>
  </entry>
</root>

Then XMLQUERY and XMLTABLE will find the correct value but the regular expression outputs:

| VALUE    |
| :------- |
| NOT THIS |

db<>fiddle here

Or, if your data suddenly has an attribute:

<root>
  <entry>
    <string>NAME1</string>
    <string>VALUE1</string>
  </entry>
  <entry>
    <string>NAME2</string>
    <string attr="attr value">VALUE2</string>
  </entry>
</root>

Then parsing with the regular expression will fail and return NULL.

db<>fiddle here

So, don’t use a regular expression, use a proper XML parser.

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top