XML, HTML & Word integrator: MQL recipes

Recipes

Purchase order in XML to a JSON for the API
Reshape an XML shipment notice for a partner
Scrape exchange rates from an HTML page
Fill a Word template, then check the result as XML
Build a SEPA credit transfer XML (pain.001)
Control a UBL e-invoice before booking it
Stage XML orders in an HSQL database and export them as XML
RSS feed to an HTML news digest
Promote an XML logging configuration from dev to prod
GPS track (GPX): distance, climb and average speed

executed on MentDB

Purchase order in XML to a JSON for the API

A partner sends a purchase order in XML. The script reads the header with XPath attributes and texts, loops over the order lines, computes each amount and the total, and returns a JSON document ready for the order API.

Pipeline

01 · loadParse the XMLxml load→02 · headerAttributes and textsxml select attribute→03 · linesCount and loopxml count→04 · valuesQty and pricexml select text→05 · jsonBuild the JSONjson iarray

Script

admin

#1. Purchase order received from the partner;
xml load "rx_po" "<order id=\"PO-881\" date=\"2026-10-28\" currency=\"EUR\"><customer code=\"C-0042\">Dupont &amp; Fils</customer><line sku=\"BLT-M8\"><qty>250</qty><price>0.12</price></line><line sku=\"NUT-M8\"><qty>300</qty><price>0.05</price></line><line sku=\"DRL-18V\"><qty>2</qty><price>129.00</price></line></order>";
#2. Header of the order;
json load "rx_out" "{}";
json iobject "rx_out" "/" "order" (xml select attribute "rx_po" "/order/@id") STR;
json iobject "rx_out" "/" "date" (xml select attribute "rx_po" "/order/@date") STR;
json iobject "rx_out" "/" "customer" (xml select text "rx_po" "/order/customer") STR;
json iobject "rx_out" "/" "customer_code" (xml select attribute "rx_po" "/order/customer/@code") STR;
json iobject "rx_out" "/" "line_fields" (xml fields "rx_po" "/order/line[1]") ARRAY;
json iobject "rx_out" "/" "lines" "[]" ARRAY;
#3. One JSON object per line, with its amount;
-> "[n]" (xml count "rx_po" "/order/line");
-> "[total]" 0;
for (-> "[i]" 1) (<= [i] [n]) (++ "[i]") {
	-> "[p]" (concat "/order/line[" [i] "]");
	-> "[amount]" (math round (* (xml select text "rx_po" (concat [p] "/qty")) (xml select text "rx_po" (concat [p] "/price"))) 2);
	json iarray "rx_out" "/lines" (concat "{\"sku\": \"" (xml select attribute "rx_po" (concat [p] "/@sku")) "\", \"amount\": " [amount] "}") OBJ;
	-> "[total]" (+ [total] [amount]);
};
json iobject "rx_out" "/" "total" [total] NUM;
xml unload "rx_po";
json doc "rx_out";

Result

mentdb

{
  "date": "2026-10-28",
  "total": 303.0,
  "line_fields": [
    "qty",
    "price"
  ],
  "lines": [
    {
      "amount": 30,
      "sku": "BLT-M8"
    },
    {
      "amount": 15,
      "sku": "NUT-M8"
    },
    {
      "amount": 258,
      "sku": "DRL-18V"
    }
  ],
  "customer_code": "C-0042",
  "order": "PO-881",
  "customer": "Dupont \u0026 Fils"
}

Go further

Read a whole sub-tree as XML with xml select node to forward it unchanged
List every path of an unknown document with xml xpath

executed on MentDB

Reshape an XML shipment notice for a partner

The warehouse produces a shipment notice with internal data. Before sending it to the customer, the script removes the internal cost attribute, adds the partner code, normalises the carrier, removes a cancelled parcel, adds a status and an escaped note, then checks the result with XPath.

Pipeline

01 · loadInternal noticexml load→02 · attributesRemove and addxml dattribute→03 · nodesUpdate, delete, insertxml dnode→04 · escapeSafe text in a nodexml escape_10→05 · checkPaths and documentxml xpath

Script

admin

#1. Internal shipment notice;
xml load "rx_asn" "<shipment id=\"SH-5521\" warehouse=\"WH-RUN\" internal_cost=\"18.40\"><carrier>colissimo</carrier><parcel><ref>P1</ref><weight>2.4</weight></parcel><parcel><ref>P2</ref><weight>0.8</weight></parcel><parcel><ref>P3</ref><weight>5.1</weight></parcel></shipment>";
#2. Attributes: hide the internal cost, add the partner;
xml dattribute "rx_asn" "/shipment" "internal_cost";
xml iattribute "rx_asn" "/shipment" "partner" "DUPONT";
#3. Nodes: carrier in capitals, parcel P2 cancelled, status and note added;
xml utext "rx_asn" "/shipment/carrier" (string upper (xml select text "rx_asn" "/shipment/carrier"));
xml dnode "rx_asn" "/shipment/parcel[2]";
xml itext "rx_asn" "/shipment" "status" "SHIPPED";
xml inode "rx_asn" "/shipment" (concat "<note lang=\"en\">" (xml escape_10 "Fragile: glass & <ceramics>") "</note>");
xml inode "rx_asn" "/shipment" (concat "<contact>" (xml escape_11 "R&D Dept. \"Lab\"") "</contact>");
#4. Check the paths and return the new document;
-> "[parcels]" (xml count "rx_asn" "/shipment/parcel");
-> "[paths]" (json load "rx_paths" (xml xpath "rx_asn" "/shipment/parcel"));
-> "[res]" (concat "parcels: " [parcels] ", paths: " (string regexp_replace (json doc "rx_paths") "\s" "") ", loaded: " (string regexp_replace (xml show) "\s" "") " " (xml exist "rx_asn") [_n_] (xml select node "rx_asn" "/shipment/note") [_n_] (xml doc "rx_asn"));
xml unload_all;
[res];

