Inserting Image URLs from Excel into XML File of Product Data

[Yannick_Jasper]

Hello,

a customer of us want us to implement image URLs in an PRICAT data (xml structure).
Since our Inventory managment system only supports internal links to images, which cannot be used from outside our system, I started uploading images to Postimage (website to create public links to images) and made a excel where every product has its own row of EAN/Imagenames/ImageURL1/ImageURL2 …

Now comes the tricky part and since i never realy worked with xml in KNIME im kinda lost.

The xml structure repeats for every product with all the product details like this:
25032652312020035.xml (3.5 KB)

There is also a header for the xml but im not to worried about that, because I could simply cut this out and put it back in when im finished adding the image data.

So my goal is, to match the EAN in the xml and add the “Bild URL 1-7” if there is content available to the xml. Naming of the xml tag isnt relevant because the data will get processed for the customer anyways so the data simply need to be unifyed before sending it out.

Thanks for any advice and solution, this would save us a lot of costs to development in out system.

221012_MASTERLISTE_EAN_ART-NO_DATEINAME.xlsx (104.2 KB)

[armingrudd]

Hi @Yannick_Jasper,

You can read the XML file with the XML Reader node and then extract the code by using the XPath node.
Then you have the code from the xml and xlsx files so you use the Joiner node to find the matching ones.
The you have several options how to add the URLs to your xml. One way would be to aggregate the URL columns, convert the aggregated column to xml and add it at the end of the xml.

I wasn’t sure if the “referenceNumber” should match “EAN - COD” and didn’t find any value to match. If you provide me with a sample xml and xlsx with at least one match, I can share an example workflow.

[Yannick_Jasper]

Hey @armingrudd

thank you for the quick response.
The reference number will not work for the joiner. The Tag 4051244531610 will work with matching xml with xlsx.

I will have a try on the solution u provided.

Thanks a lot

[Yannick_Jasper]

Hey @armingrudd,

reading the xml with the XPath worked out just fine.
However I´m not sure how to handle the “Header” of my file, since this will only appear at the Top of the xml for once and the products will repeat many times.
So my question at this point would be:
How can I distinguish between a single occurrence and multiple occurrences of variables when reading in the file? Because the “EdiPricatHeader” appears only once at the Top and “EdiPricat” will repeat for every product.

Adding the URL´s with the joiner worked as well, but getting the XML back together is where im stuck now. I thought about a Loop since the “Column to XML” can only do it for one Parameter.
But i never used loops so if you could share me your solution I would be pretty thankful.

This is the workflow with the used data at the current state:
XML Add Image.knar (382.3 KB)

Thanks for any advice

[armingrudd]

I just added a few nodes to your workflow to generate your desired XML. This is just an example approach to achieve what you desire. I think you (or someone in the community) can work on it a bit more to optimize the workflow:
XML Add Image.knar (402.1 KB)
URLs are added under <images> at the end of each <EdiPricat> element.

[Yannick_Jasper]

Hey @armingrudd

thanks a lot for the given solution. This made a lot of steps clear and is working wunderful

Great work
Thanks
Yannick