DataSonnet

DataSonnet supports several different input formats out-of-the-box. But what if you need to produce custom output that does not conform to any of the existing standard data formats? One option would be to write a custom DataSonnet plugin that handles this format. Thankfully, there’s an easier way. In this post, we show you how to generate a custom CSV-like payload using only standard DataSonnet features.

Our sample use case requires converting order shipment information from XML to custom flat format.

The input XML looks like this:

<?xml version="1.0" encoding="UTF-8" ?>
<order id="order12345" date="2020-04-11T14:35:00">
   <billTo>
       <name>Kym Chillcot</name>
       <address1>6904 Vera Trail</address1>
       <city>White Plains</city>
       <state>NY</state>
       <zip>10633</zip>
       <phone>(555) 018-0141</phone>
       <email>kchillcot0@x.example.com</email>
   </billTo>
   <shipment id="shipment1" date="2020-04-21T09:10:00">
       <shipTo>
           <name>Ava McGunley</name>
           <address1>5488 Katie Plaza</address1>
           <city>Las Vegas</city>
           <state>NV</state>
           <zip>89105</zip>
           <phone>(555) 014-4721</phone>
           <email>amcgunley2@y.example.com</email>
       </shipTo>
       <items>
           <item name="Hand Sanitizer" sku="hs123" quantity="2"/>
       </items>
   </shipment>
   <shipment id="shipment2" date="2020-04-23T08:42:00">
       <shipTo>
           <name>Ava McGunley</name>
           <address1>5488 Katie Plaza</address1>
           <city>Las Vegas</city>
           <state>NV</state>
           <zip>89105</zip>
           <phone>(555) 014-4721</phone>
           <email>amcgunley2@y.example.com.com</email>
       </shipTo>
       <items>
           <item name="Toilet Paper" sku="tp2pl976" quantity="12"/>
           <item name="Face Mask" sku="856326" quantity="10"/>
       </items>
   </shipment>
</order>

Generate the flat file using the following logic:

Order Header: id,date as MM/dd/yyyy HH:mm:ss
Bill To Address: name,address,city,state,zip,phone,email

Then, for each shipment:

Shipment Header: id,date as MM/dd/yyyy HH:mm:ss
Ship To Address: name,address,city,state,zip,phone,email
List of Items: name,sku,quantity

The idea is to produce each section of the document separately using the DataSonnet CSV plugin and then concatenate them using a function from the DataSonnet standard library. Start by declaring some helper functions to avoid duplicating the same code in many places:

local formatDate(date) = DS.LocalDateTime.format(date, "yyyy-MM-dd'T'HH:mm:ss", "MM/dd/yyyy HH:mm:ss");
local writeCSV(arr) = DS.Formats.write(arr, "application/csv", { "UseHeader": false });
local toArray(item) = if (std.isArray(item)) then item else [item];

Next, create the order header and bill to address lines:

local orderHeader = writeCSV([
                     payload.order["@id"],
                     formatDate(payload.order["@date"])
                    ]);
local billTo = writeCSV([
    payload.order.billTo.name["$"],
    payload.order.billTo.address1["$"],
    payload.order.billTo.city["$"],
    payload.order.billTo.state["$"],
    payload.order.billTo.zip["$"],
    payload.order.billTo.phone["$"],
    payload.order.billTo.email["$"]
]);

The shipments section is slightly more complicated. It will contain two nested loops, the outer loop over the shipments and the inner loop over the line items in each shipment. Use the std.foldl() function to iterate over the items and aggregate the results:

local shipments = std.foldl( //Loop over <shipment> elements
    function(aggregate, shipment)
        aggregate + [ //For each shipment
            writeCSV([ //Create shipment header line                                              
                shipment["@id"],
                formatDate(shipment["@date"]),
            ]) +
            writeCSV([ //Create ship to line
                shipment.shipTo.name["$"],                                      
                shipment.shipTo.address1["$"],
                shipment.shipTo.city["$"],
                shipment.shipTo.state["$"],
                shipment.shipTo.zip["$"],
                shipment.shipTo.phone["$"],
                shipment.shipTo.email["$"]
            ]) +
            writeCSV( //Items
                std.foldl( //Loop over items in the shipment
                  function(aggregate, item)
                      aggregate + [[
                        item["@name"],
                        item["@sku"],
                        item["@quantity"],
                      ]],
                  toArray(shipment.items.item),
                  [])
            )
         ],
      toArray(payload.order.shipment),
      []);

Finally, put the parts together:

std.join("", [
               orderHeader,
               billTo
             ] + shipments)

Be sure to select the output type as Text or Java (or if you are using DataSonnet in your code, set the output mime type to “text/plain”).

The resulting output is:

order12345,"04/11/2020 14:35:00"
"Kym Chillcot","6904 Vera Trail","White Plains",NY,10633,"(555) 018-0141","kchillcot0@x.example.com.com"
shipment1,"04/21/2020 09:10:00"
"Ava McGunley","5488 Katie Plaza","Las Vegas",NV,89105,"(555) 014-4721",amcgunley2@y.example.com
"Hand Sanitizer",hs123,2
shipment2,"04/23/2020 08:42:00"
"Ava McGunley","5488 Katie Plaza","Las Vegas",NV,89105,"(555) 014-4721",amcgunley2@y.example.com
"Toilet Paper",tp2pl976,12
"Face Mask",856326,10

I hope this tutorial was helpful to you. We love receiving suggestions, comments, and contributions from the open source community. If you have any questions you can visit the DataSonnet documentation to read more about DataSonnet formats and Standard Library functions. And, you can find the complete code on GitHub.

No Data Format Plugin? No MIME Type? No Problem! – Part 1