DB XML Servlet

It may be convenient to export and import DB XML data remotely from the Transportation and Global Trade Management Cloud application. This can be achieved in a number of ways; by sending XML messages via HTTP POST to a servlet on the Transportation and Global Trade Management Cloud Web Server (discussed in this section) or as a SOAP message to a Web Service on the Transportation and Global Trade Management Cloud Application Server (discussed in the next section).

The HTTP POST body should use the format defined below and be sent via HTTP POST to the glog.integration.servlet.DBXMLServlet.

The servlet requires authentication using HTTP Basic Authentication. If the network used for communication cannot be assumed to be secure, the HTTPS protocol should be used.

Additionally, the URL command parameter should specify which DBXML command should be executed i.e. xmlImport for import and xmlExport for export. A complete example URL would therefore be:

https://<OTM URL>/GC3/glog.integration.servlet.DBXMLServlet?command=xmlExport

Note: It is necessary for the XML content to be specified as "text/xml". In curl this is done by adding the option: -H "Content-Type:text/xml".

Note: The at-sign “@” prefix on the filename specified with the -d option is required to inform curl that the parameter is a filename containing the XML text to be sent, rather than the literal text itself.

For example,

curl -k -u GUEST.ADMIN:PW -d @i_transmission.db.xml -o response.xml -H "Content-Type:text/xml" https://<OTM URL>/GC3/glog.integration.servlet.DBXMLServlet?command=xmlExport

Export Message Format

Query

The following is the format for the XML message to export XML based on a SQL query:

<?xml version="1.0" encoding="UTF-8"?>
<sql2xml>
   <Query>
      <RootName>{output XML tag name}</RootName>
      <Statement>{SQL select clause}</Statement>
   </Query>
   <FootPrint>{Y|N}</FootPrint>
   <UseLOBElement>{Y|N}</UseLOBElement>
</sql2xml>

For example,

<?xml version="1.0" encoding="UTF-8"?>
<sql2xml>
   <Query>
      <RootName>Location</RootName>
      <Statement>SELECT * FROM LOCATION WHERE LOCATION_GID = ‘GUEST.MY_LOC’</Statement>
   </Query>
</sql2xml>

Note: It is necessary to encode the SQL comparison operators (<, >) with &lt; and &gt;

For example,

<?xml version="1.0" encoding="UTF-8"?>
<sql2xml>
   <Query>
      <RootName>Location</RootName>
      <Statement>SELECT * FROM LOCATION WHERE rownum &lt; 10</Statement>
   </Query>
</sql2xml>

Migration Entity

The following is the format for the XML message to export based on a Migration Entity name:

<?xml version="1.0" encoding="UTF-8"?>
<sql2xml>
   <Entity>
      <Name>{entity name}</Name>
      <PK>{object PK}</PK>
      …more PK elements…
   </Entity>
   <FootPrint>{Y|N}</FootPrint>
   <UseLOBElement>{Y|N}</UseLOBElement>
</sql2xml>

For example,

<?xml version="1.0" encoding="UTF-8"?>
<sql2xml>
   <Entity>
      <Name>Location</Name>
      <PK>GUEST.MY_LOC1</PK>
      <PK>GUEST.MY_LOC2</PK>
   </Entity>
</sql2xml>

The response XML will be the TRANSACTION_SET XML identical to that seen in the UI.

Import Message Format

The root element for the DB XML Import message is xml2sql and will contain the following:

<?xml version="1.0" encoding="UTF-8"?>
<xml2sql Version=”21C”>
   <TransactionCode>{I|II|IU|D|RC}</TransactionCode>
   <SchemaOwner>{schema name}</SchemaOwner>
   <UpdateCache>{Y|N}</UpdateCache>
   <RaiseEvents>{Y|N}</RaiseEvents>
   <ManagedTables>
      <Table>{table name 1}</Table>
      <Table>{table name 2}</Table>
   </ManagedTables>
   <TRANSACTION_SET>
       <…table specific elements…>
       …
   </TRANSACTION_SET>
</xml2sql>

For example,

<?xml version="1.0" encoding="UTF-8"?>
<xml2sql Version=”21C”>
   <TransactionCode>I</TransactionCode>
   <SchemaOwner>GLOGOWNER</SchemaOwner>
   <UpdateCache>Y</UpdateCache>
   <RaiseEvents>Y</RaiseEvents>
   <TRANSACTION_SET>
       <LOCATION LOCATION_GID=’GUEST.MY_LOC’..etc>
          <LOCATION_CORPORATION …etc…/>
          <…etc… other child elements…/>
       </LOCATION>
   </TRANSACTION_SET>
</xml2sql>

The response XML will contain the counts for successful or error transactions.

<xml2sql>
   <SuccessCount>n</SuccessCount>
   <ErrorCount>m</ErrorCount>
   <ElapsedTime>p</ElapsedTime>
   <TimePerTransaction>q</TimePerTransaction>
</xml2sql>

Where n, m, p & q are integers and p & q are the number of milliseconds.