Result

mentdb

parcels: 2, paths: ["/shipment/parcel","/shipment/parcel/ref","/shipment/parcel/weight","/shipment/parcel[2]","/shipment/parcel/ref","/shipment/parcel/weight"], loaded: ["rx_asn"] 1
<?xml version="1.0" encoding="UTF-8"?><note lang="en">Fragile: glass &amp; &lt;ceramics&gt;</note>

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<shipment id="SH-5521" partner="DUPONT" warehouse="WH-RUN">
  <carrier>COLISSIMO</carrier>
  <parcel>
    <ref>P1</ref>
    <weight>2.4</weight>
  </parcel>
  <parcel>
    <ref>P3</ref>
    <weight>5.1</weight>
  </parcel>
  <status>SHIPPED</status>
  <note lang="en">Fragile: glass &amp; &lt;ceramics&gt;</note>
  <contact>R&amp;D Dept. "Lab"</contact>
</shipment>

Go further

xml escape_10 and xml escape_11 escape a value for XML 1.0 and 1.1: needed with xml inode, which receives raw XML
Free the memory of all documents with xml unload_all at the end of a batch

executed on MentDB

Scrape exchange rates from an HTML page

A treasury tool needs the reference rates published on a web page. The script reads the title by id, the date by class, every rate row with a CSS selector and the download links by tag, then converts 250 EUR into each currency.

Pipeline

01 · loadParse the pagehtml load_from_str→02 · elementTitle by idhtml element→03 · selectRows by CSS selectorhtml parse→04 · convertLoop on the ratesjson parse_obj→05 · closeFree the DOMhtml close

Script

admin

#1. Start clean, then parse the page, here as a string;
html close_all;
html load_from_str "rx_page" "<html><body><h1 id=\"title\">Euro foreign exchange reference rates</h1><p class=\"date\">31 October 2026</p><table id=\"rates\"><tr><th>Currency</th><th>Rate</th></tr><tr class=\"rate\"><td>USD</td><td>1.0842</td></tr><tr class=\"rate\"><td>GBP</td><td>0.8571</td></tr><tr class=\"rate\"><td>JPY</td><td>164.12</td></tr></table><a href=\"/stats/eurofxref.csv\">CSV</a> <a href=\"/stats/eurofxref.xml\">XML</a></body></html>";
#2. Title by id, date by class;
html element "rx_page" "rx_t" "title";
html parse "rx_page" "rx_d" "CLASS" "date" "" {
	-> "[date]" (json select "rx_d" "/text");
};
#3. Rate rows with a CSS selector, quoted because it contains a space;
json load "rx_rates" "{}";
html parse "rx_page" "rx_row" "SELECT" "\"table#rates tr.rate\"" "" {
	-> "[row]" (json select "rx_row" "/text");
	json iobject "rx_rates" "/" (string regexp_extract [row] "^([A-Z]+)" 1) (string regexp_extract [row] "([0-9.]+)$" 1) NUM;
};
#4. Download links by tag;
json load "rx_links" "[]";
html parse "rx_page" "rx_a" "TAG" "a" "" {
	json iarray "rx_links" "/" (json select "rx_a" "/attributes/href") STR;
};
#5. Convert 250 EUR with each rate;
json load "rx_conv" "{}";
json parse_obj "rx_rates" "/" "[cur]" "[rate]" {
	json iobject "rx_conv" "/" [cur] (math round (* 250 [rate]) 2) NUM;
};
-> "[open]" (concat "{\"exist\": " (html exist "rx_page") ", \"doms\": " (string regexp_replace (html show) "\s" "") "}");
html close "rx_page";
concat "{\"title\": \"" (json select "rx_t" "/text") "\", \"date\": \"" [date] "\", \"dom_before_close\": " [open] ", \"rates\": " (string regexp_replace (json doc "rx_rates") "\s" "") ", \"eur_250\": " (string regexp_replace (json doc "rx_conv") "\s" "") ", \"links\": " (string regexp_replace (json doc "rx_links") "\s" "") "}";

Result

mentdb

