Creating optimized XML from table

[josemcobos]

Hello!

I’m trying to create optimized XML files from an excel table. My data source looks like this:

Column1 - Values: A ; A ; A ; A; B; B.
Column2 - Values: A1 ; A1 ; A2 ; A2; B1; B2.
Column3 - Values: A11 ; A12 ; A21 ; A22; B11; B21.

I would like to obtain an XML looking like this:

<root>
     <group Column1="A">
          <subgroup Column2="A1">
               <item>
                    <Column3>A11</Column3>
              </item>
               <item>
                    <Column3>A12</Column3>
              </item>
          </subgroup>
          <subgroup Column2="A2">
               <item>
                    <Column3>A21</Column3>
              </item>
               <item>
                    <Column3>A22</Column3>
              </item>
          </subgroup>
     </group>
     <group Column1="B">
          <subgroup Column2="B1">
               <item>
                    <Column3>B11</Column3>
              </item>
          </subgroup>
          <subgroup Column2="B2">
               <item>
                    <Column3>B21</Column3>
              </item>
          </subgroup>
     </group>
</root>

So far, I’ve done a Column to XML on Column1, Column2 and Column3 but that doesn’t really do the trick because it doesn’t optimise it and, after applying the XML Row combiner I end up with something like:

<name Column1="A" Column2="A1" Column3="A11"></name>
<name Column1="A" Column2="A1" Column3="A12"></name>

…

I’ve checked the XML Creator component from the Hub but I’m not too convinced either.

Any ideas on how to tackle this situation?

Many thanks for your help in advance!
J.

[josemcobos]

In case somebody arrives to this message in the future, I managed to solve it just using python.
I Created an Excel reader node, then a Python script one where I used the Pandas library to loop through the data and the ElementTree to create the XML elements. My results of the python code were a Pandas Dataframe that I transformed to a knime table using knio.Table.from_pandas(myDataFrame), and there I had a table with as many rows as elements my XML would have.
The next steps were just connecting the Python output to a String to XML node, its output connected to a XML Row combiner node and finally an XML Writer node.

Simpler said than done but still worth it

[ScottF]

Thanks for posting your solution, @josemcobos !