Modify XML attribute value in dynamic child xml tags

[jaydeep2]

I have an xml which may have multiple child tags. There may be 1 or more child xml tags. Based on a condition of a xml tag value of a specific xml tag, I need to modify (find/replace) value of another xml tag.

Example below:

<InvoiceDocument>
  <Invoice>
 
    <InvoiceNumber nil="False">299995395</InvoiceNumber>
    <NetSum nil="False">109.33</NetSum>
    <SupplierCode nil="False">10080673</SupplierCode>
    <CodingRows>
      <CodingRow>
        <AccountCode nil="False">990080</AccountCode>
        <AccountName nil="False">Prepaid - Adv</AccountName>
        <Text1 nil="False"></Text1>
        <Text2 nil="False">1000</Text2>
        <Text3 nil="False">12242099-XY300</Text3>
      </CodingRow>
      <CodingRow>
        <AccountCode nil="False">990080</AccountCode>
        <AccountName nil="False">Prepaid - Adv</AccountName>
        <Text1 nil="False"></Text1>
        <Text2 nil="False">1000</Text2>
        <Text3 nil="False">12242099-XY810</Text3>
      </CodingRow>
     </CodingRows>
    <InvoiceRows />
  </Invoice>
</InvoiceDocument>

CodingRow can be multiple child elements, in my example above there are 2.

Based on the value of AccountCode of the CodingRow - if the value is 990080 then AccountCode value to be set to XY300 which is from Text3 (portion after hyphen) of the same CodingRow. Also the AccountName to be replaced by a set value as per AccountCode. The same logic needs to be applied to all toe Coding Rows.

Expected Output is:

<InvoiceDocument>
  <Invoice>
 
    <InvoiceNumber nil="False">299995395</InvoiceNumber>
    <NetSum nil="False">109.33</NetSum>
    <SupplierCode nil="False">10080673</SupplierCode>
    <CodingRows>
      <CodingRow>
        <AccountCode nil="False">XY300</AccountCode> <!-- 990080 replaced by XY300 from Text3-->
        <AccountName nil="False">AccountName1</AccountName> <!-- Prepaid - Adv replaced by a specific name "AccountName1" for the account-->
        <Text1 nil="False"></Text1>
        <Text2 nil="False">1000</Text2>
        <Text3 nil="False">12242099-XY300</Text3>
      </CodingRow>
      <CodingRow>
        <AccountCode nil="False">XY810</AccountCode> <!-- 990080 replaced by XY810 from Text3-->
        <AccountName nil="False">AccountName2</AccountName><!-- Prepaid - Adv replaced by a specific name "AccountName2" for the account-->
        <Text1 nil="False"></Text1>
        <Text2 nil="False">1000</Text2>
        <Text3 nil="False">12242099-XY810</Text3>
      </CodingRow>
     </CodingRows>
    <InvoiceRows />
  </Invoice>
</InvoiceDocument>

Is it something that can be achieved via Knime?

Thanks for your time and suggestions.

[jaydeep2]

I did come up with Python Script solution to my problem which works in outside Python IDE however I am not able to make it work within Knime Python Script node.

I am unable to read XML column from previous node to parse an xml within the Python node. Dataframe shows the type as “Series” so I converted to string and it shows me value starting with RowID and other few prefixed values then the actual XML, which is causing Invalid XML error by Python XML Parser.

Can someone help me with how to read XML column into Python DF?

Here’s my Python code I am trying to run within Python Script

import knime.scripting.io as knio
#import pandas as pd
import xml.etree.ElementTree as ET

# Get the table as Pandas DataFrame
df = knio.input_tables[0].to_pandas()

#tree = ET.parse(df["XML"])


xml_in = df['XML'].to_string()

#print(xml_in)
xml_path = df['FileName_Read'].to_string()

tree = ET.parse(df['FileName_Read'])

#root = ET.fromstring(xml_in)

for coding in root.findall('.//CodingRows/CodingRow'):
    Acct = coding.find('AccountCode').text
    #print(Acct)
    if Acct == '130080':
        NEWACCT = coding.find('Text3').text
        AFTERSPLIT = NEWACCT.split("-",1)[1]
        print(AFTERSPLIT)
        coding.find('AccountCode').text = AFTERSPLIT

Here’s the screenshot of the Knime Python script configuration with the error.

[armingrudd]

Hi @jaydeep2 and sorry for the late reply,

I built an example workflow to do what you asked for with no code:

Modify XML – armingrudd

I don’t think this is the most efficient way to do this and I jsut tried to be quick.
Maybe you or someone else from the community can optimize it.

Please let me know if you have more questions.

[jaydeep2]

What a genius approach to solve this… I will definitely use this in my solution! thank you for your time and help!