Developers

EDI Translation: How to Transform a directory of EDI messages

The recent release of DataDirect XQuery 3.1 features new integration with with DataDirect XML Converters, and since DataDirect XML Converters supports the ability to query all files in a directory, this opens up a new range of use cases and solutions for EDI translation.

Suppose you have a directory with EDI messages and need to transform or translate them all to XML, for example to translate the EDI messages into an XML data format for consumption by some other business process.

In a previous tutorial on XQuery generating multiple XML documents we learned how to query a directory of XML document, and to transform and save each of the documents into a new XML file. To refresh our mind, the following query copies all XML files from one to another directory:

declare function local:get-file-name($document-uri as xs:string){
  tokenize($document-uri, "/")[last()]
}; 

for $doc in fn:collection("file:///C:/input?select=*.xml")
let $filename := concat("file:///C:/output/",
                        local:get-file-name(document-uri($doc)))
return
  ddtek:serialize-to-url($doc, $filename, "")

An EDI Translation Example

Now suppose that we have a directory of EDI messages, and want to transform them all into XML. Only a few changes to the above query are needed. The argument to fn:collection is different, in order to make DataDirect XQuery invoke the appropriate DataDirect XML Converter. And second, the local:get-file-name() is a bit more complex to generate the matching .xml filename.

declare function local:get-file-name($document-uri as xs:string){
  let $file-name := tokenize($document-uri, "/")[last()]
  let $file-name := replace($file-name,'^(.*)\..*','$1')
  let $file-name := concat($file-name, ".", "edi")
  return $file-name
}; 

for $doc in fn:collection("converter:///EDI?file:///C:/input?select=*.edi")
let $filename := concat("file:///C:/output/",
                        local:get-file-name(document-uri($doc)))
return
  ddtek:serialize-to-url($doc, $filename, "")

What if the resulting XML document needs to conform to a specific XML Schema? No problem, XQuery is schema aware and is designed to perform such transformations. Assume we need to transform EDIFACT messages, we start from our previous query and simply add some transformation logic.

for $edi in fn:collection("converter:///EDI?file:///C:/input?select=*.edi")

let $filename := concat("file:///C:/output/",
                        local:get-file-name(document-uri($edi)))
let $xml := 
  <order submitter="{$edi/EDIFACT/UNB/UNB02/UNB0201}">{
    for $group28 in $edi/EDIFACT/ORDERS/GROUP_28
     return 
      <book>
        <id>{
          $group28/LIN/LIN03/LIN0301/text()
        }</id>
        <quantity>{
          $group28/QTY/QTY01/QTY0102/text()
        }</quantity>
        <ISBN>{
          $group28/LIN/LIN03/LIN0301/text()
        }</ISBN>
      </book>
  }</order>
return
  ddtek:serialize-to-url($xml, $filename, "")

We have still fairly simple code with already significant functionality. You're now ready to use any of the built-in DataDirect XQuery functionality. Assume the data needs to be enriched with data from a Web service or your customer data stored in a relational database. Let's extend the previous example and add a shipping address to the result, which is obtained out of the CUSTOMERS table in our database.

for $edi in fn:collection("converter:///EDI?file:///C:/input?select=*.edi")
let $address := collection("CUSTOMERS")/CUSTOMERS[ID = $edi/EDIFACT/UNB/UNB02/UNB0201]
let $filename := concat("file:///C:/output/",
                        local:get-file-name(document-uri($edi)))
let $xml := 
  <order submitter="{$edi/EDIFACT/UNB/UNB02/UNB0201}">{
    <shipping_address>
      <street>{
        concat($address/STREET, " ", $address/NUMBER)
      }</street>
      <zip>{
        $address/ZIPCODE
      }</zip>
      <state>{
        $address/STATE
      }</state>
    </shipping_address>,
    for $group28 in $edi/EDIFACT/ORDERS/GROUP_28
     return 
      <book>
        <id>{
          $group28/LIN/LIN03/LIN0301/text()
        }</id>
        <quantity>{
          $group28/QTY/QTY01/QTY0102/text()
        }</quantity>
        <ISBN>{
          $group28/LIN/LIN03/LIN0301/text()
        }</ISBN>
      </book>
  }</order>
return
  ddtek:serialize-to-url($xml, $filename, "")

EDI Translation of Directories in a Nutshell

This tutorial demonstrated how to query, enrich and transform XML messages which came from a XML-translated directory of EDI files. To get started with EDI translation, download a free trial of DataDirect XML Converters and/or DataDirect XQuery today.

Feeds

Multimedia

Newsletter