Excel to XML using groupBy and JSON to prepare the structure

[mfabian]

Hi all. I’m working on a transformation from Excel data to XML. XML structure is complex, it is related to expenses on budgets and projects. I’m transforming each column to JSON and GroupBy to create the structure and the items that are collections (lists). When the structure is ready I transform everything to JSON and then to XML. Everything is almost done, but the problem is that where there are lists/collections, the JSON and XML is taking each list as separate item, so instead of having a list with items, I’m getting several lists with the items.

Attach you find the knar fileAwards.knar (50.6 KB)
and a folder output-awards where there is the expenditures-target.xml (XML to generate) and Row0.xml (the xml generated by the workflow). The expected expenditure structure is:

• financialFundings
  • financialFunding1
    • budgets
      • budget1
        • expenditures
          • expenditure1
          • expenditure2
      • budget2
        …
  • financialFunding2
    • budgets
      …

But, the structure I got is:

• financialFundings
  • financialFunding1
    • budgets
      • budget1
        • expenditures
          • expenditure1
        • expenditures
          • expenditure2
    • budgets
      • budget2
        …
• financialFundings
  • financialFunding2
    • budgets
      …

I’m assuming when I’m using the GroupBy, the lists are created like this. So, I wonder if there is something to do in the GroupBy or should I use JSON Transformer or String Manipulation to correct this. Just looking for a good solution.

Thanks.

Mario

[ScottF]

Hi @mfabian -

I’m no JSON expert, but it looks like where things are breaking down structure-wise is at the JSON Transformer node. I don’t think there’s something different you should do with the GroupBy, since options are limited for that configuration, and your other fields apart from Expenditures seem to be handled OK.

Maybe there is a String Manipulation solution, but I would defer to others more knowledgeable than I am on JSON structuring. Hopefully someone will chime in.