CODEWITHSUNDEEP
xmlxpathplsqlxmltable
Alessandro Chiancone
Alessandro Chiancone

Reputation: 13

ORACLE XMLTABLE don't extraxt the value

This XML is present in the u.ack field and I have to extract value from this XPATH: /ORG_O20/ERR/ERR.5/CWE.2

<?xml version="1.0" encoding="ISO-8859-15"?>
<ORG_O20 xmlns="urn:hl7-org:v2xml">
  <MSH>
    <MSH.1>|</MSH.1>
    <MSH.2>^~\&amp;</MSH.2>
    <MSH.3>
      <HD.2>SOLE</HD.2>
    </MSH.3>
    <MSH.7>
      <TS.2>20250110120904</TS.2>
    </MSH.7>
    <MSH.9>
      <MSG.1>ORG</MSG.1>
      <MSG.2>O20</MSG.2>
      <MSG.3>ORG_O20</MSG.3>
    </MSH.9>
    <MSH.10>b682e31e-820a-4735-a0e9-ab4b6de63e41</MSH.10>
    <MSH.11>
      <PT.1>P</PT.1>
    </MSH.11>
    <MSH.12>
      <VID.1>2.5</VID.1>
      <VID.3>
        <CE.1>0.5</CE.1>
      </VID.3>
    </MSH.12>
  </MSH>
  <MSA>
    <MSA.1>AE</MSA.1>
    <MSA.2>EM250000000575520824</MSA.2>
  </MSA>
  <ERR>
    <ERR.4>E</ERR.4>
    <ERR.5>
      <CWE.1>110.PRE0</CWE.1>
      <CWE.2>Prescrizione [nre=080K03362498072] non autorizzata. Risposta SAR:Prestazione Prog=1, DMR=91.30.7, codiceCatSole=4808.001, branca=011 non presente in catalogo.</CWE.2>
      <CWE.3>SAR</CWE.3>
    </ERR.5>
  </ERR>
  <ORG_O20.RESPONSE>
    <ORG_O20.ORDER>
      <ORC/>
    </ORG_O20.ORDER>
  </ORG_O20.RESPONSE>
</ORG_O20>

To extract value I use this SQL code:

select xmltype(u.ack), x.messaggio, x.oggetto
from messaggihl7  u,
    XMLTABLE('/ORG_O20/ERR/ERR.5/CWE.2'
            passing xmltype(u.ack)
            columns messaggio VARCHAR2(4000) PATH 'text()',
                    oggetto XMLTYPE PATH '/ORG_O20/ERR/ERR.5/CWE.2') x
where destinatario like '%Pres%'
    and dataora_ricevuto > to_date ('01/01/2025', 'dd/mm/yyyy') 
    and classe like '%OMG%'
    and ack like '%%non presente%catalogo%%';

The XPATH

The XPATH looks correct to me, but no value is returned! Why? Why? Why?

Upvotes: 1

Views: 41

Answers (1)

Yitzhak Khabinsky
Yitzhak Khabinsky

Reputation: 22293

As @Fravadona pointed out, you need to declare and use a namespace.

A declared DEFAULT namespace, without a prefix, could be omitted from the XPath expressions.

db-fiddle

SQL

with tbl as
(
    select
        XMLType(
        '<?xml version="1.0" encoding="ISO-8859-15"?>
<ORG_O20 xmlns="urn:hl7-org:v2xml">
    <MSH>
        <MSH.1>|</MSH.1>
        <MSH.2>^~\&amp;</MSH.2>
        <MSH.3>
            <HD.2>SOLE</HD.2>
        </MSH.3>
        <MSH.7>
            <TS.2>20250110120904</TS.2>
        </MSH.7>
        <MSH.9>
            <MSG.1>ORG</MSG.1>
            <MSG.2>O20</MSG.2>
            <MSG.3>ORG_O20</MSG.3>
        </MSH.9>
        <MSH.10>b682e31e-820a-4735-a0e9-ab4b6de63e41</MSH.10>
        <MSH.11>
            <PT.1>P</PT.1>
        </MSH.11>
        <MSH.12>
            <VID.1>2.5</VID.1>
            <VID.3>
                <CE.1>0.5</CE.1>
            </VID.3>
        </MSH.12>
    </MSH>
    <MSA>
        <MSA.1>AE</MSA.1>
        <MSA.2>EM250000000575520824</MSA.2>
    </MSA>
    <ERR>
        <ERR.4>E</ERR.4>
        <ERR.5>
            <CWE.1>110.PRE0</CWE.1>
            <CWE.2>Prescrizione [nre=080K03362498072] non autorizzata. Risposta SAR:Prestazione Prog=1, DMR=91.30.7, codiceCatSole=4808.001, branca=011 non presente in catalogo.</CWE.2>
            <CWE.3>SAR</CWE.3>
        </ERR.5>
    </ERR>
    <ORG_O20.RESPONSE>
        <ORG_O20.ORDER>
            <ORC/>
        </ORG_O20.ORDER>
    </ORG_O20.RESPONSE>
</ORG_O20>'
        ) xmldata
    from dual
)
select messaggio
from   tbl,
       xmltable(
         xmlnamespaces(DEFAULT 'urn:hl7-org:v2xml'),
         '/ORG_O20/ERR/ERR.5'
         PASSING tbl.xmldata 
         COLUMNS messaggio VARCHAR2(4000) PATH 'CWE.2');

Output

MESSAGGIO
Prescrizione [nre=080K03362498072] non autorizzata. Risposta SAR:Prestazione Prog=1, DMR=91.30.7, codiceCatSole=4808.001, branca=011 non presente in catalogo.

Upvotes: 2

Related Questions