How to include namespace information and prefix on some elements on a XML column?

[mfabian]

Hi all. I’m working on an integration project and we are defining best approach to generate an XML file from an Excel data file. XML needs to have namespaces and two elements with v3 prefix. I created two approaches:

1. Using Excel Reader and the Column to XML nodes per each column in the table and XML combiner to create a complex node valuevalue2. I was able to create the XML including the namespace attributes and prefix

2. Using Excel Reader and JSON nodes to create the structure and convert it to XML. All the XML structure was created except the namespaces and the prefix on the elements required. What is the best way to do this?

Image of the workflow and knar file are attached. On the output-users directory, there is the Row0-Target.xml with the file that needs to be generated.

Pure Workflows.knar (45.6 KB)

[armingrudd]

Hi @mfabian and welcome to the KNIME community forum,

For the first approach you could use the Column List Loop Start node to transform columns to XML. Flow variables can be used to control names, attributes,…

In the second approach, you can add the namespaces in XML Row Combiner as you have done in the first approach but you cannot put the prefixes for “firstname” and “lastname” items in the column name since while converting JSON to XML the node removes any non-letter characters.
There is a workaround:
Use a String Manipulation node with this expression after the XML Row Combiner:
regexReplace($XML Complete$, "(firstname|lastname)", "v3:$1")

And then Use the String To XML node.

P.S. Your first approach (when used with a column list loop) looks much more straightforward than the second approach.

[mfabian]

@armingrudd

regexReplace($XML Complete$, “(firstname|lastname)”, “v3:$1”)

Hi @armingrudd, thank you very much for your quick response. Your suggestion for second approach worked and actually, I had to use another node because after the XML Row Combiner added the namespaces, and empty xmlns=“” appear in each user element. So, I remove it.

I will try your suggestion on the first approach. I was looking for a way to avoid creating manually the elements when the table from the Excel has 30 or 50 columns because it would be a complex / not elegant design.

Thank you again.

[mfabian]

Hi @armingrudd, regarding the suggestion with first approach, I created the workflow although not sure about how to control the prefix for elements and in the loop. I suppose a conditional or rule is necessary to include this information in the attributes properties of the Column to XML node. Or apply the same solution with String Manipulation that was discussed before. Here is the image of the workflow as reference. Not sure how to apply flow variables on this case. What do you think?

[mfabian]

Hi @armingrudd, I appreciate if you have additional comments on my last post.

Thanks!

[armingrudd]

Sorry @mfabian. I missed your reply.

Here is an example:

Pure Users.knwf (61.5 KB)

[mfabian]

Hi @armingrudd, THANK YOU!!! It worked smoothly!