Parse XML with same node at different depths

[jwallATL]

Is there a way to only grab a single instance of an XML node at a higher level than a child node? See XML example below.

<INVOICE>

<TAX_AMT>44.58</TAX_AMT>

<LINE>

       <TAX_AMT>22.48</TAX_AMT>

</LINE>

</INVOICE>

I want to get the 22.48 and disregard the 44.48. The XML could have multiple lines as well.

[Rhys_Cooper]

hi  @jwallATL. please can you upload an example file and i will get a solution across.

[jwallATL]

Please find attached sample XML. In this it would be TOTAL_TAX_AMOUNT of 22.80 that I want to get.

[Rhys_Cooper]

Hi @jwallATL  thanks providing that, I have presupposed that when dealing with this within an Alteryx data stream the xml is contained within a single field. As such, at the start of the solution attached I read the xml file as a csv to stop alteryx auto parsing it, and concatenated the separate lines with a \n new line space character as the concatenation value such that it all sits within one field. Since you want the first tax amount value it seems, i use the summarize tool to find the min value of the line numbers in combination with a join tool to isolate that row. If it was the second line value you were after just change this to max. This is finished with a select tool choosing only the record ID and the desired Field.

I made the workflow purposefully generalised to enable you to adapt it to your exact use case.If you are reading it directly as an xml file, you can modifity the start. if you find this has helped solve your problem, please feel free to mark this as the accepted solution. Otherwise let me know what changes you wish to make and we can revisit this!

[jwallATL]

That you for the workflow. I wanted to make sure I clarified that this XML will come in as a single in the data as it will be the result of a WebService call so it will all be in a DownloadData colum with the XML in a single cell.

[Rhys_Cooper]

ah a single cell, that previously provided solution wont work for this scenario then, Change of tact, since it is the indented/nested line you want the tax amount from, lets try a regex approach instead. let me know if this is fits the bill! You might have to tweak the "2" value in the regex to match the amount of indentation in your actual xml. This means 2 or more instance of whitespace character in regex lingo. Again that first container isnt nessecary when you plug this in, just map the regex field to your xml field.

[jwallATL]

Thank you and I did try this however the data in the DownLoadData record comes through without formatting. it would come back from the WebService like below and attached.

<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"><soap:Body><taxCalculationResponse><OUTDATA version="G"><REQUEST_STATUS><IS_SUCCESS>true</IS_SUCCESS><IS_PARTIAL_SUCCESS>true</IS_PARTIAL_SUCCESS></REQUEST_STATUS><COMPANY_ROLE>S</COMPANY_ROLE><EXTERNAL_COMPANY_ID>1005608663-EU40</EXTERNAL_COMPANY_ID><INVOICE><INVOICE_DATE>2024-11-13</INVOICE_DATE><INVOICE_NUMBER>TEST123456</INVOICE_NUMBER><TOTAL_TAX_AMOUNT>45.60</TOTAL_TAX_AMOUNT><LINEID="001"><COMMODITY_CODE>85391000100</COMMODITY_CODE><GROSS_AMOUNT>120.00</GROSS_AMOUNT><LINE_NUMBER>10</LINE_NUMBER><TOTAL_TAX_AMOUNT>22.80</TOTAL_TAX_AMOUNT></LINE><LINEID="002"><COMMODITY_CODE>85391000133</COMMODITY_CODE><GROSS_AMOUNT>100.00</GROSS_AMOUNT><LINE_NUMBER>20</LINE_NUMBER><TOTAL_TAX_AMOUNT>20.00</TOTAL_TAX_AMOUNT></LINE></INVOICE></OUTDATA></taxCalculationResponse></soap:Body></soap:Envelope>

[jwallATL]

@Rhys_Cooper  any thoughts on the last message?

[Rhys_Cooper]

Hi @jwallATL. third time lucky hey, eager to get this solved for you. Heres a different approach based off your last message. Ive annotated the tools to explain the steps and included the output with some presumptions of what fields you would want to keep. you can see the 22.80 ( and 20.00 for the next commodit) included in the output screenshot. Is this what you were after?