Power Automate Desktop & getting XML values

Question

I been struggling to get data from xml file in Power Automate Desktop.

I get XML files which contain multiple invoices and inside each invoice there is the invoice data:

<INVOICE>
        <HEADER>
          <INVOICE_ID>1019526</INVOICE_ID>
          <PROCESS_CODE>00</PROCESS_CODE>
          <INVOICE_TYPE UNTDID_CODE="381">00</INVOICE_TYPE>
          <METHOD_OF_CHARGE>00</METHOD_OF_CHARGE>
          <SUBJECT>Creditnote</SUBJECT>
          <CREDIT_INVOICE_NUMBER>1013996</CREDIT_INVOICE_NUMBER>
        </HEADER>
        <SUMMARY>
          <ROWS_TOTAL>
            <AMOUNT SIGN="-" VAT="EXCLUDED">8123.25</AMOUNT>
          </ROWS_TOTAL>
        </SUMMARY>
</INVOICE>
<INVOICE>
        <HEADER>
          <INVOICE_ID>1029526</INVOICE_ID>
          <PROCESS_CODE>00</PROCESS_CODE>
          <INVOICE_TYPE UNTDID_CODE="381">00</INVOICE_TYPE>
          <METHOD_OF_CHARGE>00</METHOD_OF_CHARGE>
          <SUBJECT>Invoice</SUBJECT>
          <CREDIT_INVOICE_NUMBER>1023996</CREDIT_INVOICE_NUMBER>
        </HEADER>
        <SUMMARY>
          <ROWS_TOTAL>
            <AMOUNT SIGN="+" VAT="EXCLUDED">764.25</AMOUNT>
          </ROWS_TOTAL>
        </SUMMARY>
</INVOICE>
<INVOICE>
        <HEADER>
          <INVOICE_ID>1119526</INVOICE_ID>
          <PROCESS_CODE>00</PROCESS_CODE>
          <INVOICE_TYPE UNTDID_CODE="381">00</INVOICE_TYPE>
          <METHOD_OF_CHARGE>00</METHOD_OF_CHARGE>
          <SUBJECT>Creditnote</SUBJECT>
          <CREDIT_INVOICE_NUMBER>1013956</CREDIT_INVOICE_NUMBER>
        </HEADER>
        <SUMMARY>
          <ROWS_TOTAL>
            <AMOUNT SIGN="-" VAT="EXCLUDED">722.25</AMOUNT>
          </ROWS_TOTAL>
        </SUMMARY>
</INVOICE>

What I would need to do is identify if the XML contains invoices where SUBJECT = "Creditnote" and AMOUNT SIGN = "-". If one or more is found then I would want to change the INVOICE_TYPE to "04" in all these invoices. And lastly save it as new file.

Would I get help how to build this? Thanks!

I have succesfully identified the first invoice in the xml. But how to identify these all and then change the INVOICE_TYPE?

XPath is a query tool, even if you can get the values as need be, you’ll then need another way to change them. XPath won’t do that for you. Given you’re using PAD, you need to think outside the box, the entire desktop environment is at your disposal. — Skin, Mar 22 '23 at 18:44
What sort of volumes are we taking about? Like, how many invoices per file? Also, the structure of the XML looks quite balanced, is that a fair statement? i.e. Does each inordinate ve record always have the same amount of child elements? — Skin, Mar 22 '23 at 18:58

Expiscornovus · Answer 1 · 2023-03-22T15:03:59.013

0

You should be able to use XPath for this. Have a look at the Xml actions in PAD.

There should be Execute XPath expression action available which you can use for this: https://learn.microsoft.com/en-us/power-automate/desktop-flows/actions-reference/xml#executexpathquery

I am not an expert in XPath, but below is a query ~~is the first criteria of your filter~~ which seems to work
Btw, added the INVOICES root node to turn it into valid xml.

//INVOICE[.//SUBJECT[text() = "Creditnote"] and .//AMOUNT[@SIGN = "-"]]

edited Mar 22 '23 at 15:03

answered Mar 22 '23 at 13:38

Expiscornovus

1,222
1
3
7