{"title": "Euro foreign exchange reference rates", "date": "31 October 2026", "dom_before_close": {"exist": 1, "doms": ["rx_page"]}, "rates": {"JPY":164.12,"GBP":0.8571,"USD":1.0842}, "eur_250": {"JPY":41030.0,"GBP":214.28,"USD":271.05}, "links": ["/stats/eurofxref.csv","/stats/eurofxref.xml"]}

Go further

Fetch the live page with html load_from_url "rx_page" <url> "get" 5000 "{}" "{}" "{}" instead of the string
The key of html parse is evaluated as MQL: quote it as "\"...\"" when the selector contains spaces or parentheses

executed on MentDB

Fill a Word template, then check the result as XML

Customer letters are produced from a Word template with tags like @val:client.name, a products table and images. The script fills the sample template shipped in tools/mswordx, then opens the produced docx, which is a zip of XML files, to count the text runs, read the first one and list the embedded images.

Pipeline

01 · dataTags as JSONjson iobject→02 · mergeFill the templatemswordx replace→03 · unzipA docx is a zipcompress unzip→04 · xmlRead the document XMLxml select text→05 · mediaEmbedded imagesfile dir_list

Script

admin

#1. Values for the tags of the template: @val:, @tab:products, @img:;
json load "rx_fr" "{}";
json iobject "rx_fr" "/" "lang" "en" STR;
json iobject "rx_fr" "/" "nb" "2" STR;
json iobject "rx_fr" "/" "client" "{}" OBJ;
json iobject "rx_fr" "/client" "name" "Mrs Hoarau" STR;
json iobject "rx_fr" "/client" "city" "Saint-Pierre" STR;
json iobject "rx_fr" "/" "products" "[]" ARRAY;
json iarray "rx_fr" "/products" (concat "{\"ref\": \"A001\", \"prix\": \"50\", \"photo\": \"" (file b64_read "images/info.png") "\"}") OBJ;
json iarray "rx_fr" "/products" (concat "{\"ref\": \"B220\", \"prix\": \"120\", \"photo\": \"" (file b64_read "images/stop.png") "\"}") OBJ;
json iobject "rx_fr" "/" "logo" (file b64_read "images/neural.png") STR;
json iobject "rx_fr" "/" "photo_product" (file b64_read "images/info.png") STR;
#2. Fill the template;
mswordx replace "tools/mswordx/word1.docx" "tmp/rx_letter.docx" (json doc "rx_fr");
#3. Open the docx: it is a zip of XML parts;
compress unzip "tmp/rx_letter.docx" "tmp/rx_letter";
xml load "rx_doc" (file load "tmp/rx_letter/word/document.xml");
json load "rx_media" (file dir_list "tmp/rx_letter/word/media");
concat "{\"text_runs\": " (xml count "rx_doc" "//*[local-name()='t']") ", \"first_run\": \"" (xml select text "rx_doc" "(//*[local-name()='t'])[1]") "\", \"images\": " (json count "rx_media" "/") ", \"docx_bytes\": " (file size "tmp/rx_letter.docx") ", \"text\": \"" (string trim (string regexp_replace (string regexp_replace (file load "tmp/rx_letter/word/document.xml") "<[^>]+>" " ") "\s+" " ")) "\"}";

Result

mentdb

{"text_runs": 16, "first_run": "Bonjour Mrs Hoarau de Saint-Pierre, vous avez 2 articles. Logo de l'entreprise :  Photo produit principale : ", "images": 3, "docx_bytes": 11721, "text": "Bonjour Mrs Hoarau de Saint-Pierre, vous avez 2 articles. Logo de l'entreprise : Photo produit principale : PRIX REF 50 A001 120 B220"}

Go further

With a second template for the repeated block, mswordx replace <mainSource> <loopSource> <destination> <json> expands the @loop: tags
Turn the letter into PDF for archiving, or attach the docx to mail send

executed on MentDB

Build a SEPA credit transfer XML (pain.001)

The treasury sends approved supplier payments to the bank as a pain.001 message. The script starts from a skeleton, adds the namespace and the execution date, one CdtTrfTxInf node per payment with the creditor name escaped, then fills NbOfTxs and the exact CtrlSum and checks the message before sending.

Pipeline

01 · skeletonLoad and add attributesxml iattribute→02 · paymentsOne node per paymentxml inode→03 · textExecution datexml itext→04 · headerUpdate the totalsxml utext→05 · checkFields and nodexml select node

Script

admin

