Mass copy pictures across Exact Online articles

Wiki (en)
,
1

This topic explains how to mass copy pictures between Exact Online articles. It is related to the topic on exporting the pictures associated with Exact Online articles to individual files on disk, but this code stores the pictures with another article.

Each article has one main picture, displayed on the Articles page, for example:

Exact Online article with picture

Also, you can associate multiple pictures as documents. Just specify the related article as “Item” on the document.

The Exact Online REST has no known method to import pictures for articles. The Exact Online REST APIs for articles (Items, ItemsBulk and ItemsIncremental) display (read-only) thumbnail information such as the URL on Exact Online, the file name, and the content type. The Exact Online XML API offers the XML <Image> tag to attach an image to an article.

The following code performs the following steps:

  • Select a range of source articles (called Items on the Exact Online API) with a picture.
  • Determine to which target articles without a picture which should display a picture.
  • Retrieve the images using the ThumbnailByUrl table function.
  • Construct a large XML upload for all articles to be updated.
  • Upload the pictures in one large upload using UploadXMLTopics.
  • UploadXMLTopics where necessary fragments the payload to stay within Exact Online API rate and size limits.

The typical runtime for uploading small to medium-sized pictures to Exact Online is 5 pictures per second.

--
-- Upload pictures where missing.
--
create or replace table xmlupload@inmemorystorage
as
select 'item-pictures.xml' filename
,      to_char(t.division) division
,      'Items' topic
,      xmlformat
       ( '<?xml version="1.0" encoding="utf-8"?>'
         || '<eExact xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="eExact-XML.xsd">'
	     || '<Items>'
         || t.xml
	     || '</Items>'
         || '</eExact>' 
       )
       filecontents
from   ( select itmorig.division
         ,      listagg
                ( '<Item ID="{' || itmvar.id || '}" code="' || itmvar.code || '" >'
                  || '<Image>'
                  || xmlelement
                     ( 'Name'
                     , itmorig.code
                       || '.'
                       || case 
                          when tnlorig.ImageContentType = 'image/jpeg' 
                          then 'jpg'
                          else 'bin'
                          end 
                     )
                  || '<BinaryData>' || base64_encode(tnlorig.imagecontents) || '</BinaryData>'
                  || '</Image>'
                  || '</Item>'
                , ''
                ) xml
         from   ItemsBulk itmorig
         join   ThumbnailByUrl(itmorig.pictureurl) tnlorig
         join   ItemsBulk itmvar
         on     itmvar.division = itmorig.division
         --
         -- Find associated article.
         --
         and    itmvar.code like itmorig.code || '_%'
         --
         -- Target item has no picture yet.
         --
         and    itmvar.PictureName is null
         --
         -- Source article has a picture.
         --
         where  itmorig.PictureName is not null
         --
         -- Apply some filter to find the source.
         --
         and    itmorig.code = 'MYSOURCEARTICLE'
         group
         by     itmorig.division
       ) t

Next, the large XML is uploaded, automatically split by the embedded logic of UploadXMLTopics in fragments of approximately 1 MB:

insert into UploadXMLTopics
( topic
, payload
, division_code
, orig_system_reference
, fragment_payload_flag
, fragment_max_size_characters
, fail_on_error
)
select topic
,      filecontents
,      division
,      filename
--
-- Try to split into pieces of 1 MB at most.
--
,      true fragment_payload_flag
,      1000000 fragment_max_size_characters 
,      true fail_on_error
from   xmlupload@inmemorystorage
order 
by     division
,      filename

At the end we check the outcome (only necessary when fail_on_error is false):

--
-- Check everything was loaded correctly in Exact Online.
-- There may be NO row with success = false.
--
select *
from   UploadXMLTopicFragments