Hi and thanks! This produces only the HEADER data, but not the complete INVOICE data. As the AMOUNT SIGN is not under HEADER so it will not be included in the results. SO no luck yet. – USSSR Mar 22 '23 at 14:09
For the sign you can use this part `//AMOUNT[@SIGN = "-"]/ancestor::INVOICE`. You only need to add those two together with an operator. Again, no Xpath expert here. – Expiscornovus Mar 22 '23 at 14:17
I think I have found a XPath query which should combine the two: `//INVOICE[.//SUBJECT[text() = "Creditnote"] and .//AMOUNT[@SIGN = "-"]]`. Also learned some new Xpath expression language myself today, lol – Expiscornovus Mar 22 '23 at 15:02
Hi Thanks for trying but that combination does not produce the complete invoice, it produces only the header data. I have also tried but no combination this far has produced the complete data from invoice. – USSSR Mar 22 '23 at 17:53
Like I mentioned in my first response. I have added a single invoices node to to XML. Maybe that is the difference compared to the XML you are using? – Expiscornovus Mar 22 '23 at 18:00

score 0 · Answer 2 · answered Mar 22 '23 at 14:10

0

You can do it with Powershell :

using assembly System 
using assembly System.Linq
using assembly System.Xml.Linq 

$inputFilename = "c:\temp\test.xml"
$outputFilename = "c:\temp\test1.xml"
$xDoc = [System.Xml.Linq.XDocument]::Load($Filename)

$invoices = $xDoc.Descendants("INVOICE")


foreach($invoice in $invoices)
{
   $id = $Invoice.Descendants("INVOICE_ID")[0].Value;
   $subject = $invoice.Descendants("SUBJECT")[0].Value
   if($subject -eq "Creditnote")
   {
      $amount = $invoice.Descendants("AMOUNT")[0]
      $sign = $amount.Attribute("SIGN").Value
      if($sign -eq "-")
      {
         Write-Host "Changing Invoice" $id
         $invoice_Type = $invoice.Descendants("INVOICE_TYPE")[0]
         $invoice_Type.SetValue("04")
      }
   }
}

$xDoc.Save($outputFilename)

answered Mar 22 '23 at 14:10

jdweng

33,250
2
15
20

Thanks but no experience on Powershell. How shoud this work out? Can I use this in power automate desktop so that I add there a "run Powershell script" and add the code there? If I do it like that it doesnt produce the output file? – USSSR Mar 22 '23 at 17:51
1) Put into a file with a ps1 extension. 2) Open Powershell by pressing start button and typing Powershell 3) Then type path of ps1 file like .\test1.ps1. You can use CD (change directory) and DIR just like cmd.exe. Or use FILE explorer to go to folder and then hold down ALT and SHIFT Keys and right click mouse. there will be an option "Open Powershell Here". – jdweng Mar 22 '23 at 18:53
You can run Powershell from Power Automate also : https://learn.microsoft.com/en-us/power-automate/desktop-flows/how-to/convert-data-powershell – jdweng Mar 22 '23 at 19:49

score 0 · Answer 3 · answered Mar 23 '23 at 03:54

The Powershell option is a strong one but if you're wanting to use something a little more pedestrian due to confidence levels then you can always do this in Excel via formulas.

Given you're in PAD, you have the ability to make use of the entire Windows desktop and all of the apps on offer.

That being the case, my suggestion is to amend your flow and perform the following steps ...

Open the XML file and copy the entire contents to the clipboard.
Open Excel and copy/paste special values into cell A1.
Now add the following formula into cell B1 ...

=IF(ISERROR(FIND("</INVOICE_TYPE>",A1)),A1,IF(AND(NOT(ISERROR(FIND("Creditnote",A3))),NOT(ISERROR(FIND("SIGN=""-""",A8)))), SUBSTITUTE(A1,TEXTAFTER(TEXTBEFORE(A1,"</"), ">") & "</","04</"),A1))

Fill down B1, this should drop the formula right down to the bottom of the last line of the XML.
Now select the entire B column and copy and paste that data to the clipboard or an open version of notepad.
Save the file and you have an updated version of the XML that you're after.

This is an example of the workbook and the result ...

Yellow - Denotes lines that didn't need to change.

Green - Lines that, based on your rule, needed to be changed.

Power Automate Desktop & getting XML values

3 Answers3