DB XML Export

The DB XML Export process produces a transaction set which can be viewed in the UI or saved as an external XML file.

There are a number of ways to specify the data to be exported:

Specifying a SQL Query
Migration entity

For all methods described below there is an option to specify whether the so-called “foot print” columns are included in the export. The foot print columns are the INSERT_DATE, INSERT_USER, UPDATE_DATE, and UPDATE_USER. The values for these columns would be updated for any subsequently imported data by way of INSERT/UPDATE triggers and so their presence in exported data is largely informational.

By default, LOB data (BLOB and CLOB) is exported as a ‘base 64’ encoded string attribute value. Working with the CLOB data, for example, would require decoding the string to obtain the original character text, editing the text and then encoding again to a ‘base 64’ string.

To make this process easier, and as a way to avoid exceeding any XML attribute length limit, it is possible to export LOB data as separate elements. For example, if parent_col2 is a CLOB column and parent_col3 is a BLOB column:

<?xml version="1.0" encoding="UTF-8"?>
<xml2sql Version=”20B”>
   <TRANSACTION_SET useLOBElement="true">
      <"parent table name" parent_col1="value_1" parent_col2="CLOBID#1" parent_col3="BLOBID#1" ..etc..>
         <"child table name" child_col1="value_1" ..etc..>
            ...etc...
         </"child table name">
         <CLOB ID="CLOBID#1">
            <![CDATA[
               ...Both xml and non-xml text appears here...
            ]]>
         </CLOB>
         <BLOB ID="BLOBID#1">
            ...base 64 encoded binary value appears here...
         </BLOB>
      </"parent table name">
   </TRANSACTION_SET>
 </xml2sql>

The attribute value for the LOB column will then contain a unique ID for the element containing the CLOB or BLOB content. Note that BLOB content must always be a ‘base 64’ encoded string. The “useLOBElement” is generated on export and required on import for DB.XML files that contain the above format.

Using an Object Set Name

The Object Set Name is a named list of Object Names (described above). This allows a logical grouping of data to be exported in one file. The base install will provide the following pre-configured queries:

Object Set Names

Object Set Name	Associated Object Names
DomainReferenceData	STATUS_TYPE, WORKFLOW_TOPIC_INFO, NOTIFY_SUBJECT_CONTACT, PLANNING_PARAMETER, BN_RULE, NOTIFY_SUBJECT_STYLESHEET

New customer-defined object set names and object lists can be added by setting the associated properties. For example, the above set is defined by the property:

glog.integration.dbxml.objectset.DomainReferenceData=STATUS_TYPE,WORKFLOW_TOPIC_INFO,NOTIFY_SUBJECT_CONTACT,PLANNING_PARAMETER,BN_RULE,NOTIFY_SUBJECT_STYLESHEET

The use of a "where clause" is mandatory for the export by Object Set.

Specifying a SQL Query

The complete SQL query can also be specified directly. When this approach is used there must be an additional "Root Name" parameter given. This is used as the name of the top level parent element name for each record retrieved by the query.

Migration Entity

The Migration Entity names are a list of all the application objects available in Transportation and Global Trade Management Cloud. They are designed to support the export of a top level object, e.g. Location and all its child objects, e.g. LocationStatus, LocationRefnum, etc. However, the list also contains the respective child entity names to support fine grained export.

The specific entities are retrieved based on a comma separated list of unique object primary keys for the entity name selected. For example, if Location were the selected entity the Object ID would be a comma separated list of Location Primary Key strings.