XMLTABLE Query Fails With ORA-19279: XPTY0004 After Upgrade To 19c (Doc ID 2774577.1)

Last updated on JULY 20, 2024

Applies to:

Symptoms

After 19c upgrade getting error ORA-19279

ORA-19279: XPTY0004 - XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence

Query :

SELECT xt.fflp AS fflp,
xt.fflp_crdr AS fflp_crdr
FROM DVP_RTTM.EVENT_MESSAGE_HIST em,
XMLTABLE (
XMLNAMESPACES ('http://dvp.dtcc.com/EventMessage' AS "msg"),
'/msg:message'
PASSING em.event
COLUMNS sec_id VARCHAR2 (9)
PATH 'body/securitySummary/detail/secId',
payment_date VARCHAR2 (32)
PATH 'body/securitySummary/detail/paymentDate',
price VARCHAR2 (32)
PATH 'body/securitySummary/detail/price',
coupon_rate VARCHAR2 (32)
PATH 'body/securitySummary/detail/coupon',
coupon_indicator VARCHAR2 (32)
PATH 'body/securitySummary/detail/coupon/@status',
fndp VARCHAR2 (32)
PATH 'body/securitySummary/detail/lineItems/lineItem[fundsType="FNDP"]/amount',
fndp_crdr VARCHAR2 (6)
PATH 'body/securitySummary/detail/lineItems/lineItem[fundsType="FNDP"]/amount/@type',
fflp VARCHAR2 (32)
PATH 'body/securitySummary/detail/lineItems/lineItem[fundsType="FFLP"]/amount',
fflp_crdr VARCHAR2 (6)
PATH 'body/securitySummary/detail/lineItems/lineItem[fundsType="FFLP"]/amount/@type',
ffmk VARCHAR2 (32)
PATH 'body/securitySummary/detail/lineItems/lineItem[fundsType="FFMK"]/amount',
ffmk_crdr VARCHAR2 (6)
PATH 'body/securitySummary/detail/lineItems/lineItem[fundsType="FFMK"]/amount/@type',
fcpn VARCHAR2 (32)

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.