REGEXP_SUBSTR-来自XML代码的多个匹配项

Question

我从选择(CLOB数据(整个记录代码中得到了这个结果：

<Job jobId="ANAGRAFICHE_MANAGERIAL" release="5.3.18.269"><Node name="Start" jobId="ANAGRAFICHE_MANAGERIAL" id="1" isJobRoot="true" captionOfDesc="{R001145}" xCoord="20" yCoord="20" endNodeIdLinked="5" type="0"/><Node name="ETL_JOB_TABLES" jobId="ANAGRAFICHE_MANAGERIAL" id="2" codActivity="ETL_JOB_DATA" captionOfDesc="{C006939}" xCoord="135" yCoord="1" endNodeIdLinked="5" type="1" desc0="Job ETL per anagrafiche 1" desc1="ETL job for tables 1"><Param name="raccolta" caption="C000264" className="it.grupposervizi.easy.ef.base.Raccolta" isMandatory="true" isArray="false" componentClass="it.grupposervizi.easy.ef.base.Raccolta" inputType="codMultiDesc" value="SEGMENT2022_M" isEnabled="true" hasDisplayValues="false" enableAllValuesVisible="false" isEnabledOnlyAllValues="false" dim="RAC" parameterType="ANA"/><Param name="scenarioPeriodoOriginarioList" caption="C003374" className="[Lit.grupposervizi.easy.ef.base.ScenarioPeriodoOriginario;" isMandatory="true" isArray="true" componentClass="it.grupposervizi.easy.ef.base.ScenarioPeriodoOriginario" inputType="codMultiDesc" isEnabled="true" hasDisplayValues="true" enableAllValuesVisible="true" isEnabledOnlyAllValues="false" dim="SCE_ORIG_PER" parameterType="ANA"/>
<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"/>
</Node><Node name="ETL_JOB_TABLES" jobId="ANAGRAFICHE_MANAGERIAL" id="3" codActivity="ETL_JOB_DATA" captionOfDesc="{C006939}" xCoord="340" yCoord="1" endNodeIdLinked="5" type="1" desc0="Job ETL per anagrafiche 2" desc1="ETL job for tables 2"><Param name="raccolta" caption="C000264" className="it.grupposervizi.easy.ef.base.Raccolta" isMandatory="true" isArray="false" componentClass="it.grupposervizi.easy.ef.base.Raccolta" inputType="codMultiDesc" value="SEGMENT2022_M" isEnabled="true" hasDisplayValues="false" enableAllValuesVisible="false" isEnabledOnlyAllValues="false" dim="RAC" parameterType="ANA"/><Param name="scenarioPeriodoOriginarioList" caption="C003374" className="[Lit.grupposervizi.easy.ef.base.ScenarioPeriodoOriginario;" isMandatory="true" isArray="true" componentClass="it.grupposervizi.easy.ef.base.ScenarioPeriodoOriginario" inputType="codMultiDesc" isEnabled="true" hasDisplayValues="true" enableAllValuesVisible="true" isEnabledOnlyAllValues="false" dim="SCE_ORIG_PER" parameterType="ANA"/>
<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"/>
</Node><Node name="ETL_JOB_TABLES" jobId="ANAGRAFICHE_MANAGERIAL" id="4" codActivity="ETL_JOB_DATA" captionOfDesc="{C006939}" xCoord="545" yCoord="1" endNodeIdLinked="5" type="1" desc0="Job ETL per anagrafiche 3" desc1="ETL job for tables 3"><Param name="raccolta" caption="C000264" className="it.grupposervizi.easy.ef.base.Raccolta" isMandatory="true" isArray="false" componentClass="it.grupposervizi.easy.ef.base.Raccolta" inputType="codMultiDesc" value="SEGMENT2022_M" isEnabled="true" hasDisplayValues="false" enableAllValuesVisible="false" isEnabledOnlyAllValues="false" dim="RAC" parameterType="ANA"/><Param name="scenarioPeriodoOriginarioList" caption="C003374" className="[Lit.grupposervizi.easy.ef.base.ScenarioPeriodoOriginario;" isMandatory="true" isArray="true" componentClass="it.grupposervizi.easy.ef.base.ScenarioPeriodoOriginario" inputType="codMultiDesc" isEnabled="true" hasDisplayValues="true" enableAllValuesVisible="true" isEnabledOnlyAllValues="false" dim="SCE_ORIG_PER" parameterType="ANA"/>
<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"/>
</Node><Node name="End" jobId="ANAGRAFICHE_MANAGERIAL" id="5" captionOfDesc="{R001146}" startNodeIdLinked="1" type="2"/><Edge jobId="ANAGRAFICHE_MANAGERIAL" from="1" to="2"/><Edge jobId="ANAGRAFICHE_MANAGERIAL" from="2" to="3"/><Edge jobId="ANAGRAFICHE_MANAGERIAL" from="3" to="4"/><Edge jobId="ANAGRAFICHE_MANAGERIAL" from="4" to="5"/></Job>

我对REGEXP_SUBSTR的预期结果是：

ANA_AZ_CONS_MA
AZI_PROCESSO_MA
CAMBI_MA

我尝试了这个选择，但我不能得到我期望的结果，有可能得到我之前说的结果吗？还是我在做梦？：

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

感谢

Answer 1

不要使用正则表达式来解析XML。使用合适的解析器：

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';

或：

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

对于样本数据：

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;

两种输出：

VALUE
ANA_AZ_CONS_MA
AZI_PROCESSO_MA
CAMBI_MA