When I execute the query as shown the attachment is always empty. I would expect that the contents of the attachment with name AttachmentFileName would be in the column Attachment.
How do I retrieve the blob of the file?
select /*+ join_set(mat, mailmessageid, 5000) */ mat.Attachment xmlfilecontents
, mat.division || '-' || AttachmentFileName filename
from mailmessagesreceived mre
join MailMessageAttachments mat
on mat.mailmessageid = mre.id
and mat.Type in ( 20, 22 ) /* 20: UBL 2.0, 22: Simplerinvoicing 1.0 */
where mre.SenderMailbox = 'Facturen@ExactOnline.nl'
and mre.created >= add_months(trunc(sysdate, -1), -10)
and mat.FileSize > 0
The column Attachment stores the attachment directly associated with the received Exact Online MailMessage, as long as the mail message hasn't been processed. In that case, the attachment is forwarded to Documents.
However, the Exact Online invoices itself are retrieved indirectly. The Internet location is in the field Url. It is also available in the table using the column AttachmentFromUrl.