Home:Learn XQuery:XQuery Examples:MySQL Examples:Processing EDI as XML with MySQL and XQuery

Processing EDI as XML using MySQL and DataDirect XQuery^®

In the previous topic, Processing XML using DataDirect XQuery^®, 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, DataDirect XQuery^® is able to provide access to a wide variety of EDI messages as if they were XML messages, thanks to its integration with DataDirect XML Converters™. Thanks to such integration, 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 DataDirect XQuery 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 XQuery:

    <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 DataDirect XQuery^®, 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 DataDirect XQuery^®?

Next MySQL Example

Archiving XML in a MySQL Database