You have data stored in XML and you want to use this to create list items in SharePoint and you thing that Microsoft Flow might be able to help you. You couldn’t be more right. Flow can help you, but is it something that is easy to do? No! Although once you know how to do it it is quite easy.
My starting point
I am using the following XML to set a variable in my flow.
Now I first want to collect an array of list items. I’m using xpath in a Compose step to do this.
xpath(xml(variables('XML')),'//XML/ListItems/ListItem')
Then I take the output from the Compose action and walk through the array of list items.
I hear you ask: “What is that $content?”. I’m simply taking each item in the Apply to Each and grab the $content.
To really understand this you might want to look at the output from the first Compose actions.
now you can see for each item we have a $content element in json. To get to this data we can set the expression on the Compose 2 action to the following
items('apply-to-each')?['$content']
but now we still have the unreadable text starting with “PE…”. to convert this into a readable XML, we will need to use the base64ToString() function.
base64ToString(items('apply-to-each')?['$content'])
Now we have the Xml for each list item:
Now all we need to do is repeat our selves inside the Apply to Each for each of the list items.
This time I’m avoiding the base64ToString function by using the text() function instead
xpath(Xml(outputs('Compose_3')),'//ListItem/*[self::Title or self::Description]/text()')
No when we look at the output from my final compose step we have an array of values that we can use to create our list items in SharePoint or any other place where you would like to use this data:
If you still want to keep the field names form the original Xml that we started with then you could adjust the expression and not use the text() function. However I thought from an educational point of view the showing the different options in this post would be more useful.
Pieter Veenstra
Is your business still running on paper trails, sprawling Excel files, or ageing Access databases? There’s a better way — and I can show you exactly what it looks like. I’m the Technical Director of Vantage 365, a Microsoft solutions consultancy working with clients across the UK, the Netherlands, and worldwide. For over 30 years I’ve been turning messy, manual business processes into clean, automated systems that save time, reduce errors, and give teams the visibility they need to make better decisions. SharePains is not just any blog run by a Microsoft MVP. Have you ever used Try-Catch in Power Automate? The original post about Try-Catch in Power Automate can still be found on this site, https://staging.sharepains.com/2018/02/07/try-catch-finally-in-power-automate-flow/ Or have you ever used the Pieter’s method to avoid variables and speed up your flows? https://staging.sharepains.com/2020/03/11/pieters-method-for-advanced-in-flows/ You can contact me using contact@staging.sharepains.com
Leave a Reply
You must be logged in to post a comment.