ORA-19279: XQuery dynamic type mismatch: expected singleton sequence

[richardkb]

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
XMLINV   6508087 tim@aaa.co.uk dddss.csv  Success
XMLINV   6508087 tim@aaa.co.uk eeess.csv  Success
XMLINV   6508087 rrd@bbb.co.uk zzzzss.csv Success
XMLINV   6508087 rrd@bbb.co.uk yyyyss.csv Success

SELECT *
FROM XMLTABLE ('/xxlcc_email_extractor_output'
PASSING XMLTYPE(
'<xxlcc_email_extractor_output>
<batch_id>XMLINV</batch_id>
<run_id>6508087</run_id>
<date_run>07/07/2010 09:25</date_run>
<email>
<from>tim@aaa.co.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>rrd@bbb.co.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

 

[tsuji]

[0] If the problem is approached within the xmldb sql/xml with only one xmltype table, I fear it would be very complicated. I can vision the solution is to build one building the xmltype table, and then self join a xmlsequence constructed from the said table before doing the select using sql/xml operations, like extractvalue() etc. It is an long-long query.

[0.1] I would conjecture using a transform() then sql/xml operations on the output would be more concise. Similarly, using xquery on repository would be more concise as well.

[0.2] I would also suggest if the set up is having two tables, one parent table with a primary id key and columns of batch_id, run_id and date_run, and a second table with the id as foreign key together with email as xmltype column. That would be more manageable in the join and sql/xml operation, rather than all in one single xmltype table!

[1] I can suggest however xquery on the serialized xml document (suppose it is serialized to a file "xxlcc.xml") to produce either a csv or an xml output for the purpose.

[1.1] Produce a csv resultset.
[tt]
let $sfile:="xxlcc.xml"
let $doc:=doc($sfile)
for $n in $doc/xxlcc_email_extractor_output/email/attachment
return
($n/../../batch_id/text(),',', $n/../../run_id/text(),',', $n/../from/text(),',', $n/filename/text(),',', $n/status/text(),'
')
[/tt]
[1.2] Produce an xml resultset.
[tt]
<result>{
let $sfile:="xxlcc.xml"
let $doc:=doc($sfile)
for $n in $doc/xxlcc_email_extractor_output/email/attachment
return (
<row>{
$n/../../batch_id, $n/../../run_id, $n/../from, $n/filename, $n/status
}</row>
)
}</result>
[/tt]
[2] Maybe or maybe not the above would show the essential or inspire you for an integration into an xmldb xquery approach. For a transform approach, similar xslt can be constructed with the same spirit. But I am not in a position to undertake that part with a single xmltype table!

 

[richardkb]

Hi,

This issue has been resolved by following code similar to below:

SELECT 
            xaii.id
            ,xaii.xml_filename
            ,xaii.xml_load_date
            ,xml_file1.run_id
            ,SUBSTR(xml_file1.date_run,1,10) date_run
            ,SUBSTR(xml_file1.date_run,12,16) time_run
            ,xml_file2.email_from
            ,SUBSTR(xml_file2.date_received,1,10) date_received
            ,SUBSTR(xml_file2.date_received,12,16) time_received
           ,REVERSE(SUBSTR(REVERSE(xml_file3.filename),1,INSTR(REVERSE(xml_file3.filename),'/',1,1)-1)) file_name
            ,xml_file3.status
            ,xml_file3.status_message
  FROM xxlcc_ar_irs_invoices xaii
          , XMLTABLE(
               '//xxlcc_email_extractor_output'
              PASSING xaii.xml_out
              COLUMNS 
              run_id                  VARCHAR(100)  PATH 'run_id'
              ,date_run             VARCHAR(16)    PATH 'date_run'
              ,emails                 XMLTYPE        PATH 'email'
              ) xml_file1
          , XMLTable(
               '/email'
               PASSING xml_file1.emails
              COLUMNS
              email_from        VARCHAR2(100) PATH 'from'
              ,date_received   VARCHAR2(16)   PATH 'date_received'
             ,attachments       XMLTYPE           PATH 'attachment'
             )  xml_file2
          , XMLTable(
               '/attachment'
               PASSING xml_file2.attachments
               COLUMNS
               filename             VARCHAR2(200) PATH 'filename'
              ,status                VARCHAR2(10)   PATH 'status'
              ,status_message VARCHAR2(100)  PATH 'status_message'
              )  xml_file3
WHERE xaii.id = '1'

Thanks

 

[tsuji]

Good stuff. Setting up the xmltable before querying it makes it less bloated and much more manageable rather than getting everything in one hugh select statement, resulting in so much pleasure to read. Maybe you can spare some time in helping others here in this area in the future? Thanks for posting back your solution.