+1-757-461-3022 x124

XML Import to Temporary Tables

When importing XML data from an online source the best practice is to load the file contents as text to a temporary table. There SQL statements and functions can be used to move the data to the desired xTuple tables. This article describes how to create the temporary table for the XML document and setup the xTuple Import Data utility.

The first step is to make sure that the desktop that will run the Import Data utility has a XSLT Processor. This is a program that runs at the command line and interprets the XSLT document. If the desktop is Macintosh the xsltproc program will already be available. If the desktop is running Windows the msxsl program will need to be installed. Follow the steps described at this link to install msxsl.exe.

Create the Table

With the XSLT Processor installed a table will need to be defined that reflects the XML file. Name each column for the XML tags that will have a data value. Below is an example of a segment of a sales order XML file and the related table definition. Then create a schema for the temporary table, in this example it is xtmfg.

Sample XML SQL Create Table Statement

<?xml version="1.0" encoding="utf-8"?>
<SalesTransaction>
<TransactionHeader>
<TransactionHeaderFields>
<WrntyID>37872372</WrntyID>
<Type>Sales Order</Type>
<Status>Submited</Status>
<CreationDateTime>2017-09-03T09:29:54Z</CreationDateTime>
<ModificationDateTime>2017-09-03T09:30:18Z</ModificationDateTime>
<ActionDateTime>2017-09-03T09:30:18Z</ActionDateTime>
<DeliveryDate>2017-09-03</DeliveryDate>
<Remark />
</TransactionHeaderFields> ...

CREATE TABLE xtxml.import
(
import_id serial NOT NULL,
import_loadstatus TEXT DEFAULT 'new',
import_wrntyid TEXT,
import_type TEXT,
import_status TEXT,
import_creationdatetime TEXT,
import_modificationdatetime TEXT,
import_actiondatetime TEXT,
import_deliverydate TEXT,
import_remark TEXT, ...

Create the XSLT

Now you will need to create the XSLT that maps the XML tags to the import table. Using the same XML sample, the XSLT would be as shown in the table below. You can test transforming the XML using online resources like http://xslttest.appspot.com/ to test the XSLT.

Sample XML XSLT

<?xml version="1.0" encoding="utf-8"?>
<SalesTransaction>

<xsl:template match="/">
<SalesTransaction>
<xsl:apply-templates/>
</SalesTransaction>
</xsl:template>

<TransactionHeader>

<TransactionHeaderFields>

<xsl:template match="TransactionHeader">

<xsl:for-each select=".">
<xtxml.import>

<WrntyID>37872372</WrntyID>

<import_wrntyid><xsl:value-of select="TransactionHeaderFields/WrntyID"/</import_wrntyid>

<Type>Sales Order</Type>

<import_type><xsl:value-of select="TransactionHeaderFields/Type"/</import_type>

<Status>Submited</Status>

<import_status><xsl:value-of select="TransactionHeaderFields/Status"/</import_status>

<CreationDateTime>2017-09-03T09:29:54Z</CreationDateTime>

<import_creationdatetime> <xsl:value-of select="TransactionHeaderFields/CreationDateTime"/></import_creationdatetime>

<ModificationDateTime>2017-09-03T09:30:18Z</ModificationDateTime>

<import_modificationdatetime> <xsl:value-of select="TransactionHeaderFields/ModificationDateTime"/> </import_modificationdatetime>

<ActionDateTime>2017-09-03T09:30:18Z</ActionDateTime>

<import_actiondatetime> <xsl:value-of select="TransactionHeaderFields/ActionDateTime"/> </import_actiondatetime>

<DeliveryDate>2017-09-03</DeliveryDate>

<import_deliverydate> <xsl:value-of select="TransactionHeaderFields/DeliveryDate"/> </import_deliverydate>

<Remark />

<import_remark> <xsl:value-of select="TransactionHeaderFields/Remark"/> </import_remark>

Configure Import/Export

Next configure xTuple for importing XML data. Go to System > Setup > Import/Export and complete the setup as shown in the screenshots below.  See the xTuple Reference Guide for a descriptions of the Import/Export configuration screens.

System > Setup > Import/Export > XSLT Settings (Windows)

Note the XML Processor line for using msxsl. Here the XML file is first and the XSLT file is second.

System > Setup > XSLT Settings (Macintosh)

Note the XML Processor line for using xsltproc. Here the XSLT file is first and the XML file is second.

Click New to define the map.

System > Setup > Import/Export > Import Settings

Running Import Data Utility

To run the Import Data utility, go to System > Utilities > Import Data. The screen will list the files available for import. 

Select the available file and click Import Selected or click Import All. The Status for the file will change to DONE if the import was successful.

Follow on Processing

Once the data is in the temporary table additional processing can validate the data, provide an error report, and insert into xTuple tables. This can be accomplished using schedule metasql or creating screens that run SQL or postgres functions. It is recommended to use the API views whenever possible to ensure that xTuple business logic is preserved.

After processing, the data in the temporary table should be deleted or the loadstatus changed from “new” to “imported”. Use the loadstatus field however it best suits the follow on process.

up
80 users have voted.