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") |
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") |
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 :) |
Using DataDirect XQuery together with DataDirect XML Converters and all the expressiveness of the XQuery language there are close to no limits.
