Translate X12 EDI documents into Database Entries

Arc connects your back-office systems with EDI. The following sample project demonstrates how different ports in Arc can be used to automatically insert incoming EDI purchase orders from a trading partner (WayneTech) into a database. The sample will also save to and poll a database table for new invoices and convert new entries into X12 documents.

Drop in the Sample Project

To get started with the sample project, download and install the Arc application (free 30-day trial). Then download the sample project and extract and copy the contents to the "data" folder in the installation location for Arc. For Windows installations, this will likely be C:\Program Files\CData\Arc. Please consult the documentation for the Java build of Arc for Linux/Unix/Mac installations.

Translate an X12 Purchase Order to an XML Purchase Order

The X12_WayneTech_IN Connector is the entry point into the process flow. This is an X12 translation connector, used to convert incoming X12 documents from a trading partner into XML, which can be manipulated further.

You can manage ports by selecting the connector in the menu on the Ports tab. On the Send tab for this connector, you will see an 850 purchase order document received from WayneTech and ready for processing.

When you select this document in the table and click Send, the connector translates the purchase order into XML. The XML generated by the X12_WayneTech_IN Connector conforms to the standards of the X12 document being parsed, with the elements in the resulting XML containing names that correspond directly to the segment names and Ids from the X12 specification. These fields can be easily extracted through XPath in a script connector.

Translate an XML Purchase Order to an XML Sales Order/Invoice

When you select the MAP_850_IN Connector, you will see the XML purchase order ready to be processed into a sales order and invoice on the Send tab. This script connector maps the XML that was received from the X12_WayneTech_IN Connector and transforms it into an XML invoice whose fields the database that we will be communicating with can accept.

This is done with a simple script that parses specific XML elements from the XML input document, where the XPaths are based on the values in the X12 standard, and then renders those values and XML fields directly into the resulting XML file.

Save to and Poll the Database

The SQLite Database Connector establishes an SQLite database connection to an SQLite database embedded in the demo. While any ADO.NET or ODBC driver can be used in Arc, major databases such as PostgreSQL, MySQL, and SQLite are supported out of the box.

On the Settings tab, you can configure the database connection and create input and output templates. Input templates allow for the insertion of records into the database; Output templates extract new transactions from the database. The templates used here can be designed with a wizard that automatically connects to the database and determines what tables and rows are available for insertion into and retrieval from the database.

On the Send tab for the SQLite Connector, you will see the invoice in XML format. When you select the XML invoice and click Send, Arc will write the invoice and a sales order to the database.

The SQLite Connector also queries the Invoice table on a polling interval and places the Invoice data retrieved, in XML format, in the MAP_810_OUT Connector.

Translate an XML Invoice into an XML 810 Document

The MAP_810_OUT Script Connector maps and transforms the outbound Invoice documents retrieved from the SQLite Connector into XML based on the X12 standard that we will use for translation. The same technique from step 3 is used here - the elements we are looking for are parsed from the input XML and rendered into the values and XML fields directly in the resulting XML file.

When you select the XML invoice on the Send tab for the connector and click Send, Arc translates the fields based on columns in the database table into fields based on an invoice in the X12 format, an 810 document.

Translate an XML 810 Document to an X12 810 Document

The X12_WayneTech_OUT Connector creates outbound X12 documents from the XML structure that we have created earlier. When you select the XML 810 documents on the Send tab and click Send, Arc translates the XML documents into X12 documents ready for transport.

More Information & Next Steps

As you can see, with Arc, it is possible to automate the translation of business data from EDI formats into back-office systems like an operational database. For more information on Arc, visit our home page. Download your own copy of Arc and start automating the movement of your operational data today!



Download & Install Sample

Already running Arc? This sample will setup Arc with the data and connector configurations referenced in this article: