cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Import XML to SharePoint list with Flow and xpath

Hi,

 

I've come so far in my flow that I've got the XML imported and trying to push the result to a SharePoint list using "apply to each".

 

I've used the expression "xPath(xml(body('HTTP')),'(/Party/Table/Id)')" in the apply to each action and that seems to work as it does 3 applies. Unfortunately it creates 3 identical items in my SharePoint list (new item) containing the first record in the XML, the 4736 by using the expression "xPath(xml(body('HTTP')),'string(/Party/Table/Id)')" as title. I would like to have 3 items in the SharePoint list 4736, 4752 and 4753 with selected columns (If I get the first right the rest should follow). The output from the Http body looks like this:

 

<?xml version="1.0" encoding="UTF-8"?>
<Party>
<Table>
<Id>4736</Id>
<CompanyId>1495</CompanyId>
<PersonId>1138</PersonId>
<Created>2009-12-31T22:02:52.493Z</Created>
<IsDeleted>false</IsDeleted>
</Table>
<Table>
<Id>4752</Id>
<CompanyId>2785</CompanyId>
<Created>2010-08-24T22:47:44.177Z</Created>
<IsDeleted>false</IsDeleted>
</Table>
<Table>
<Id>4753</Id>
<CompanyId>7570</CompanyId>
<Created>2010-08-24T22:47:44.177Z</Created>
<IsDeleted>false</IsDeleted>
</Table>
</Party>

1 ACCEPTED SOLUTION

Accepted Solutions
v-xida-msft
Community Support
Community Support

Hi @Anonymous,

 

Could you please share a screenshot of your flow's configuration?

Do you want to get the value of <Id> tag from the output of HTTP body?

 

The string function within Xpath expression only find the value of first node which matches the path that you specified. I afraid that there is no way to achieve your needs using Xpath expression in Microsoft Flow currently.

 

I have made a test on my side and please take a try with the following workaround:

  • Add a proper trigger, here I use Flow Button trigger.
  • Add a Compose action, Inputs set to the XML data that you mentioned as below:

 

<?xml version="1.0" encoding="UTF-8"?>
<Party>
<Table>
<Id>4736</Id>
<CompanyId>1495</CompanyId>
<PersonId>1138</PersonId>
<Created>2009-12-31T22:02:52.493Z</Created>
<IsDeleted>false</IsDeleted>
</Table>
<Table>
<Id>4752</Id>
<CompanyId>2785</CompanyId>
<Created>2010-08-24T22:47:44.177Z</Created>
<IsDeleted>false</IsDeleted>
</Table>
<Table>
<Id>4753</Id>
<CompanyId>7570</CompanyId>
<Created>2010-08-24T22:47:44.177Z</Created>
<IsDeleted>false</IsDeleted>
</Table>
</Party>

 

 

  • Add a Variables-> "Initialize variable" action, Name set to IdArray, Type set to Array and Value set to empty.
  • Add a Compose 2 action, Inputs set to following formula:

 

xpath(xml(outputs('Compose')),'/Party/Table/Id')

 

  • Add a "Apply to each" action, input parameter set to output of "Compose 2" action.
  • Within "Apply to each" action, add a "Append to array variable" action, Name choose IdArray, Value set to following formula:

 

replace(replace(decodeBase64(item()?['$content']),'<Id>',''),'</Id>','')

 

  • Under "Apply to each" action, add a "Compose 3" action, Inputs set to IdArray variable.
  • Add a "Apply to each 2" action, input parameter set to output of IdArray variable. Within "Apply to each 2" action, add a "Create item" action, the XML_ID field set to following formula:
item()

Image reference:9.JPG

 

10.JPG

 

11.JPG

The flow works successfully as below:14.JPG

 

13.JPG

 

12.JPG

 

 

Best regards,

Kris

 

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

8 REPLIES 8
Anonymous
Not applicable

I think I need to make use of variables between getting the XML via the HTTP action and the "Apply to each" action

v-xida-msft
Community Support
Community Support

Hi @Anonymous,

 

Could you please share a screenshot of your flow's configuration?

Do you want to get the value of <Id> tag from the output of HTTP body?

 

The string function within Xpath expression only find the value of first node which matches the path that you specified. I afraid that there is no way to achieve your needs using Xpath expression in Microsoft Flow currently.

 

I have made a test on my side and please take a try with the following workaround:

  • Add a proper trigger, here I use Flow Button trigger.
  • Add a Compose action, Inputs set to the XML data that you mentioned as below:

 

