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>^~\&</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?
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.
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>^~\&</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. |