<COUNTRYNAME>IN</COUNTRYNAME><LINE1>15 KINGSTON SQURE</LINE1>
<LINE2>57 ATHLLON DRIVE</LINE2>
<LOCALITYNAME>RICHMOND</LOCALITYNAME>
<POSTCODE>2900</POSTCODE>
<STATETERRITORY>LN</STATETERRITORY>
<USAGE>YES</USAGE>
<USAGEPERIOD><END>9999-12-31T00:00:00+11:00</END>
<START>2014-12-10T00:00:00+11:00</START>
</USAGEPERIOD>
</ADDRESSDETAILSREC>
<ADDRESSDETAILSREC>
<COUNTRYNAME>SK</COUNTRYNAME><LINE1>121 PEEL</LINE1>
<LINE2>BRIGHTON</LINE2>
<LOCALITYNAME>RICHMOND</LOCALITYNAME>
<POSTCODE>2322</POSTCODE>
<STATETERRITORY>AN</STATETERRITORY>
<USAGE>NO</USAGE>
<USAGEPERIOD><END>9999-12-31T00:00:00+11:00</END>
<START>2014-12-10T00:00:00+11:00</START>
</USAGEPERIOD>
</ADDRESSDETAILSREC>
</ADDRESSLIST>
</CUSTOMERDETAILSCHANGE>
SELECT y.*
FROM TABLE1 e,
XMLTABLE('/CUSTOMERDETAILSCHANGE' passing XMLType(e.message_data) columns "Country_name" PATH './ADDRESSLIST/ADDRESSDETAILSREC/COUNTRYNAME',
"Line1" PATH './ADDRESSLIST/ADDRESSDETAILSREC/LINE1', "Line2" PATH './ADDRESSLIST/ADDRESSDETAILSREC/LINE2', "Locality_Name" PATH './ADDRESSLIST/ADDRESSDETAILSREC/LOCALITYNAME',
"PostCode" PATH './ADDRESSLIST/ADDRESSDETAILSREC/POSTCODE',
"StateTerritory" PATH './ADDRESSLIST/ADDRESSDETAILSREC/STATETERRITORY', "USAGE" PATH './ADDRESSLIST/ADDRESSDETAILSREC/USAGE') y
WHERE e.id= 121;
This throws error as:
ORA-19279: XPTY0004 - XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence
19279. 00000 - "XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence"
*Cause: The XQuery sequence passed in had more than one item.
*Action: Correct the XQuery expression to return a single item sequence
It works fine for single record but for multiple records getting error. how to resolve this issue.
Thanks,
Ajay