Create (EDI) messages out of your database

by Marc Van Cappellen on December 27, 2007

in XML

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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
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.

1
2
3
4
5
6
7
8
9
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.

Tech Tags:

Tagged as: edi, example, XQuery

Bookmark and Share

Leave a Comment

Previous post:

Next post: