web
You’re offline. This is a read only version of the page.
close
Skip to main content
Community site session details

Community site session details

Session Id : jrllQ6m1rZMc4GjfV+Ix7s
Power Automate - Building Flows
Unanswered

XML to Sharepoint - extracting layered information

Like (0) ShareShare
ReportReport
Posted on 7 Feb 2021 21:04:33 by

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:

DT5_0-1612731024628.png

Flow:

DT5_1-1612731047593.png

 

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

  • Paulie78 Profile Picture
    8,416 Moderator on 09 Feb 2021 at 12:29:27
    Re: XML to Sharepoint - extracting layered information

    I always find it easier, if possible to convert the XML to JSON. I did this with your XML like this:

    2021-02-09_12-27-03.jpg

    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.

     

  • tom_riha Profile Picture
    10,127 Most Valuable Professional on 08 Feb 2021 at 17:38:00
    Re: XML to Sharepoint - extracting layered information

    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"]')

     

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Announcing our 2025 Season 2 Super Users!

A new season of Super Users has arrived, and we are so grateful for…

Paul Stork – Community Spotlight

We are honored to recognize Paul Stork as our July 2025 Community…

Congratulations to the June Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Power Automate

#1
Michael E. Gernaey Profile Picture

Michael E. Gernaey 497 Super User 2025 Season 2

#2
David_MA Profile Picture

David_MA 436 Super User 2025 Season 2

#3
Riyaz_riz11 Profile Picture

Riyaz_riz11 244 Super User 2025 Season 2