It is possible that XML-tables are lacking fields that actually can have content due to limitations of the XML-documentation (the so-called “XSD” which can also be downloaded through the “Import/Export” menu in Exact Online). Exact has made little changes to extend or update the XML API in terms of documentation and functionality over the last five years. However, this limitation does not in any way restrict the uploading of XML. In fact, the use of insert, update and delete on the XML schema is not recommended if possible at all (there are few tables for which a module has been created).
The recommended way, which also opens all capabilities as described in the XSD, is the use of UploadXMLTopics. The use of UploadXMLTopics as illustrated in Mass copy pictures across Exact Online articles also has exactly the same functionality as the XML file that can be uploaded via the user interface of Exact Online.
Another example can be found below, which is a simplified version of the transaction mass import on https://get-my-report.com
It reads from an Excel file uploaded through a website, but exceltable also accepts local files when run on a device. Next, it transforms the data in the needed XML format for Exact Online. Then it uses UploadXMLTopics to upload the data, whereas the last queries select an overview of the results.
begin
--
-- Load transactions in Exact Online from an uploaded Excel sheet.
--
-- Retrieve transaction lines from Excel source.
--
create or replace table source@inmemorystorage
as
select cast(coalesce(division_code, to_number(:division_code_default)) as int32)
division_code
label "{res:itgen_eol_administration}"
, trim(journal_code)
journal_code
label "{res:itgen_eol_journal_code}"
, entry_number
label "{res:itgen_eol_journal_entry_number}"
, financial_year
label "{res:itgen_eol_financial_year}"
, financial_period
label "{res:itgen_eol_financial_period}"
, trim(description)
description
label "{res:itgen_eol_description}"
, '[GMRIVEIMP:'
|| to_char(cast(coalesce(division_code, to_number(:division_code_default)) as int32))
|| '-'
|| :orig_system_group
|| '-'
|| trim(journal_code)
|| '-'
|| entry_number
-- || '-'
-- Not yet available in exceltable. Needs row_number from ITGEN-3552.
-- || linenumber
|| '] '
|| replace(translate_resources('{res:itgen_gmr_imported_note_par1}'), '{0}', to_char(sysdate))
note
label "{res:itgen_eol_note}"
, entry_date
label "{res:itgen_eol_entry_date}"
, trim(gl_account_code)
gl_account_code
label "{res:itgen_eol_general_ledger_account}"
, trim(cost_center_code)
cost_center_code
label "{res:itgen_eol_cost_center}"
, trim(cost_unit_code)
cost_unit_code
label "{res:itgen_eol_cost_unit}"
, trim(currency_code)
currency_code
label "{res:itgen_eol_currency_code}"
, trim(vat_code)
vat_code
label "{res:itgen_eol_vatcode}"
, trim(account_code)
account_code
label "{res:itgen_eol_account_code}"
, trim(project_code)
project_code
label "{res:itgen_eol_project_code}"
, start_date
label "{res:itgen_eol_start_date}"
, end_date
label "{res:itgen_eol_end_date}"
, amount
label "{res:itgen_eol_amount}"
, null /* Not yet used. */
references
label "{res:itgen_reference}"
, to_char(sysdate, 'YYYYMMDDHH24MISS') file_timestamp
label "{res:itgen_time_stamp}"
from exceltable
( worksheet 'General Ledger Transactions'
passing :upload_file
skip empty rows
skip first 1 rows
columns division_code int64 position 1
, journal_code varchar2 position 2
, entry_number int32 position 3
, financial_year int16 position 4
, financial_period int16 position 5
, description varchar2 position 6
, entry_date date position 7
, gl_account_code varchar2 position 8
, amount number position 9
, cost_center_code varchar2 position 10
, cost_unit_code varchar2 position 11
, currency_code varchar2 position 12
, vat_code varchar2 position 13
, account_code varchar2 position 14
, project_code varchar2 position 15
, start_date date position 16
, end_date date position 17
)
;
set use-http-disk-cache-read@eol false;
set use-http-disk-cache-write@eol false;
set use-http-memory-cache-read@eol false;
set use-http-memory-cache-write@eol false;
--
-- Optional checks removed.
--
--
-- Actual upload.
--
create or replace table sourcelinesxml@inmemorystorage
as
select src.division_code
, src.entry_date
, src.entry_number
, src.journal_code
, src.financial_year
, src.financial_period
, src.journal_code
|| '/'
|| to_char(src.entry_number)
|| ':'
|| to_char(src.financial_year)
|| '-'
|| to_char(src.financial_period)
description
, '<GLTransaction entry="'
|| src.entry_number
|| '">'
|| chr(13)
|| '<Journal code="'
|| src.journal_code
|| '" />'
|| chr(13)
|| '<FinYear number="'
|| src.financial_year
|| '" />'
|| '<FinPeriod number="'
|| src.financial_period
|| '" />'
head
, '<GLTransactionLine>'
|| '<Date>'
|| substr(xmlencode(src.entry_date), 1, 10)
|| '</Date>'
|| '<FinYear number="'
|| src.financial_year
|| '" />'
|| '<FinPeriod number="'
|| src.financial_period
|| '" />'
|| '<GLAccount code="'
|| src.gl_account_code
|| '" />'
|| case
when src.cost_center_code is not null
then '<Costcenter code="'
|| src.cost_center_code
|| '" />'
end
|| case
when src.cost_unit_code is not null
then '<Costunit code="'
|| src.cost_unit_code
|| '" />'
end
|| case
when src.project_code is not null
then '<Project code="'
|| src.project_code
|| '" />'
end
|| case
when src.account_code is not null
then '<Account code="'
|| src.account_code
|| '" />'
end
|| case
when src.description is not null
then xmlelement('Description', src.description, false)
end
|| case
when src.note is not null
then xmlelement('Note', src.note, false)
end
|| case
when src.references is not null
then '<References>'
|| xmlelement('InvoiceNumber', src.references, false)
|| '</References>'
end
|| '<Amount>'
|| xmlelement('Value', src.amount, false)
|| case
when src.currency_code is not null
then '<Currency code="'
|| src.currency_code
|| '" />'
end
|| case
when src.vat_code is not null
then '<VAT code="'
|| src.vat_code
|| '" />'
end
|| '</Amount>'
|| case
when src.start_date is not null
then xmlelement('DeferredFrom', src.start_date, false)
end
|| case
when src.end_date is not null
then xmlelement('DeferredTo', src.end_date, false)
end
|| '</GLTransactionLine>'
line
from source@inmemorystorage src
;
--
create or replace table xmlupload@inmemorystorage
as
select 'GLTransactions' topic
, division_code
, null orig_system_reference
, :orig_system_group orig_system_group
, xmlformat
( '<?xml version="1.0" encoding="utf-8"?>'
|| '<eExact xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="eExact-XML.xsd">'
|| xmlcomment(replace(translate_resources('{res:itgen_gmr_for_company_par1}'), '{0}', division_code))
|| xmlcomment(replace(translate_resources('{res:itgen_gmr_orig_system_group_par1}'), '{0}', :orig_system_group))
|| '<GLTransactions>'
|| xml
|| '</GLTransactions>'
|| '</eExact>'
)
filecontents
from ( select division_code
, listagg
( head
|| lines
|| '</GLTransaction>'
, ''
)
xml
from ( select brl.division_code
, brl.head
, listagg(brl.line, '') lines
from sourcelinesxml@inmemorystorage brl
group
by brl.division_code
, brl.head
)
group
by division_code
)
;
--
-- Upload into Exact Online.
--
insert into UploadXMLTopics@eol
( topic
, payload
, division_code
, orig_system_reference
, orig_system_group
, fragment_payload_flag
, fragment_max_size_characters
, fail_on_error
)
select topic
, filecontents
, to_char(division_code)
, orig_system_reference
, orig_system_group
--
-- Try to split into pieces of 25 KB at most.
--
, true fragment_payload_flag
, 25000 fragment_max_size_characters
--
-- Returns error through user interface, not as a HTTP 500 error.
--
, false fail_on_error
from xmlupload@inmemorystorage
order
by division_code
, orig_system_reference
, orig_system_group
;
end;
--
-- Include input in resulting spreadsheet.
--
select /*+ result_set_name("Source") */
*
from source@inmemorystorage
--
-- Results.
--
select /*+ result_set_name("Results") */
uxt.transaction_id
, uxt.successful
, uxt.division_code
, uxt.orig_system_reference
, uxt.orig_system_group
, uxt.date_started_utc
, uxt.date_ended_utc
, substr(uxt.payload, 1, 32767)
payload_32k
label "{res:itgen_eol_payload}"
, substr(uxt.result, 1, 32767)
result_32k
label "{res:itgen_result}"
, substr(uxt.result_errors, 1, 32767)
result_errors_32k
label "{res:itgen_eol_result_errors}"
, substr(uxt.payload_retry_failed, 1, 32767)
payload_retry_failed
label "{res:itgen_eol_payload_retry_failed}"
from UploadXMLTopicFragments@eol uxt
order
by uxt.transaction_id desc
