Import XML to SharePoint list with Flow and xpath

[Anonymous]

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>

[Anonymous]

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 · Accepted solution]

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:

The flow works successfully as below:

Best regards,

Kris

[Anonymous]

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?

[bbedingfield]

@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.

[jpd99]

@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]

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:

ID	Company ID	Person ID
4736	1495	1138
4752	2785
4753	7570

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

[joshlwulf]

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]

@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