#1. Payments approved today, to send to the bank as a SEPA credit transfer file;
json load "rx_pay" "[{\"id\": \"PAY-0101\", \"name\": \"Martin & Fils\", \"iban\": \"FR7630006000011234567890189\", \"amount\": \"1250.00\"}, {\"id\": \"PAY-0102\", \"name\": \"Hôtel <Les Palmes>\", \"iban\": \"FR7617569000301234567890112\", \"amount\": \"380.40\"}, {\"id\": \"PAY-0103\", \"name\": \"Transports Hoarau\", \"iban\": \"FR7610107001011234567890129\", \"amount\": \"2045.15\"}]";
#2. Skeleton of the pain.001 message, header values filled later;
xml load "rx_sct" "<Document><CstmrCdtTrfInitn><GrpHdr><MsgId>SCT-20261101-01</MsgId><NbOfTxs>0</NbOfTxs><CtrlSum>0</CtrlSum></GrpHdr><PmtInf/></CstmrCdtTrfInitn></Document>";
xml iattribute "rx_sct" "/Document" "xmlns" "urn:iso:std:iso:20022:tech:xsd:pain.001.001.03";
xml itext "rx_sct" "/Document/CstmrCdtTrfInitn/PmtInf" "ReqdExctnDt" "2026-11-02";
#3. One CdtTrfTxInf node per payment, names escaped;
-> "[sum]" 0;
for (-> "[i]" 0) (< [i] (json count "rx_pay" /)) (++ "[i]") {
	-> "[p]" (concat "/[" [i] "]");
	xml inode "rx_sct" "/Document/CstmrCdtTrfInitn/PmtInf" (concat "<CdtTrfTxInf><EndToEndId>" (json select "rx_pay" (concat [p] "/id")) "</EndToEndId><InstdAmt>" (json select "rx_pay" (concat [p] "/amount")) "</InstdAmt><Cdtr>" (xml escape_11 (json select "rx_pay" (concat [p] "/name"))) "</Cdtr><IBAN>" (json select "rx_pay" (concat [p] "/iban")) "</IBAN></CdtTrfTxInf>");
	xml iattribute "rx_sct" (concat "/Document/CstmrCdtTrfInitn/PmtInf/CdtTrfTxInf[" (+ [i] 1) "]/InstdAmt") "Ccy" "EUR";
	-> "[sum]" (bd+ [sum] (json select "rx_pay" (concat [p] "/amount")));
};
#4. Header totals, then checks before sending;
xml utext "rx_sct" "/Document/CstmrCdtTrfInitn/GrpHdr/NbOfTxs" (xml count "rx_sct" "/Document/CstmrCdtTrfInitn/PmtInf/CdtTrfTxInf");
xml utext "rx_sct" "/Document/CstmrCdtTrfInitn/GrpHdr/CtrlSum" [sum];
-> "[out]" (concat "{\"loaded\": " (xml exist "rx_sct") ", \"pmtinf_fields\": " (string regexp_replace (xml fields "rx_sct" "/Document/CstmrCdtTrfInitn/PmtInf") "\s" "") ", \"nb\": " (xml select text "rx_sct" "/Document/CstmrCdtTrfInitn/GrpHdr/NbOfTxs") ", \"ctrl_sum\": " (xml select text "rx_sct" "/Document/CstmrCdtTrfInitn/GrpHdr/CtrlSum") ", \"second_creditor\": \"" (string json_escape (xml select text "rx_sct" "/Document/CstmrCdtTrfInitn/PmtInf/CdtTrfTxInf[2]/Cdtr")) "\", \"size_bytes\": " (string length (xml doc "rx_sct")) "}");
-> "[tx2]" (xml select node "rx_sct" "/Document/CstmrCdtTrfInitn/PmtInf/CdtTrfTxInf[2]");
xml unload "rx_sct";
json unload "rx_pay";
concat [out] (string char 10) [tx2]

Result

mentdb

{"loaded": 1, "pmtinf_fields": ["ReqdExctnDt","CdtTrfTxInf","CdtTrfTxInf","CdtTrfTxInf"], "nb": 3, "ctrl_sum": 3675.55, "second_creditor": "Hôtel <Les Palmes>", "size_bytes": 1031}
<?xml version="1.0" encoding="UTF-8"?><CdtTrfTxInf>
  <EndToEndId>PAY-0102</EndToEndId>
  <InstdAmt Ccy="EUR">380.40</InstdAmt>
  <Cdtr>Hôtel &lt;Les Palmes&gt;</Cdtr>
  <IBAN>FR7617569000301234567890112</IBAN>
</CdtTrfTxInf>

Go further

The sum uses bd+ so 1250.00 + 380.40 + 2045.15 is exact: the bank rejects a file whose CtrlSum is off by one cent
Read the bank status report pain.002 back with xml select text on /Document/CstmrPmtStsRpt

executed on MentDB

Control a UBL e-invoice before booking it

A supplier sends a UBL 2.1 e-invoice with namespaces. Read it with local-name() XPath, recompute the line totals and the VAT, and compare them with the declared LegalMonetaryTotal before the invoice is booked.

Pipeline

01 · loadUBL invoice with namespacesxml load→02 · linesCount and read the linesxml count→03 · recomputeLine sum, VAT, payablexml select text→04 · verdictCompare with the declared totalsjson iobject

Script

admin

