Create (EDI) messages out of your database
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-url comes 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.
Using DataDirect XQuery together with DataDirect XML Converters and all the expressiveness of the XQuery language there are close to no limits.
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 := <supplier name="{$supplier/NAME/}">
<orders>{
for $order in $orders
return
<order>
<isbn>{$order/ISBN/text()}</isbn>
<quantity>{$order/QUANTITY/text()}</quantity>
}</orders>
</supplier>
return
ddtek:serialize-to-url($msg, $url, "method=xml")
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.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 :)
<EDIFACT>
<UNB>
<UNB01>
<UNB0101>UNOA</UNB0101>
<UNB0102>4</UNB0102>
</UNB01>
<UNB02>
<UNB0201>MYBOOKSHOP</UNB0201>
<UNB0202>666</UNB0202>
</UNB02>
<UNB03>
<UNB0301>{$supplier/NAME/text()}</UNB0301>
<UNB0302>{$supplier/BUSINESS_ID/text()}</UNB0302>
</UNB03>
<UNB05>6002</UNB05>
</UNB>
<ORDERS>{
for $order at $lineitem in $orders
return
<GROUP_28>
<LIN>
<LIN01>{$lineitem}</LIN01>
<LIN02>1</LIN02>
<LIN03>
<LIN0301>{$order/ISBN/text()}</LIN0301>
<LIN0302>IB</LIN0302>
</LIN03>
</LIN>
<QTY>
<QTY01>
<QTY0101>1</QTY0101>
<QTY0102>{$order/QUANTITY/text()}</QTY0102>
</QTY01>
</QTY>
</GROUP_28>
}</ORDERS>
</EDIFACT>
return
ddtek:serialize-to-url($edi, $url, "method=EDI")
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.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 :)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 Converters and all the expressiveness of the XQuery language there are close to no limits.
posted by marc at
6:30 PM
|
0 Comments
