Got it. Here’s the solution - definitely some advanced Expression Language usage is required but I did learn quite a bit doing it:
12465_xml-to-flatten-csv_2022_05_20.slp (13.6 KB)
Since you want to keep it to two rows, we drop the second JSON Splitter from my first solution above - and I’ve renamed the first Splitter to Split on KSSK:
So we have data that looks like this now:
Since you want CHARACT values like PM_ARB_A_AC_MAN to become keys, we have to do an Unpivot (i.e. making { "key": "someKey", "value": "someValue" } become { "someKey": "someValue" })
I used the following expression to do that (note: yes, this should really be a new Snap😞
$AUSP.API_VAL_R.map((elem) => { [elem.CHARACT]: elem.VALUE } )
mapped to a temporary field $unpivoted.
I also took the opportunity to flatten the structure of the other fields in the JSON in preparation for CSV:
What’s important here is that I moved them under a placeholder $root object. That is because I’ll want to merge contents of the $unpivoted array into $root whilst also dropping the array itself. It’s easier to do it this way rather than trying to modify the root $ document and also remove the array at the same time.
The last tricky step is the merge:
Again I use the Expression Language - this time the object.extend(target:data) Object function:
$root.extend(...$unpivoted)
The triple-dot is the Spread Operator/Syntax:
Spread syntax (
...) allows an iterable such as an array expression or string to be expanded in places where zero or more arguments (for function calls) or elements (for array literals) are expected, or an object expression to be expanded in places where zero or more key-value pairs (for object literals) are expected.
In other words, I can pass an array of objects of key/value pairs, and extend() will combine the key/value pairs into a single object. And since I am extending the $root objects, those k/v pairs will be combined with the others!
Binding the result of the expression to $ means I’ll have two flat objects ready to be used for CSV.
Finally, you need to manually specify the CSV Formatter’s Headers to use, since the documents have different fields (the default is to use the headers from the first document).
Enabling the “Use Header” checkbox and listing the fields from the input document:
should output your desired CSV content:
"AEDAT","EQASP","EQUNR","ERDAT","ERNAM","FLEET_NUM","CLINT","KLART","MAFID","PM_ARB_A_BSTNR","PM_ARB_A_BSTPO","PM_ARB_A_AC_MAN","PM_ARB_A_ADVERT_FRAMES","PM_ARB_A_CCTV","PM_ARB_A_CURRENT_REG_DT","PM_ARB_A_EMISSIONS","PM_ARB_A_ENG_EURO_NORM","PM_ARB_A_GPS","PM_ARB_A_MAN_DT"
"2021-10-01","E","000000000010014880","2017-09-18","DB21016","SKBMI823EJ","0000027802","002","O","10934902","140",",",",",",",","
"2021-10-01","E","000000000010014880","2017-09-18","DB21016","SKBMI823EJ","0000027803","002","O",",","WEBASTO ATHEMIA","No","No","26.01.2017","16.08.2019","EURO VI","No","26.01.2017"