#1. The e-invoice, UBL 2.1 namespaces kept;
xml load "rx_p4i_ubl" "<Invoice xmlns=\"urn:oasis:names:specification:ubl:schema:xsd:Invoice-2\" xmlns:cac=\"urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2\" xmlns:cbc=\"urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2\"><cbc:ID>INV-2026-0912</cbc:ID><cbc:IssueDate>2026-10-29</cbc:IssueDate><cac:AccountingSupplierParty><cac:Party><cac:PartyName><cbc:Name>Ocean Pack Ltd</cbc:Name></cac:PartyName></cac:Party></cac:AccountingSupplierParty><cac:TaxTotal><cbc:TaxAmount currencyID=\"EUR\">121.80</cbc:TaxAmount></cac:TaxTotal><cac:LegalMonetaryTotal><cbc:LineExtensionAmount currencyID=\"EUR\">609.00</cbc:LineExtensionAmount><cbc:PayableAmount currencyID=\"EUR\">730.80</cbc:PayableAmount></cac:LegalMonetaryTotal><cac:InvoiceLine><cbc:InvoicedQuantity unitCode=\"C62\">12</cbc:InvoicedQuantity><cbc:LineExtensionAmount currencyID=\"EUR\">294.00</cbc:LineExtensionAmount><cac:Price><cbc:PriceAmount>24.50</cbc:PriceAmount></cac:Price></cac:InvoiceLine><cac:InvoiceLine><cbc:InvoicedQuantity unitCode=\"C62\">35</cbc:InvoicedQuantity><cbc:LineExtensionAmount currencyID=\"EUR\">315.00</cbc:LineExtensionAmount><cac:Price><cbc:PriceAmount>9.00</cbc:PriceAmount></cac:Price></cac:InvoiceLine></Invoice>";
-> "[ns]" "/*[local-name()='Invoice']";
#2. Header;
json load "rx_p4i_ctl" "{}";
json iobject "rx_p4i_ctl" / "invoice" (xml select text "rx_p4i_ubl" (concat [ns] "/*[local-name()='ID']")) STR;
json iobject "rx_p4i_ctl" / "supplier" (xml select text "rx_p4i_ubl" "//*[local-name()='AccountingSupplierParty']//*[local-name()='Name']") STR;
#3. Each line: quantity x price must equal the line amount;
-> "[n]" (xml count "rx_p4i_ubl" "//*[local-name()='InvoiceLine']");
-> "[sum]" 0;
-> "[bad_lines]" 0;
for (-> "[i]" 1) (<= [i] [n]) (++ "[i]") {
	-> "[l]" (concat "(//*[local-name()='InvoiceLine'])[" [i] "]");
	-> "[q]" (xml select text "rx_p4i_ubl" (concat [l] "/*[local-name()='InvoicedQuantity']"));
	-> "[p]" (xml select text "rx_p4i_ubl" (concat [l] "//*[local-name()='PriceAmount']"));
	-> "[a]" (xml select text "rx_p4i_ubl" (concat [l] "/*[local-name()='LineExtensionAmount']"));
	if (!= (math round (* [q] [p]) 2) (math round [a] 2)) {++ "[bad_lines]";};
	-> "[sum]" (+ [sum] [a]);
};
#4. Totals: lines, VAT 20 percent, payable;
-> "[decl_net]" (xml select text "rx_p4i_ubl" "//*[local-name()='LegalMonetaryTotal']/*[local-name()='LineExtensionAmount']");
-> "[decl_vat]" (xml select text "rx_p4i_ubl" "//*[local-name()='TaxTotal']/*[local-name()='TaxAmount']");
-> "[decl_pay]" (xml select text "rx_p4i_ubl" "//*[local-name()='PayableAmount']");
-> "[vat]" (math round (* [sum] 0.2) 2);
json iobject "rx_p4i_ctl" / "lines" [n] INT;
json iobject "rx_p4i_ctl" / "lines_in_error" [bad_lines] INT;
json iobject "rx_p4i_ctl" / "net_ok" (== (math round [sum] 2) (math round [decl_net] 2)) BOOL;
json iobject "rx_p4i_ctl" / "vat_ok" (== [vat] (math round [decl_vat] 2)) BOOL;
json iobject "rx_p4i_ctl" / "payable_ok" (== (math round (+ [sum] [vat]) 2) (math round [decl_pay] 2)) BOOL;
json iobject "rx_p4i_ctl" / "currency" (xml select attribute "rx_p4i_ubl" "//*[local-name()='PayableAmount']/@currencyID") STR;
xml unload "rx_p4i_ubl";
json doc "rx_p4i_ctl";

Result

mentdb

{
  "net_ok": "1",
  "lines_in_error": 0,
  "supplier": "Ocean Pack Ltd",
  "vat_ok": "1",
  "currency": "EUR",
  "invoice": "INV-2026-0912",
  "lines": 2,
  "payable_ok": "1"
}

Go further

Check the VAT rate per line from cac:ClassifiedTaxCategory when the invoice mixes rates.
Reject the file when an XPath returns nothing: test it with xml count first.
Book the invoice with sql dml only if the three checks are true.

executed on MentDB

Stage XML orders in an HSQL database and export them as XML

A partner drops a batch of orders in XML. Stage them in an embedded HSQL database, aggregate them per customer with SQL, and send back an acknowledgement produced by sql to xml.

Pipeline

