Update XML with some value in a tag based on ID

The issue where I am stuck is like this…
I have got two xml’s ( generated from Archi ( Data Modelling Tool ) ). I am using XML reader to read from both and XPATH to get ‘identifier’ and ‘name’ Lets say identifier is ‘ia123’ and name is ‘Business Services’. Then doing ‘name’ and doing full antijoin to find missing values on both sides. Assume this is not found in other xmls and the row found in other as in the other xml there is a name ‘Business Services ** Updated **’ Now, task is to update the first xml based on ID i.e. ‘ia123’ and update the name there with ‘Business Services ** Updated **’. Any ideas will save my life :-(. Many thanks in advance

@takbb can you please help?

Hi @sudhirkumra ,
Pleased to “meet” you on linkedin! :slight_smile: and thanks for reposting the question here, as hopefully you will get assistance from more people.

So to start with can I just ask a few questions to build a fuller picture.

You have two XMLs
one contains something like this?

<entities>
  <entity>
     <identifier>ia123</identifier>
     <name>Business Services</name>
   </entity>
</entities>

and you want to update this to be

<entities>
   <entity>
      <identifier>ia123</identifier>
      <name>Business Services ** Updated **</name>
   </entity>
</entities>

…and there could be other updates that you have to do, and you need to write out the whole xml again at the end.

… is all the data that you are using for lookup contained in the other xml, or do you have another data source too?

2 Likes

Thanks Mate.

Yes, I want to update the values as you understood correctly.

Writing a new XML is not going to be an easy one as it’s an open XML generated from ARCHI will relationships, Processes and all. So, since we already have that XML that we used for XML reader, just need to update one filed there i.e. name based on the id search. No other updates. Basically I have a delta XML that I want to update the original with.

1 Like

Ok, thanks for the additional info.

So from your delta xml, you have extracted a table of identifiers and names to be applied to the primary xml?

I may (will!) have to do some research on this :slight_smile:

There is an XSLT node that looks enticing and can possibly assist here, (according to a post I found from 2016! ).

That is where I would focus at the moment. I haven’t used XSLT for a few years though and I’ve not previously used that node, but I suspect it may well feature in the solution, unless it turns out this requires a python or java snippet.

1 Like

@kowisoft , any help!

1 Like

Hi @sudhirkumra , I have been having a play…

Maybe this can be adapted for your needs

The two “Memo Reader” components are simply there to make it easy for me to write in some demo text. In this case some xml that looks like this:

and an XSLT that looks like this:

The Table Creator contains a list of identifiers with the new names to be applied

The adjoining String Manipulation node creates additional XSLT templates for each of these. The template tells the XSLT processor to replace the “name” value for the given “identifier”

join(
  "<!-- Template to update <name lang=\"en\"> tag for elements with identifier '",$identifier$,"' -->",
  "<xsl:template match=\"element[@identifier='",$identifier$,"']/name[@lang='en']\">",
  "  <name lang=\"en\">",$name$,"</name>",
  "</xsl:template>"
	)

and the resultant templates look like this:

and then these get appended to the original XSLT so they make one large template like this:

When that XSLT is applied to the XML by the XSLT node, it transforms the XML from what I showed above, like this:


xml replacer.knwf (111.2 KB)

btw chatgpt was once again my friend when it came to generating some usable xslt!! :wink:
Here is my chatgpt conversation, including my mistakes along the way…

2 Likes

Oh Dear, Many thanks for the quick turn around. I cannot see the the workflow running as memo reader is probably an extension? and we are not allowed to install extensions unfortunately. But I’ll try to read from your screenshots and apply what you have suggested. Thanks Again! I’ll come back tomorrow if I need any further assistance.

Only thing to check here is that the original XML can be very complex and BIG with about 40k lines and this subset of xmls will be about 50 lines and will this just replace that part of XML in the original? Anything else I can use instead of memo reader to make this wf of yours working?

Hi @sudhirkumra , the Memo Reader is just a component I wrote (built using standard KNIME nodes) so it’s not using any extensions. You should be able to run it just fine. If not, I’ll upload a version that had the demo data in a Table Creator or a text file.


Edit: here is a version using plain Table Creators instead :slight_smile:

xml replacer - using table creator.knwf (21.7 KB)

nb, as the ideas for the workflow were put together quickly, there may be some improvements that can be made to the XSLT. I don’t have an idea of how the XSLT node will perform with 40k lines, as I’ve not used it before. Let’s see how you get on, and if this idea needs any refinements (or a new idea! :wink: )

2 Likes

Cheers @takbb Been on trainings since Friday. I’ll be on it tomorrow and see if you can make it to work with my big XML…TYSM

@takbb I have been trying to create a string manipulation node to form XSLT but it is giving an error “Ambiguous return type! Use String() or another function from the Convert Type”

Hi @sudhirkumra , that message is a generic one from String Manipulation whenever it cannot automatically work out from the expression what the return type of the data is, even though it might appear “obvious”…

e.g.

Depending on your expression, you can normally wrap it in string( ) [note: function name is all lower case, and there must not be any whitespace between the function name and the opening parenthesis]

e.g.

Of course the specifics of your String Manipulation expression may differ, so if this doesn’t immediately help, please upload a copy of the expression you are trying to use, and when uploading, please remember to use the “preformatted text” button on the forum so that the forum software doesn’t try to modify the uploaded expression

2 Likes
##REPLACEMENT TEMPLATES##

above is what I am trying to do in a String Manipulation node

I’m not sure I follow… are you trying to create that xlst text as a string in String Manipulation node?

In which case you’d have to write it out as a string concatenation, which can be done, but is a bit fiddly which is why I used Table Creator (and originally wrote that Memo Reader component).

You’d need to write it as something like this:

join(
"<!-- identity_with_update.xslt -->",
"<xsl:stylesheet version=\"1.0\" xmlns:xsl=\"http://www.w3.org/1999/XSL/Transform\">",
"<!-- Identity transformation for all elements and attributes --><xsl:template match=\"@*|node()\">",
"<xsl:copy>",
"<xsl:apply-templates select=\"@*|node()\"/>",
"</xsl:copy>",
"</xsl:template>",
"##REPLACEMENT TEMPLATES##",
"</xsl:stylesheet>"
)

I don’t know if that’s exactly right. You need to make sure every double quote is escaped, and life is generally too short, which is exactly why I didn’t do it that way in the first place. :wink:

@takbb the example workflow you created looks good but the issue is that the Table Creater you have at the top for original values has only got two identifiers! In my case, it will be a big XML. and I need to find the identifier and replace the value. How can I achieve this? Basically, XSLT node will have input as the big XML with loads of things! If I connect this to XSLT node, it says, input document is not a stylesheet! This original xml is an Archimate Open Exchange file :frowning:

Index of /xsd/archimate/3.1/examples (opengroup.org)

Our XML is combination of all, Elements, Properties, Views, Relationships etc.

if anyone can of any help to achieve this, will really appreciate that!

Hi @sudhirkumra , in the example my table has two identifiers, but it should work equally for 3, 4 or 100 identifiers, so I think I need to understand better what the problem is. It’s difficult to work out without seeing the actual workflow you are using.

The XSLT node takes an XML as the top input and and XSLT as the lower input

So can you elaborate more on when it says the input document is not a stylesheet. How have you got it configured?

The stylesheet itself would need to be adjusted to work with the actual xml, as the stylesheet I included was only a demo for the sample/demo xml.