Creating column names with XPATH Node

KNIME Analytics Platform
3

Here I have an alternative solution which is a bit tricky:
First, use a String Manipulation node to modify your XML file to make the element names be values.
E.g.:
If this is the XML:

<?xml version='1.0' encoding='UTF-8'?>
<root>
    <item>
        <id>0001</id>
        <idBoard>0001</idBoard>
    </item>
    <item>
        <id>0001</id>
        <idBoard>0001</idBoard>
    </item>
    <item>
        <id>0001</id>
        <idBoard>0001</idBoard>
    </item>
</root>

Use this expression:
regexReplace($json$, "(?:<)(.*)(?:>.*<\\/.*>*?)", "<item>$1</item>")
And then convert the output of the String Manipulation node to XML, so you will have this:

<?xml version='1.0' encoding='UTF-8'?>
<root>
    <item>
        <item>id</item>
        <item>idBoard</item>
    </item>
    <item>
        <item>id</item>
        <item>idBoard</item>
    </item>
    <item>
        <item>id</item>
        <item>idBoard</item>
    </item>
</root>

Now use a XPath node to extract the new values which are indeed the element names of the original XML file. Use Multiple rows option in XPath and use a RowID node after this XPath node to reset the Row IDs.
Now let’s get back to the original XML file. Use a XPath node to extract the values. The column names do not matter. Use Multiple columns option. Then use Extract Column Header node and uncheck the “Use new output column names”. Then use a Transpose node and connect the first output port of the Extract Column Header node the this node and execute the node. Then use another RowID node to reset the Row IDs.
Now, join the output of the two RowID nodes based on the Row IDs. Use a Insert Column Header node and pass the output of the joiner node to the second input port and the second output port of the Extract Column Header node to the first input port of the Insert Column Header node. Now in the configuration window of the Insert Column Header node assign the column containing the old column names to the “Lookup Column” and the column containing the new names to the “Value Column”.

That’s it.
Here is an example workflow for you:

Untitled
Untitled836×389 76.1 KB

XPath_Column.knwf (50.8 KB)

Best,
Armin

2 Likes