01 · parseOrders from the XML batchxml select attribute→02 · stageEmbedded HSQL staging tablecm set hsql_embedded→03 · aggregatePer customer totalssql to json→04 · ackAcknowledgement as XMLsql to xml

Script

admin

#1. The batch;
xml load "rx_p4i_batch" "<orders batch=\"B-2026-311\"><order id=\"7001\" customer=\"C-12\" amount=\"420.00\"/><order id=\"7002\" customer=\"C-07\" amount=\"89.90\"/><order id=\"7003\" customer=\"C-12\" amount=\"1310.50\"/><order id=\"7004\" customer=\"C-31\" amount=\"56.00\"/></orders>";
#2. Embedded HSQL database under tmp;
cm set "tuto_p4i_stage_db" {execute "db.hsql.embedded.config.get" "[database]" "tmp/rx_p4i_hsql/stage" "[user]" "SA" "[password]" "";};
sql connect "rx_p4i_hs" {cm get "tuto_p4i_stage_db"};
sql dml "rx_p4i_hs" (concat "create table orders (id int primary key, customer varchar(10), amount decimal(10,2), batch varchar(20))");
-> "[batch]" (xml select attribute "rx_p4i_batch" "/orders/@batch");
for (-> "[i]" 1) (<= [i] (xml count "rx_p4i_batch" "/orders/order")) (++ "[i]") {
	-> "[o]" (concat "/orders/order[" [i] "]");
	sql dml "rx_p4i_hs" (concat "insert into orders values (" (xml select attribute "rx_p4i_batch" (concat [o] "/@id")) ", " (sql encode (xml select attribute "rx_p4i_batch" (concat [o] "/@customer"))) ", " (xml select attribute "rx_p4i_batch" (concat [o] "/@amount")) ", " (sql encode [batch]) ")");
};
xml unload "rx_p4i_batch";
#3. Aggregate per customer;
json load "rx_p4i_agg" (sql to json "rx_p4i_hs" "customers" (concat "select customer, count(*) as nb, sum(amount) as total from orders group by customer order by total desc"));
#4. The acknowledgement, straight from SQL to XML;
-> "[ack]" (sql to xml "rx_p4i_hs" "ack" (concat "select id, 'ACCEPTED' as status from orders where batch = " (sql encode [batch]) " order by id"));
#HSQL keeps the database open in the JVM until SHUTDOWN;
sql dml "rx_p4i_hs" "SHUTDOWN";
sql disconnect "rx_p4i_hs";
cm remove "tuto_p4i_stage_db";
file delete "tmp/rx_p4i_hsql";
xml load "rx_p4i_ack" [ack];
-> "[res]" (concat "Batch " [batch] ": top customer " (json select "rx_p4i_agg" "/data[0]/CUSTOMER") " with " (json select "rx_p4i_agg" "/data[0]/TOTAL") " EUR in " (json select "rx_p4i_agg" "/data[0]/NB") " orders, customers: " (json count "rx_p4i_agg" "/data") [_n_] "Ack table: " (xml select text "rx_p4i_ack" "/table/table") ", fields per row: " (string regexp_replace (xml fields "rx_p4i_ack" "/table/data/item[1]") "\s+" "") ", accepted rows: " (xml count "rx_p4i_ack" "/table/data/item") ", first id: " (xml select text "rx_p4i_ack" "/table/data/item[1]/ID"));
xml unload "rx_p4i_ack";
[res];

Result

mentdb

Batch B-2026-311: top customer C-12 with 1730.50 EUR in 2 orders, customers: 3
Ack table: ack, fields per row: ["ID","STATUS"], accepted rows: 4, first id: 7001

Go further

Send the acknowledgement back with sftp put once written with file create.
Reject the duplicates with a primary key error caught by try.
Keep the HSQL file between runs to track the batches already received.

executed on MentDB

RSS feed to an HTML news digest

Turn a supplier RSS 2.0 feed into a short HTML digest for the intranet: keep the items of the last days, format the dates, then parse the produced HTML to check every link before publishing.

Pipeline

01 · feedRSS 2.0 itemsxml count→02 · filterItems after a datedate is_after→03 · htmlDigest as HTMLxml escape_11→04 · checkParse the HTML, list the linkshtml parse

Script

admin

