REGEXP_SUBSTR - multiple matches from XML code-CodePudding

i have this result from a select (CLOB data):

<Param name="job" caption="C003886" className="it.grupposervizi.easy.ef.base.etl.Job" isMandatory="true" isArray="false" componentClass="it.grupposervizi.easy.ef.base.etl.Job" inputType="codMultiDesc" value="**ANA_AZ_CONS_MA**" isEnabled="true" hasDisplayValues="true" enableAllValuesVisible="false" isEnabledOnlyAllValues="false" dim="JOB"/>
<Param name="job" caption="C003886" className="it.grupposervizi.easy.ef.base.etl.Job" isMandatory="true" isArray="false" componentClass="it.grupposervizi.easy.ef.base.etl.Job" inputType="codMultiDesc" value="**AZI_PROCESSO_MA**" isEnabled="true" hasDisplayValues="true" enableAllValuesVisible="false" isEnabledOnlyAllValues="false" dim="JOB"/>
<Param name="job" caption="C003886" className="it.grupposervizi.easy.ef.base.etl.Job" isMandatory="true" isArray="false" componentClass="it.grupposervizi.easy.ef.base.etl.Job" inputType="codMultiDesc" value="**CAMBI_MA**" isEnabled="true" hasDisplayValues="true" enableAllValuesVisible="false" isEnabledOnlyAllValues="false" dim="JOB"/>

the result i expect with REGEXP_SUBSTR is:

ANA_AZ_CONS_MA
AZI_PROCESSO_MA
CAMBI_MA

I try with this select but i can't get the result i expect, is it possible to get the result as i said before or am i dreaming?:

SELECT REGEXP_SUBSTR (JOB_GRAPH, 'value="([^\"] )') "REGEXPR_SUBSTR" FROM JOB_STRUCTURE WHERE JOB_ID = 'ANAGRAFICHE_MANAGERIAL'

Thanks

CodePudding user response：

Do not use regular expressions to parse XML. Use a proper parser:

SELECT x.value
FROM   job_structure j
       CROSS JOIN XMLTABLE(
         '/Param'
         PASSING XMLTYPE( j.job_graph )
         COLUMNS
           value VARCHAR2(50) PATH '@value'
       ) x
WHERE  job_id = 'ANAGRAFICHE_MANAGERIAL';

or:

SELECT XMLQUERY('/Param/@value' PASSING XMLTYPE(job_graph) RETURNING CONTENT)
         AS value
FROM   job_structure
WHERE  job_id = 'ANAGRAFICHE_MANAGERIAL';

Which, for the sample data:

CREATE TABLE JOB_STRUCTURE (
  job_id    VARCHAR2(50),
  job_graph CLOB
);

INSERT INTO job_structure (job_id, job_graph)
  SELECT 'ANAGRAFICHE_MANAGERIAL', '<Param name="job" caption="C003886" className="it.grupposervizi.easy.ef.base.etl.Job" isMandatory="true" isArray="false" componentClass="it.grupposervizi.easy.ef.base.etl.Job" inputType="codMultiDesc" value="ANA_AZ_CONS_MA" isEnabled="true" hasDisplayValues="true" enableAllValuesVisible="false" isEnabledOnlyAllValues="false" dim="JOB"/>' FROM DUAL UNION ALL
  SELECT 'ANAGRAFICHE_MANAGERIAL', '<Param name="job" caption="C003886" className="it.grupposervizi.easy.ef.base.etl.Job" isMandatory="true" isArray="false" componentClass="it.grupposervizi.easy.ef.base.etl.Job" inputType="codMultiDesc" value="AZI_PROCESSO_MA" isEnabled="true" hasDisplayValues="true" enableAllValuesVisible="false" isEnabledOnlyAllValues="false" dim="JOB"/>' FROM DUAL UNION ALL
  SELECT 'ANAGRAFICHE_MANAGERIAL', '<Param name="job" caption="C003886" className="it.grupposervizi.easy.ef.base.etl.Job" isMandatory="true" isArray="false" componentClass="it.grupposervizi.easy.ef.base.etl.Job" inputType="codMultiDesc" value="CAMBI_MA" isEnabled="true" hasDisplayValues="true" enableAllValuesVisible="false" isEnabledOnlyAllValues="false" dim="JOB"/>' FROM DUAL;

Both output:

VALUE
ANA_AZ_CONS_MA
AZI_PROCESSO_MA
CAMBI_MA

fiddle

CodePudding user response：

You need to use

REGEXP_SUBSTR(JOB_GRAPH, 'value="([^"] )', 1, 1, 'i', 1)

See an online fiddle:

SELECT REGEXP_SUBSTR(
 'Param name="job" caption="C003886" className="it.grupposervizi.easy.ef.base.etl.Job" isMandatory="true" isArray="false" componentClass="it.grupposervizi.easy.ef.base.etl.Job" inputType="codMultiDesc" value="ANA_AZ_CONS_MA" isEnabled="true" hasDisplayValues="true" enableAllValuesVisible="false" isEnabledOnlyAllValues="false" dim="JOB"',
 'value="([^"] )', 1, 1, 'i', 1) AS Result FROM dual

See how REGEXP_SUBSTR reference, the last 1 argument means you need to get the captured value, not the whole match value.