Transform a directory of EDI messages

We recently announced DataDirect XQuery 3.1. It integrates now nicely with the DataDirect XML Converters, and as we have the ability to query all files in a directory, this opens a new range of use cases.

Suppose you have a directory with EDI messages and need to transform them all to XML, for example to make the messages consumable for some other business process.

In 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,

[cc lang="xquery"]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, "")[/cc]

But ok, we have now 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.

[cc lang="xquery"]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, "")[/cc]

The resulting XML document need to conform to a specific XML Schema. No problem, XQuery 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.

[cc lang="xquery"]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 := { for $group28 in $edi/EDIFACT/ORDERS/GROUP_28 return

{ $group28/LIN/LIN03/LIN0301/text() } { $group28/QTY/QTY01/QTY0102/text() } { $group28/LIN/LIN03/LIN0301/text() }

} return ddtek:serialize-to-url($xml, $filename, "")[/cc]

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.

[cc lang="xquery"]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 := {

{ concat($address/STREET, " ", $address/NUMBER) } { $address/ZIPCODE } { $address/STATE } , for $group28 in $edi/EDIFACT/ORDERS/GROUP_28 return

{ $group28/LIN/LIN03/LIN0301/text() } { $group28/QTY/QTY01/QTY0102/text() } { $group28/LIN/LIN03/LIN0301/text() }

} return ddtek:serialize-to-url($xml, $filename, "") [/cc]

Hopefully I demonstrated how to query, enrich an transform XML messages. Soon we'll talk about "split a huge XML file into smaller documents".

digg_skin = 'compact'; digg_url = 'http://www.xml-connection.com/2007/12/transform-directory-of-edi-messages.html'