CSV to nested XML

(Im a beginner) Having problems finding how to achieve that several subnodes are shown under a parent node, without repeating the parent node.
My input would be a csv with 8 col, one is header:

name,projectNo,invoiceDate,productNo,description,qty,price,vatPercent
Free text field,12345,12.06.2022,1,Free text description1,4,385,0
Free text field,12345,12.06.2022,1,Free text description2,5,247,0
Free text field,31312,18.07.2022,1,Free text description3,2,192,0
Free text field,31312,18.07.2022,1,Free text description4,3,914,0

Wanted output XML is:

<?xml version="1.0" encoding="utf-8"?>
<orders>
  <order>
    <name>Free text field</name>
    <projectNo>12345</projectNo>
    <invoiceDate>2022-12-06</invoiceDate>
    <orderLines>
      <orderLine>
        <productNo>1</productNo>
        <description>Free text description1</description>
        <qty>4</qty>
        <price>385</price>
        <vatPercent>0</vatPercent>
      </orderLine>
      <orderLine>
        <productNo>1</productNo>
        <description>Free text description2</description>
        <qty>5</qty>
        <price>247</price>
        <vatPercent>0</vatPercent>
      </orderLine>
    </orderLines>
  </order>
  <order>
    <name>Free text field</name>
    <projectNo>31312</projectNo>
    <invoiceDate>2022-18-07</invoiceDate>
    <orderLines>
      <orderLine>
        <productNo>1</productNo>
        <description>Free text description3</description>
        <qty>2</qty>
        <price>192</price>
        <vatPercent>0</vatPercent>
      </orderLine>
      <orderLine>
        <productNo>1</productNo>
        <description>Free text description4</description>
        <qty>3</qty>
        <price>914</price>
        <vatPercent>0</vatPercent>
      </orderLine>
    </orderLines>
  </order>
</orders>

What I’m getting with Rename Cols transformation f.i. ordeLines.orderLine.productNo is a repeat of everything for every orderline.

@Kjell I was able to do it.

Here is the comparison in BeyondCompare:

Here is the .transform file:

Kjell.transform (4.6 KB)

You might have to tweak it a bit if there are more than 2 orderLine per orderLines.

See also:

1 Like

Magical. I even managed to follow what you did and recreate it for a file input. Thank you.
But yes, the catch is that orderLine can vary, there could be one, there could be 50, and the number will vary between projectNo.
I see it’s the “Remove Cols” transformation that handles that. I dont know how long that can be :slight_smile: Just keep hiding name, projectNo,invoiceDate except the top ones.
Could be good enough to work, definately good enough to go from trial to purchasing a license. :heavy_dollar_sign: :+1:

1 Like

I think you will have to work out what the most number of orderLine is per orderLines[1], then set up a .transform that handles that. It should be able to handle less.

[1]You can probably just load a .csv and use a Count transform on ‘projectNo’ to work this out.

image

1 Like