I have an XML document stored as a clob. Some, but not all have repeating elements for at least one section. Using 11.2.0.4 EE.
with t (id, msg) as (
select 1, '<AuditMessage xmlns="urn:ca:myco:cplinker:model">
<ParticipantObjectIdentification ParticipantObjectID="PLS_ADMIN">
<ParticipantObjectIDTypeCode code="SYSTEM" codeSystem="thesys"/>
<ParticipantObjectDetail type="OPERATION">patient-search-by-mrn</ParticipantObjectDetail>
<ParticipantObjectDetail type="PARAMS">:empi-id:958885, mrnAuthority:1234, mrn:testlinkera</ParticipantObjectDetail>
<ParticipantObjectDetail type="MRN">testlinkera</ParticipantObjectDetail>
<ParticipantObjectDetail type="MRNAUTHORITY">1234</ParticipantObjectDetail>
<ParticipantObjectDetail type="MRN_AUTH">N/A</ParticipantObjectDetail>
<ParticipantObjectDetail type="MRN">N/A</ParticipantObjectDetail>
<ParticipantObjectDetail type="HEALTH_CARD">N/A</ParticipantObjectDetail>
<ParticipantObjectDetail type="EMPI_ID">958885</ParticipantObjectDetail>
</ParticipantObjectIdentification>
</AuditMessage>' from dual)
select t2.*
from t,
xmltable(xmlnamespaces(default 'urn:ca:myco:cplinker:model'),
'/AuditMessage/ParticipantObjectIdentification'
passing xmltype(t.msg)
columns object_id varchar2(255) path '@ParticipantObjectID',
type_code varchar2(255) path 'ParticipantObjectIDTypeCode/@code',
old_empi_id varchar2(255) path 'ParticipantObjectDetail[@type="OLD_EMPI_ID"]',
empi_id varchar2(255) path 'ParticipantObjectDetail[@type="EMPI_ID"]',
first_name varchar2(255) path 'ParticipantObjectDetail[@type="FIRST_NAME"]',
last_name varchar2(255) path 'ParticipantObjectDetail[@type="LAST_NAME"]',
mrn_auth varchar2(255) path 'ParticipantObjectDetail[@type="MRN_AUTH"]',
mrn_auth_alt1 varchar2(255) path 'ParticipantObjectDetail[@type="MRN_AUTHORITY"]',
mrn_auth_alt2 varchar2(255) path 'ParticipantObjectDetail[@type="SITE"]',
mrn varchar2(255) path 'ParticipantObjectDetail[@type="MRN"]',
mrn_alt1 varchar2(255) path 'ParticipantObjectDetail[@type="SITEID"]',
hcn varchar2(255) path 'ParticipantObjectDetail[@type="HEALTH_CARD"]',
hcn_alt1 varchar2(255) path 'ParticipantObjectDetail[@type="SECONDARYID1"]',
hcn_alt2 varchar2(255) path 'ParticipantObjectDetail[@type="HCN"t2;
This generates ORA-19279: XPTY0004 - XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence. If I remove on of the type="MRN" lines from the XML document it works. Unfortunately, the (sort of in-house) framework that is generating the audit data is somewhat opaque (although our devs are looking into it) so it is not currently possible to change the structure and I have a couple of million existing rows which have a similar problem.
Potentially adding to the issue is the fact that, at the moment, we do not know whether there are cases where elements other than MRN are duplicated.