I'm trying to get each element value from the xml below. I realise the example below is only retrieving one, however this is just an example. When I run this query I'm getting the error as below:
ORA-19279: XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence
I have succesfuly retireved differetn elements, however I've not been successful in retrieving each element in one select statement. Is this possible. The results I'm exspecting is something like:
Batch_id '''''''''''''''''
Run_id ''''
From '''''''''''''''''''''''''''''
Filename '''''''
Status
XMLINV1000120 '' 6508087 '' tim@aaa.co.uk ''''''''' dddss.csv '''''' Success
XMLINV1000120 '' 6508087 '' tim@aaa.co.uk ''''''''' eeess.csv '''''' Success
XMLINV1000120 '' 6508087 '' richard@bbb.co.uk '' zzzzss.csv '' Success
XMLINV1000120 '' 6508087 '' richard@bbb.co.uk '' yyyyss.csv '' Success
SELECT *
FROM XMLTABLE ('/xxlcc_email_extractor_output'
PASSING XMLTYPE(
'<xxlcc_email_extractor_output>
<batch_id>XMLINV1000120</batch_id>
<run_id>6508087</run_id>
<date_run>07/07/2010 09:25</date_run>
<email>
<from>tim@aaa.gov.uk</from>
<date_received>07/07/2010 09:24</date_received>
<attachment>
<filename>/d5/dev9/apps/apps_st/appl/xxlcc/12.0.0/irs_inv/extract/dddss.csv</filename>
<status>Success</status>
<status_message></status_message>
</attachment>
<attachment>
<filename>/d5/dev9/apps/apps_st/appl/xxlcc/12.0.0/irs_inv/extract/eeess.csv</filename>
<status>Success</status>
<status_message></status_message>
</attachment>
</email>
<email>
<from>richard@bbb.gov.uk</from>
<date_received>07/07/2010 09:24</date_received>
<attachment>
<filename>/d5/dev9/apps/apps_st/appl/xxlcc/12.0.0/irs_inv/extract/zzzzss.csv</filename>
<status>Success</status>
<status_message></status_message>
</attachment>
<attachment>
<filename>/d5/dev9/apps/apps_st/appl/xxlcc/12.0.0/irs_inv/extract/yyyyss.csv</filename>
<status>Success</status>
<status_message></status_message>
</attachment>
</email>
</xxlcc_email_extractor_output>')
COLUMNS
from_ VARCHAR2(100) PATH 'email/from'
) MATCH
Thanks