Home Services Partners Company
Create (EDI) messages out of your database

Create (EDI) messages out of your database

December 27, 2007 0 Comments

This is our third post in a series about XQuery generating multiple XML documents.

It's not uncommon to have the needs to generate XML messages out of data stored in your relational database in some type of batch process. Obviously a first class use case for DataDirect XQuery, and the built-in function ddtek:serialize-to-urlcomes again to rescue.

Suppose we're a bookstore, running a MySQL database with supplier and order information. As we know, with DataDirect XQuery tables in your relational database are accessed through the fn:collection function. The following query creates an XML document for each of the suppliers, listing all orders of today, and each order includes the book's ISBN number and a quantity. The XML documents are saved in the directory C:output, as "suppliername.xml" files.

[cc lang="xquery"]for $supplier in collection("SUPPLIERS")/SUPPLIERS let $orders := collection("ORDERS")/ORDERS[SUP_ID = $supplier/ID and ORDER_DATE = current-date()] where $orders (: ignore the supplier if there are no orders for today :) return let $url := concat("file:///C:/output/", $supplier/NAME, ".xml") let $msg := { for $order in $orders return

{$order/ISBN/text()} {$order/QUANTITY/text()} }

return ddtek:serialize-to-url($msg, $url, "method=xml") [/cc]

But now we need to communicate through EDI with our business partners. DataDirect XQuery not only integrates with DataDirect XML Converters to query and transform EDI messages as shown in a previous post, but also to generate your EDI messages.

In the example above, the third argument of ddtek:serialize-to-url is "method=xml". This instructs DataDirect XQuery to serialize the messages in XML format. By simply specifying "method=EDI", DataDirect XQuery will use the appropriate DataDirect XML Converter to produce EDI messages rather than XML.

The next example is similar to the previous, but generates EANCOM ORDERS messages for each of the suppliers.

[cc lang="xquery"]for $supplier in collection("SUPPLIERS")/SUPPLIERS let $orders := collection("ORDERS")/ORDERS[SUP_ID = $supplier/ID and ORDER_DATE = current-date()] where $orders return let $url := concat("file:///C:/output/", $supplier/NAME, ".edi") let $edi := (: this message is incomplete :)

UNOA 4

MYBOOKSHOP 666

{$supplier/NAME/text()} {$supplier/BUSINESS_ID/text()}

6002

{ for $order at $lineitem in $orders return

{$lineitem} 1

{$order/ISBN/text()} IB

1 {$order/QUANTITY/text()}

}

return ddtek:serialize-to-url($edi, $url, "method=EDI") [/cc]

This example creates a fixed EDI message for each of the suppliers, but this can easily be tuned as we're using XQuery. Suppose some of your suppliers require the order information as XML and others as EDI. We can combine parts of the previous two queries.

[cc lang="xquery"]for $supplier in collection("SUPPLIERS")/SUPPLIERS let $orders := collection("ORDERS")/ORDERS[SUP_ID = $supplier/ID and ORDER_DATE = current-date()] where $orders return if ($supplier/MESSAGE_FORMAT = "XML") then (: continue here with the first query :) else (: continue here with the second query :)[/cc]

I hope this gives you an idea how easy it is to generate XML or EDI messages out of your database. And this is not limited to EANCOM messages, many other standards are supported too, think of X12, EDIFACT, HL7, IATA, etc. Using DataDirect XQuery together with DataDirect XML Convertersand all the expressiveness of the XQuery language there are close to no limits.

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

Tech Tags:

Marc Van Cappellen

View all posts from Marc Van Cappellen on the Progress blog. Connect with us about all things application development and deployment, data integration and digital business.

Read next SAS ODBC Access to Salesforce, SQL Server and Marketo
Comments
Comments are disabled in preview mode.