Demonstration workflow for KNIME XML Generator Components.
This is a companion discussion topic for the original entry at https://hub.knime.com/-/spaces/-/latest/~aKi1R5HoVLUP-v-d/
Demonstration workflow for KNIME XML Generator Components.
I have a hard time generating a control table for a complex xml file ( No surprise - I’m a noob)
The end goal is to generate an xml payment file to upload to our bank that will initiate the transfer of money from us (Debtor) to a beneficiary (Creditor)/
The bank has provided the attached XSD and sample file.
Could you please run it through your workflow and give me hint?
One issue I think is they use the same tag name for different purposes. Like is used for the Dbtr ID, Crtr ID, DbtrAgent ID, etc
Bank schema as txt.xsd.txt (43.7 KB)
Sample payment file as txt.xml.txt (5.1 KB)
Hi @hlombard , thank you for your interest in the XML generator components. The aim of them is to be able to produce a “control file” which the different components can then use to turn your input data into the required xml.
It’s been a while since I last looked at this, so hopefully I get it right.
So, what I have done so far is taken your xsd file and read that in with the XML Reader which passed it to the Create XML Generator Control Table From XSD component.
The purpose of this component is to use the XSD to evaluate the structure of the XML document, and create the structural parts of the control table (the bits that say which XML element is a parent/child of other XML elements and so on)
The next step would be to put sample XML into the lower XML Reader which will then pass it to the Create XML Generator Data Map Table from XML component
The purpose of this is to build the parts of the control table which determine which data items are used to populate each xml element. At the moment, the supplied XML can be read in, but will not supply the information it requires. What you need to do is look at your KNIME data table that will supply the data to the output XML.
What this XML file needs to contain is the column names that will be used for each element within the resultant XML.
If you look at the demo that you linked to, you will see the data table at the very top, and the XML “Data Map” being read in by the XML Reader at the bottom. The XML file needs to contain the column names in the format shown here:
So each column name from your data table, written with the same precise case needs to be placed at the relevant places in your XML document. You don’t need to do the entire document, but you do need to have covered all of the elements, so basically you find a subset of the XML file which is complete and valid, and in place of the data values that you currently have, place the column names from the data table, enclosed in $ symbols, as per the above example.
Once you have that, we should be able to generate an initial control table. It is unlikely it will then be perfect for your needs, as there may be additional things to define. Ideally then you will also have a sample data table with a handful of (non-sensitive/anonymized) data records, which can be used to test the generator.
Using the same tag name for different purposes shouldn’t be a problem, but there’s always the possibility that I’ve overlooked something. Each tag is given an internal name as well as the display value, so if something is used in different places we create two tags. Hopefully it will be clearer once we have some sample data and the XML mapping to work with.
If you are able to upload both of those, then I can look at assisting further. Hope that helps!
I just realised that the sample XSD isn’t the XSD for the sample payment XML , as there are elements in the XML that don’t appear in the XSD, so how does the sample payment file fit into this?
The Data Mapping XML must conform to the XSD as it defines where the data elements will come from.
I appreciate your comprehensive response.
The XML and XSD I got from HSBC bank. I also wanted to check if the xml complies with the xsd, so I tried a few online xml vs xsd validators, but get an error on the first line. I think the namespace provided (xmlns=“urn:iso:std:iso:20022:tech:xsd:pain.001.001.03”>) cannot be found.
But you would think a large bank like that would provide valid files?
I also tried to generate an xsd from the provided file and got a much simpler xsd. But I’d rather build to the full xsd as we may expand the use of the xml file upload in future.
But I am still surprised you found elements in the xml that do not exist in the xsd? Can you provide an example?
Hi @hlombard , yes that would seem surprising but not totally unusual, though it’s always possible that I’m getting a little rusty!
I didn’t find the following, and didn’t spot references to other schemas.
<CstmrCdtTrfInitn> (immediately below
<Document> ) or
<PmtTpInf>). There may be others too, but I was just doing a cursory manual check.
One thing I should make a mention of, is that the xml generation components here on the KNIME Hub were built by me initially to make my own life easier in generating xml, and I posted them in case anybody else might find it useful. I’ve since only received feedback from a couple of people (both were positive but in the process I added further refinements).
The addition of the xsd processing resulted from one of the KNIME community members asking if it could be done, and I understand that it worked for their process, but of course I haven’t heard any more, and so it’s been almost a year since I looked at this.
The xsd that you have here is much more extensive than the one that was used before, so it is possible that we may come across “new” things that need some additional modifications. If you are happy to work on that basis, then I’m happy to try to assist but I cannot always guarantee that my response will be immediate, as I do this in my spare time. Thought I should mention this though. If you are at the proof of concept stage, or slightly relaxed then all good!
Whilst I should get a notification of your replies to me, if you put @takbb in your messages, I’ll definitely get a notification. Oddly, although you posted initially regarding my component, it was only by chance I saw your message as I didn’t get a notification even though you posted in “my” component space. Not the first time that’s happened!
Oh, and welcome to the KNIME community!
I understand you do this as a hobby.
I am willing to pay your rate for help. If we can structure it as Training - I have a budget for that. Let me know your rate.
BTW, I’m an accountant and also is doing this because I like the pain!
Currently I am working on your suggestion to changing the sample xml to refer to $ColunNames$ from the data I will be feeding it.
I have asked the bank for a new Sample xml and the related xsd.
I see the latest schema for ISO20022 is pain.001.002.0011 Payments Initiation / Customer Credit Transfer Initiation, but big banks move slow. They might have moved from 002 to 003!
pain.011 contains the missing elements you noticed, but look more complicated
I’ve uploaded my version of your demo here
Hi @hlombard , thanks for this upload. That’s very useful and gives me a chance to look further.
I’m going to need to get back to you as I can see that the xsd parser I built on request before only works with complex types that are “in line” in the element definitions, rather than out-of-line. A solution to this could take a while to resolve and I’m going to be short of time this week. I will endeavour to look at it as soon as I can, as clearly that is a direction it needs to go in, but thought it best to manage expectations!
You may be interested in the following thread, as this is how the xsd implementation came about.
and also the following “documentation” I started to put together which isn’t quite complete, but I admit I have had to resort to reading it to remind myself…
KNIME XML Generation Components-takbb.pdf (1.2 MB)
also available from here
Thanks for the links. I have previously read everything you have on this topic!
I proposed your Knime solution to our IT team, but they shot me down!
They insist on building their own solution - which I am sure will not materialize any time soon.
So don’t go to any trouble just for me.
No problem, and thanks for letting me know.
I’ll probably revisit this topic at some point when I have more time, and take a look at the xsds you uploaded anyway to further build on the capabilities. It’s only through having real-world examples that features get added. It would be nice one day if there were a reasonably generic “out of the box” solution, as I might need it again myself one day.