Hi everyone,
I have searched the forums so apologies if this has already been answered.
I am trying to populate a sharepoint list from an xml file with grandchild information using power automate. I have managed to do this fine when stripping back the xml file... and can extract <amount> and <narrative> for each record using this xml code:
<?xml version="1.0" encoding="UTF-8"?>
<transactions>
<transaction type="C" code="TFR" status="C">
<amount currency="GBP">1.00</amount>
<narrative>Trans 1</narrative>
</transaction>
<transaction type="C" code="TFR" status="C">
<amount currency="GBP">2.00</amount>
<narrative>Trans 2</narrative>
</transaction>
</transactions>
Output:
Flow:
The first apply to each xpath formula is:
xpath(xml(outputs('Compose')),'//transaction')
The Id and CompanyId respectively are:
xpath(xml(item()),'string(/transaction/amount)')
xpath(xml(item()),'string(/transaction/narrative)')
Under create item, I'm taking the outputs from Id and CompanyId.
My question is: what do I need to change in the power automate formula to carry on extracting <amount> and <narrative> when the xml file is structured like this:
<?xml version="1.0" encoding="UTF-8"?>
<statement xmlns="[web address removed]" xmlns:xsi="[web address removed]" xsi:schemaLocation="[web address removed] [web address removed]" type="EXPORT" customerId="00000" date="2021-02-03">
<account currency="GBP">
<name>Example Name</name>
<number type="STANDARD">000000</number>
<source type="SC">000000</source>
<transactions>
<transaction type="C" code="TFR" status="C">
<amount currency="GBP">1.00</amount>
<narrative>Trans 1</narrative>
</transaction>
<transaction type="C" code="TFR" status="C">
<amount currency="GBP">2.00</amount>
<narrative>Trans 2</narrative>
</transaction>
</transactions>
</account>
</statement>
I have tried various structures but each time it returns blank data.
Thanks for your time
I always find it easier, if possible to convert the XML to JSON. I did this with your XML like this:
The expression I used was:
json(xml(concat('<statement ', substring(outputs('XML'), indexOf(outputs('XML'), 'type="EXPORT"')))))
So it strips everything from the start of your XML until type=EXPORT and prepends it with <statement.
This makes it into XML that the JSON function will accept and you can then work with it much easier.
Hello @DT5 ,
I'd say the xpath(...) expression probably doesn't like the xmlns the xml uses. To ignore the xmlns you can address the element using local-name().
xpath(xml(outputs('Compose')),'//*[local-name()="transaction"]')
Michael E. Gernaey
497
Super User 2025 Season 2
David_MA
436
Super User 2025 Season 2
Riyaz_riz11
244
Super User 2025 Season 2