Data Integration Suite – Process EDI as XML Using MySQL

In the previous topic, Processing XML using MySQL, you learned how you can merge data available in XML documents and MySQL, and how to format a result consistent with a specific XML Schema. But some of our partner companies don’t use XML at all; they submit book orders in EDI format, specifically using one of the EDI messages part of the EANCOM family. Here's what an incoming order message looks like in EDI:

     UNA:+.? '
     UNB+UNOC:4+STYLUSSTUDIO:1+DATADIRECT:1+20051107:1159+6002'
     UNH+SSDD1+ORDERS:D:03B:UN:EAN008'
     BGM+220+BKOD99+9'
     DTM+137:20051107:102'
     NAD+BY+5412345000176::9'
     NAD+SU+4012345000094::9'
     LIN+1+1+0764569104:IB'
     QTY+1:25'
     FTX+AFM+1++XPath 2.0 Programmer?'s Reference'
     LIN+3+1+1861004656:IB'
     QTY+1:16'
     FTX+AFM+1++Java Server Programming'
     LIN+4+1+0596006756:IB'
     QTY+1:10'
     FTX+AFM+1++Enterprise Service Bus'
     UNS+S'
     CNT+2:4'
     UNT+22+SSDD1'
     UNZ+1+6002'

Of course, we would like to handle EDI as easily as we handled the proprietary XML sent to us by other partners. Luckily enough, Data Integration Suite is able to provide access to a wide variety of EDI messages as if they were XML messages. For Data Integration Suite, EDI messages are just yet another XML format for us to handle. For example, the EDI message above will look like this XML fragment from the Data Integration Suite point of view:

    <EDIFACT>
        <UNB>...</UNB>
        <ORDERS>
            <UNH>...</UNH>
            <BGM>...</BGM>
            <DTM>...</DTM>
            <GROUP_2>...</GROUP_2>
            <GROUP_28>
                <LIN>
                    <LIN01-LineItemIdentifier><!--1082-->1</LIN01-LineItemIdentifier>
                    <LIN02-ActionRequestNotificationDescriptionCode><!--1229-->1<!--Added--></LIN02-ActionRequestNotificationDescriptionCode>
                    <LIN03-ItemNumberIdentification>
                        <LIN0301-ItemIdentifier><!--7140-->0764569104</LIN0301-ItemIdentifier>
                        <LIN0302-ItemTypeIdentificationCode><!--7143-->IB<!--ISBN (International Standard Book Number)--></LIN0302-ItemTypeIdentificationCode>
                    </LIN03-ItemNumberIdentification>
                </LIN>
                <QTY>
                    <QTY01-QuantityDetails>
                        <QTY0101-QuantityTypeCodeQualifier><!--6063-->1<!--Discrete quantity--></QTY0101-QuantityTypeCodeQualifier>
                        <QTY0102-Quantity><!--6060-->25<!--Discrete quantity--></QTY0102-Quantity>
                    </QTY01-QuantityDetails>
                </QTY>
                <FTX>
                        <FTX01-TextSubjectCodeQualifier><!--4451-->AFM<!--Title--></FTX01-TextSubjectCodeQualifier>
                        <FTX02-FreeTextFunctionCode><!--4453-->1<!--Text for subsequent use--></FTX02-FreeTextFunctionCode>
                        <FTX04-TextLiteral
                            <FTX0401-FreeText><!--4440->XPath 2.0 Programmer's Reference</FTX0401-FreeText>
                        </FTX04-TextLiteral>
                </FTX>
            </GROUP_28>
        </ORDERS>
        <UNZ>...</UNZ>
    </EDIFACT>

Converting this format in something similar to our canonical XML format is simple using Data Integration Suite:

    <order>
         {
         for $GROUP_28 in doc('converter:EDI:long=yes?file:///c:/ order.edi')/EDIFACT/ORDERS/GROUP_28
         return
        <book>
            <quantity>{$GROUP_28/QTY/QTY01-QuantityDetails/QTY0102-Quantity/text()}</quantity>
            <ISBN>{$GROUP_28/LIN/LIN03-ItemNumberIdentification/LIN0301-ItemIdentifier/text()}</ISBN>
        </book>
         {
    <order>

As in the previous example, Processing XML using MySQL, we are still missing information about title, publisher, and publishing date. We can add that information retrieving it from our MySQL database, where the 'booksxml' table contains details about our books in inventory:

    <order>
         {
         for $GROUP_28 in doc('converter:EDI:long=yes?file:///c:/ order.edi')/EDIFACT/ORDERS/GROUP_28, $booksxml in collection("booksxml")/booksxml
         where $GROUP_28/LIN/LIN03-ItemNumberIdentification/LIN0301-ItemIdentifier = $booksxml/isbn
         return
        <book>
            <title>{$booksxml/title/text()}</title>
            <quantity>{$GROUP_28/QTY/QTY01-QuantityDetails/QTY0102-Quantity/text()}</quantity>
            <ISBN>{$GROUP_28/LIN/LIN03-ItemNumberIdentification/LIN0301-ItemIdentifier/text()}</ISBN>
            <publisher>{$booksxml/manufacturer/text()}</publisher>
            <publishing-date>{$booksxml/releaseDate/text()}</publishing-date>
        </book>
         {
    <order>

This XQuery will return a result consistent with the same canonical XML format described in the previous example. As you can see, this single XQuery is able to merge EDI data with information stored in our MySQL database, and format the result to be consistent with a specific XML Schema.

The last step we need to accomplish is to archive order information in our MySQL database for reporting purposes. Can we do that using Data Integration Suite?

Next MySQL Example – Archiving XML in MySQL