EDI to XML or EDI to SQL?

    In the EDI processing we need to transform data from the EDI format to the data formats of our applications and back. App to EDI to App - ERD

    What is the best way to do this?

    The most popular transformation is using an intermediate XML format. We use the XML Schema and the XSLT standards to transform one XML format to another XML format. Is it the best way?

    Let’s look at the whole data processing chain:

    EDI to XML to SQL - ERD


    1. EDI to XML transformation;

    2. data transformation (with XML Schema and XSLT);

    3. XML to SQL transformation.

        Now I’m going to step back and look at the EDI document structure a little bit.

        Does the EDI structure resemble the XML structure or the SQL structure?

        Definitely it resembles the SQL. The EDI segments are like the SQL table records. The EDI elements are like the table columns. (Sometimes the elements are compounded from several subelements but it is unimportant now.)

        Does the EDI structure resemble the XML structure? It is not. The XML relations are expressed by nesting structures. For example in XML the Order Detail records are nested inside an Order record. In EDI the Order Detail and Order segments are not nested, the relations are defined the same way as it is in SQL by some correlated IDs in related segments.

        So, EDI structure resembles the SQL structure not the XML structure.

        Also remember our final goal, which is the application data in the SQL format. Can we just bypass the XML format and transform EDI directly to SQL format?

        This is more natural. We throw out two complex EDI to XML and XML to SQL transformations and replace them by single EDI to SQL transformation. Why the EDI to XML and XML to SQL are so complex? Because we have to map the reference relations to the nesting relations or vise versa. It is not simple. There are the whole books teaching us tips and tricks for these transformations.EDI to XML OR XML to SQL - ERD

        Why EDI to SQL transformation is simple? Because it is one to one mapping, a segment to a record and an element to a field, the SQL-like EDI structures to the SQL application structures.

        EDI to SQL - ERD

        The is one problem with this new approach. We have to create a code for the EDI to SQL transformation. It is not a hard problem if we use the contemporary techniques like Linq or Entity Frameworks. Those techniques look competitive even against the adapters implemented in specialized integration systems like the BizTalk Server of Mule ESB. Code is pretty straightforward if we use two step transformation.

        EDI to SQL to SQL - ERD

        1. EDI to SQL transformation;

        2. data transformation (SQL to SQL)

          1. The first step is to transform EDI segments and elements to the SQL records and fields that mirror of the EDI structure (EDI SQL), and the second step is to map those SQL records to the SQL records of our application database (App SQL).

            This sample shows us how we could simplify our solutions, i.e. how to do some real architecture.

          Print | posted on Monday, April 21, 2014 3:30 PM


          No comments posted yet.
          Post A Comment