Upload XML payload into multiple Exact Online companies

Using a query as below, you get output in the XML format required for Exact Online.

These XML messages can be put in many files on disk using:

local export documents in filecontents to "c:\temp\in" filename column filename

And then loaded into Exact Online's current division using:

local eol batch import xml "Accounts" in "c:\temp\in\Accounts" success "c:\temp\success" fail "c:\temp\fail"

This works fine for one division, but not when you load 100 divisions/companies.

How can I directly load data from the SQL into multiple companies/divisions at the same time instead of using an individual local eol batch import xml statement and use DIVISIONCODE statement?

Query sample:

select 'Accounts\010-Accounts.xml' 
       filename
,      stg.fileprefix 
       || chr(13)
       || '<Accounts>' 
       || xml 
       || chr(13)
       || '</Accounts>'
       || stg.filepostfix
       filecontents
from   ( select listagg
                ( chr(13)
                  || '<Account code="'
                  || xmlencode(custsupid)
                  || '" status="'
...

Solution

You can upload into multiple companies/divisions with one statement using the following syntax as described on UploadXMLTopics:

insert into UploadXMLTopics@eol
( topic
, payload
, division_code
)
select topic
,      filecontents
,      division_code
from   ...

Even when one or more of the uploads fail, the insert will not report an error and process all XML messages.

You can see the results afterwards using:

select *
from   UploadXMLTopics

The column SUCCESSFUL indicates whether the payload column was loaded succesfully.

In RESULT column you will find the return message of Exact Online, such as:

<?xml version="1.0" encoding="utf-8"?>
<eExact xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="eExact-XML.xsd">
    <Messages>
        <Message type="0">
            <Topic code="FinYears" node="FinYear" />
            <Date>2016-12-09T09:02:36</Date>
            <Description>Kan boekjaar niet aanmaken: 2014 vanwege ontbrekende boekjaren. Het laagste boekjaar in Exact Online is 2016.</Description>
        </Message>
        <Message type="2">
            <Topic code="FinYears" node="FinYear">
                <Data key="2015" keyAlt="2015" />
            </Topic>
            <Date>2016-12-09T09:02:37</Date>
            <Description>Aangemaakt</Description>
        </Message>
    </Messages>
</eExact>