XPath for namespaced XML attributes in Power Automate?

Question

I receive an XML file by email each day, and am trying to write a Flow / Power Automate to parse the XML, get 3 variables from it, and append these to a table in Excel. The XML file has below structure, its loaded into the query with the identifier InputXML

<?xml version="1.0" encoding="UTF-8"?>
<ifir:inter_fund_investor_report ifir:creation_date_time="2021-02-24T21:16:01"
                                 ifir:report_id="102068045"
                                 xmlns:ifir="nzl:org:fsc:XMLSchema:InterfundInvestorReport:v2.0">
  <investor ifir:investor_name="client name" ifir:investor_id="client123">
    <fund ifir:currency="USD" ifir:fund_valuation_date="2021-02-23" ifir:fund_name="Fund Name" ifir:fund_id="fundcode" ifir:is_fund_a_PIE="Y">      
      <balances>        
        <investor_units_held>123456</investor_units_held>
        <total_units_on_issue>999999999</total_units_on_issue>
      </balances>
      <prices>
        <base_price>1.060</base_price>
        <entry_price>1.0650</entry_price>
        <exit_price>1.055</exit_price>
      </prices>
      <totals ifir:fund_allocation_date="2021-02-23"/>
    </fund>
  </investor>
</ifir:inter_fund_investor_report>

I have been able to get the base_price by using the expression:

xpath(xml(outputs('InputXML')),'//base_price')

I cannot however get the fund_name or the fund_valuation_date out from the XML.

Is someone able to help me with the XPath expression to get these two? The XML file currently only has one fund_name but could in the future have more, so I should also make the base_price query conditional on that fund name if that is possible?

kjhughes · Answer 1 · 2021-03-09T04:20:26.687

There is no indication in the Power Automate documentation for XML that it supports XPath querying of namespaced XML components.

You could work-around such a limitation by testing the local name of the attribute:

//fund[@*[local-name()='fund_name' and .='Fund Name']]/prices/base_price

This selects all base_price element children of prices element children of those fund elements whose fund_name attribute value is 'Fund Name', regardless of the namespace on the fund_name attribute.

XPath for namespaced XML attributes in Power Automate?

1 Answers1

See also