#1. The feed;
xml load "rx_p4i_rss" "<rss version=\"2.0\"><channel><title>Ocean Pack news</title><item><title>New recycled carton range</title><link>https://news.example.com/carton-r</link><pubDate>2026-10-30</pubDate></item><item><title>Price update &amp; delivery slots</title><link>https://news.example.com/prices-q4</link><pubDate>2026-10-27</pubDate></item><item><title>Summer closing days</title><link>https://news.example.com/summer</link><pubDate>2026-07-02</pubDate></item></channel></rss>";
-> "[since]" "2026-10-15";
#2. Keep the recent items, escape the titles for HTML;
-> "[li]" "";
-> "[kept]" 0;
for (-> "[i]" 1) (<= [i] (xml count "rx_p4i_rss" "/rss/channel/item")) (++ "[i]") {
	-> "[it]" (concat "/rss/channel/item[" [i] "]");
	-> "[d]" (xml select text "rx_p4i_rss" (concat [it] "/pubDate"));
	if (date is_after [d] [since]) {
		++ "[kept]";
		-> "[li]" (concat [li] "<li><a href=\"" (xml select text "rx_p4i_rss" (concat [it] "/link")) "\">" (xml escape_11 (xml select text "rx_p4i_rss" (concat [it] "/title"))) "</a> <i>" (date dayname [d] "en") " " (date day_of_month [d]) " " (date monthname [d] "en") "</i></li>");
	};
};
-> "[html]" (concat "<html><body><h2>" (xml select text "rx_p4i_rss" "/rss/channel/title") "</h2><ul>" [li] "</ul></body></html>");
xml unload "rx_p4i_rss";
#3. Check the digest as a browser would read it;
html load_from_str "rx_p4i_dom" [html];
-> "[links]" "";
html parse "rx_p4i_dom" "rx_p4i_a" "TAG" "a" "" {
	-> "[links]" (concat [links] (json select "rx_p4i_a" "/text") " => " (json select "rx_p4i_a" "/attributes/href") [_n_]);
};
html close "rx_p4i_dom";
concat "Items kept since " [since] ": " [kept] [_n_] [links] "HTML size: " (string length [html]) " chars";

Result

mentdb

Items kept since 2026-10-15: 2
New recycled carton range => https://news.example.com/carton-r
Price update & delivery slots => https://news.example.com/prices-q4
HTML size: 283 chars

Go further

Read the real feed with rest https and the same XPath.
Mail the digest with mail send as an HTML body.
Store the last link published in a parameter to send only the new items.

executed on MentDB

Promote an XML logging configuration from dev to prod

Before a release, the log4j2 XML configuration must be adapted for production: raise the log level, remove the console appender, add a rolling file appender and tag the file. The script edits the XML in place and prints a diff summary.

Pipeline

01 · readDev configurationxml load→02 · editLevel, appender removedxml dnode→03 · addRolling file appenderxml inode→04 · tagAttributes and savexml iattribute

Script

admin

#1. The dev configuration;
file create "tmp/rx_p4i_log4j2.xml" "<Configuration status=\"debug\"><Appenders><Console name=\"Console\" target=\"SYSTEM_OUT\"><PatternLayout pattern=\"%d %p %c - %m%n\"/></Console></Appenders><Loggers><Logger name=\"com.shop.api\" level=\"debug\"/><Root level=\"debug\"><AppenderRef ref=\"Console\"/></Root></Loggers></Configuration>" "UTF-8";
xml load "rx_p4i_cfg" (file load "tmp/rx_p4i_log4j2.xml" "UTF-8");
-> "[before]" (concat "appenders=" (string regexp_replace (xml fields "rx_p4i_cfg" "/Configuration/Appenders") "\s+" "") ", root=" (xml select attribute "rx_p4i_cfg" "/Configuration/Loggers/Root/@level"));
#2. Production levels, no console;
xml dattribute "rx_p4i_cfg" "/Configuration/Loggers/Root" "level";
xml iattribute "rx_p4i_cfg" "/Configuration/Loggers/Root" "level" "warn";
xml dattribute "rx_p4i_cfg" "/Configuration/Loggers/Logger[@name='com.shop.api']" "level";
xml iattribute "rx_p4i_cfg" "/Configuration/Loggers/Logger[@name='com.shop.api']" "level" "info";
xml dnode "rx_p4i_cfg" "/Configuration/Appenders/Console";
xml dnode "rx_p4i_cfg" "/Configuration/Loggers/Root/AppenderRef";
#3. Rolling file appender, 20 MB, 10 files;
xml inode "rx_p4i_cfg" "/Configuration/Appenders" "<RollingFile name=\"File\" fileName=\"logs/shop-api.log\" filePattern=\"logs/shop-api-%i.log.gz\"><PatternLayout pattern=\"%d{ISO8601} %p %c - %m%n\"/><Policies><SizeBasedTriggeringPolicy size=\"20 MB\"/></Policies><DefaultRolloverStrategy max=\"10\"/></RollingFile>";
xml inode "rx_p4i_cfg" "/Configuration/Loggers/Root" "<AppenderRef ref=\"File\"/>";
#4. Tag and save;
xml dattribute "rx_p4i_cfg" "/Configuration" "status";
xml iattribute "rx_p4i_cfg" "/Configuration" "status" "warn";
xml iattribute "rx_p4i_cfg" "/Configuration" "name" (concat "shop-api-prod-" (date sysdate));
file create "tmp/rx_p4i_log4j2_prod.xml" (xml doc "rx_p4i_cfg") "UTF-8";
-> "[after]" (concat "appenders=" (string regexp_replace (xml fields "rx_p4i_cfg" "/Configuration/Appenders") "\s+" "") ", root=" (xml select attribute "rx_p4i_cfg" "/Configuration/Loggers/Root/@level") ", api=" (xml select attribute "rx_p4i_cfg" "/Configuration/Loggers/Logger/@level") ", max files=" (xml select attribute "rx_p4i_cfg" "//DefaultRolloverStrategy/@max") ", root ref=" (xml select attribute "rx_p4i_cfg" "/Configuration/Loggers/Root/AppenderRef/@ref"));
xml unload "rx_p4i_cfg";
concat "Before: " [before] [_n_] "After:  " [after] [_n_] "Saved: " (file exist "tmp/rx_p4i_log4j2_prod.xml") ", " (file count_lines "tmp/rx_p4i_log4j2_prod.xml") " line(s), " (file size "tmp/rx_p4i_log4j2_prod.xml") " bytes";