<?xml version="1.0" encoding="UTF-8"?>
<Party>
<Table>
<Id>4736</Id>
<CompanyId>1495</CompanyId>
<PersonId>1138</PersonId>
<Created>2009-12-31T22:02:52.493Z</Created>
<IsDeleted>false</IsDeleted>
</Table>
<Table>
<Id>4752</Id>
<CompanyId>2785</CompanyId>
<Created>2010-08-24T22:47:44.177Z</Created>
<IsDeleted>false</IsDeleted>
</Table>
<Table>
<Id>4753</Id>
<CompanyId>7570</CompanyId>
<Created>2010-08-24T22:47:44.177Z</Created>
<IsDeleted>false</IsDeleted>
</Table>
</Party>

 

 

  • Add a Variables-> "Initialize variable" action, Name set to IdArray, Type set to Array and Value set to empty.
  • Add a Compose 2 action, Inputs set to following formula:

 

xpath(xml(outputs('Compose')),'/Party/Table/Id')

 

  • Add a "Apply to each" action, input parameter set to output of "Compose 2" action.
  • Within "Apply to each" action, add a "Append to array variable" action, Name choose IdArray, Value set to following formula:

 

replace(replace(decodeBase64(item()?['$content']),'<Id>',''),'</Id>','')

 

  • Under "Apply to each" action, add a "Compose 3" action, Inputs set to IdArray variable.
  • Add a "Apply to each 2" action, input parameter set to output of IdArray variable. Within "Apply to each 2" action, add a "Create item" action, the XML_ID field set to following formula:
item()

Image reference:9.JPG

 

10.JPG

 

11.JPG

The flow works successfully as below:14.JPG

 

13.JPG

 

12.JPG

 

 

Best regards,

Kris

 

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

That was great!

Should be an easier way to transform XML-data into a SharePoint list. With excel it is done in seconds.

 

Now I just need the other columns and get the data directly from the xml-source instead of the compose action. Looking at the item() on the title column, how should I think regarding getting data for the rest of the columns since I have more info per record/item. I cant use item() on the other fields, right?

@Anonymous 

 

I know this post was over a year ago, but did you ever solve getting the rest of the data to post from the XML? Do you remember how? I'm trying to do the same thing.

@v-xida-msft - Kris, this is a great help - thank you.  I wondered whether you could please offer any guidance on adding a second column to the array (and hence sharepoint)?

 

I have used your suggestion above to successfully extract the currency codes from the link below and add them to a sharepoint list;

 

http://www.hmrc.gov.uk/softwaredevelopers/rates/exrates-monthly-0120.xml

 

Ideally I would like a sharepoint list that has;

 

AUD  1.78

BRL   5.29

.

.

.

etc...

 

Better still it'd be helpful to be able to ONLY capture AUD, CHF, EUR, USD!

 

Many thanks in advance.

Anonymous
Not applicable

I have noticed that several people on this thread have been asking for how to bring in other columns into the list. I am currently in need of this answer as well - @v-xida-msft are you aware of any way to handle this? From your example I would like to pull all the data in to see a list like this:

 

IDCompany IDPerson ID
473614951138
47522785 
47537570 

 

Hopefully, you are able to provide some guidance here, I am only able to apply one of the Array sets at a time.

 

Thank you in advance!

 

- Tyler

I have the same question. It's surprising to me that there is no easy way to parse XML in Flow. The really long process described above just to pull in one "column" seems really inefficient. Now I want to also bring in other child elements for each ID and I don't see how that would even be possible. 

Anonymous
Not applicable

@joshlwulf I have great news for you! I have raised this question on a few different threads and have the answer for you! Please see here for the answer:

 

https://powerusers.microsoft.com/t5/Building-Flows/Apply-to-Each-action-SP-Create-Item-Only-allows-f...

 

You must make the output of the final 'Apply to Each' the first XPath, from there you can access each variable or compose you create from within the 'Create Item' for the SharePoint list! If you have any questions, please ask them on the new thread since it currently has traction.

 

I am going to tag those above who were asking for the same answer in case they are still looking for a solution as well.

@jpd99 | @bbedingfield 

Helpful resources

Announcements
Power Automate News & Announcements

Power Automate News & Announcements

Keep up to date with current events and community announcements in the Power Automate community.

Power Automate Community Blog

Power Automate Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Users online (3,556)