Result

mentdb

Before: appenders=["Console"], root=debug
After:  appenders=["RollingFile"], root=warn, api=info, max files=10, root ref=File
Saved: 1, 18 line(s), 604 bytes

Go further

Keep the environments in parameters and apply them with a loop of xml utext and xml iattribute.
Commit the produced file with the versioning functions before the deployment.
Compare two configurations with xml xpath on both documents.

executed on MentDB

GPS track (GPX): distance, climb and average speed

A delivery driver's GPX track must be summarised for the fleet report: total distance by the haversine formula, positive elevation gain, duration and average speed.

Pipeline

01 · pointsTrack points from the GPXxml select attribute→02 · distanceHaversine between pointsmath calc→03 · timeDuration of the trackdate duration→04 · speedAverage speed in km/hdate to_epoch

Script

admin

#1. The GPX track, Saint-Denis, Reunion island;
xml load "rx_p4i_gpx" "<gpx version=\"1.1\" creator=\"FleetBox\"><trk><name>Round 14</name><trkseg><trkpt lat=\"-20.8789\" lon=\"55.4481\"><ele>12</ele><time>2026-10-30 08:00:00</time></trkpt><trkpt lat=\"-20.8851\" lon=\"55.4560\"><ele>35</ele><time>2026-10-30 08:04:10</time></trkpt><trkpt lat=\"-20.8932\" lon=\"55.4627\"><ele>78</ele><time>2026-10-30 08:09:30</time></trkpt><trkpt lat=\"-20.9010\" lon=\"55.4712\"><ele>64</ele><time>2026-10-30 08:15:05</time></trkpt><trkpt lat=\"-20.9075\" lon=\"55.4803\"><ele>121</ele><time>2026-10-30 08:21:40</time></trkpt></trkseg></trk></gpx>";
-> "[n]" (xml count "rx_p4i_gpx" "//trkpt");
-> "[dist]" 0;
-> "[climb]" 0;
#2. Haversine distance and positive climb between consecutive points;
for (-> "[i]" 2) (<= [i] [n]) (++ "[i]") {
	-> "[la1]" (xml select attribute "rx_p4i_gpx" (concat "(//trkpt)[" (- [i] 1) "]/@lat"));
	-> "[lo1]" (xml select attribute "rx_p4i_gpx" (concat "(//trkpt)[" (- [i] 1) "]/@lon"));
	-> "[la2]" (xml select attribute "rx_p4i_gpx" (concat "(//trkpt)[" [i] "]/@lat"));
	-> "[lo2]" (xml select attribute "rx_p4i_gpx" (concat "(//trkpt)[" [i] "]/@lon"));
	-> "[dist]" (+ [dist] (math calc "2*6371000*asin(sqrt(sin(([la2]-[la1])*pi/360)^2+cos([la1]*pi/180)*cos([la2]*pi/180)*sin(([lo2]-[lo1])*pi/360)^2))"));
	-> "[de]" (- (xml select text "rx_p4i_gpx" (concat "(//trkpt)[" [i] "]/ele")) (xml select text "rx_p4i_gpx" (concat "(//trkpt)[" (- [i] 1) "]/ele")));
	if (> [de] 0) {-> "[climb]" (+ [climb] [de]);};
};
#3. Duration and average speed;
-> "[t0]" (xml select text "rx_p4i_gpx" "(//trkpt)[1]/time");
-> "[t1]" (xml select text "rx_p4i_gpx" (concat "(//trkpt)[" [n] "]/time"));
-> "[sec]" (- (date to_epoch [t1]) (date to_epoch [t0]));
-> "[name]" (xml select text "rx_p4i_gpx" "/gpx/trk/name");
xml unload "rx_p4i_gpx";
concat [name] ": " [n] " points, " (math round (/ [dist] 1000) 2) " km, climb +" [climb] " m" [_n_] "Duration " (date duration [t0] [t1]) ", average speed " (math round (/ (* [dist] 3.6) [sec]) 1) " km/h";

Result

mentdb

Round 14: 5 points, 4.64 km, climb +123 m
Duration 21min 40s, average speed 12.8 km/h

Go further

Plot the elevation profile with plot chart against the cumulated distance.
Flag the segments above the speed limit by dividing each distance by its time step.
Read the GPX files of all drivers with file dir_list_regex and store the summary in SQL.