I prefer to use JSON data in a Flow because it’s easy to work with. But you can’t always choose your source data and this time I had a Rest API that was returning XML data instead of JSON.
If you are working with JSON, then you can simply use a Parse JSON action to extract the data, with XML we will need to take a couple of extra steps and use XPath to extract it.
I will first show you how the basics of the XPath expressions work and later how to use it in combination with an API.
Parsing XML with XPath
So XPath is a generally used query language to extract data (nodes) from an XML document. In Power Automate we can use the XPath function inside a Compose action.
For this example, I will be using a part of the sample XML file from Microsoft.
<catalog> <book id="bk101"> <author>Gambardella, Matthew</author> <title>XML Developer's Guide</title> <genre>Computer</genre> <price>44.95</price> <publish_date>2000-10-01</publish_date> <description>An in-depth look at creating applications with XML.</description> </book> <book id="bk102"> <author>Ralls, Kim</author> <title>Midnight Rain</title> <genre>Fantasy</genre> <price>5.95</price> <publish_date>2000-12-16</publish_date> <description>A former architect battles corporate zombies, an evil sorceress, and her own childhood to become queen of the world.</description> </book> </catalog>
For testing purposes, I am using a flow with a manual trigger. First, we create a Compose action and add a part of the Books XML in the inputs. Always try to rename your actions to something meaning full, in this case, XML Data for example.
Now XPath is really powerful. We can for example get all books, and process each book later in the flow. But we can also get with one function all publishing dates.
Selecting nodes with XPath
So before we continue with the Flow in Power Automate, let’s first take a close look at the XPath syntax.
In the table below I have listed a couple of the most commonly used expressions and what the result would be based on the example XML
Expression | Result |
---|---|
/catalog/book | Select all nodes with the name book from the root element catalog. If you start the path with a / then it’s always the absolute path |
catalog/book | The same result is as above, but catalog can now also be a child of another element. |
//author | Select all author elements independent of their location |
book//author | Select all author elements that is a child of the book element, no matter where they are located. |
//@id | Return all id attributes (in this case the id’s of the book element |
//book[1] | Select the first book. Use [last()] to select the last element. |
//book[price>20.00] | Select all book elements with a price element higher than 20.00 |
Using XPath in Power Automate
To use XPath in a Flow we create a new Compose action. Click on the inputs field and select expression.
The structure of the function is as follows:
xpath(<XML data>, <xpath expression>)
So to select all book elements from the XML we can use the following expression:
xpath( xml( outputs('XML_Data') ), '//book' )
- So we need to convert the XML data to a valid XML form. That is why we use
xml()
. - Then we get the outputs from the XML_Data compose action.
- And in the last part, we have our XPath expression
//book
Tip
If you are wondering why my expression editor looks different then yours, then check out this article on how to enable the new Expression Editor in Power Automate
The result of the above expression is all book elements like this:
<book id="bk101"> <author>Gambardella, Matthew</author> <title>XML Developer's Guide</title> <genre>Computer</genre> <price>44.95</price> <publish_date>2000-10-01</publish_date> <description>An in-depth look at creating applications with XML.</description> </book>
If you want to see the result of your expression you can simply add a compose action and use the expression XML(item())
in it.
Iterating over the results
The Get all books actions that we created will return an array with the results. To process the books, we can use an Apply to each action.
Inside the for each, we add another Compose action to get the author for example. In this case, I don’t want to author element, but the actual value. We use the string
function to get the actual value of the node:
xpath( item(), 'string(//author)' )
We use the // here, but you can also use /book/author
or book/author
. In this case, it would all return the same results.
Using an XML API in Power Automate
With Flow, you can easily get data from an API with the HTTP action (which requires Flow Premium). The HTTP action will return the results of the API call in it’s body output.
So to use this XML data we can use the following XPath expression:
xpath( xml( body('HTTP') ), '/catalog/books' )
We convert the output of the API to valid XML and can use our XPath expression on it to get the correct elements that we need.
Counting XML Rows in Power Automate
There are two options to count XML rows in Power Automate. We can use XPath or count the length of an XPath result.
When using XPath, we basically use the same selection methods as before, but wrap it into a count function:
xpath( xml( outputs('XML_Data') ), 'count(//author)' )
Length is a standard function that can be used to measure the length of any result in Power Automate.
In this case, we measure the length of an XPath expression used earlier.
length(outputs('Get_all_books'))
Common issue with XPath in Flow
So when I started with using the XPath expression I ran into a couple of pretty much common issue that can be really frustrating if you don’t know about it.
Click to download
If you got the expression wrong, you won’t see an error. For example, the expressions are case sensitive. So //Books
won’t return any data. When you test your flow you will see in the input and output of the XPath expression Click to download.
If you download or open the file, all you see are empty brackets []. This basically means that your expression isn’t working as you expected, it didn’t find any results.
If the expression is working, then in the outputs fields you will something like this:
[ { "$content-type": "application/xml;charset=utf-8", "$content": "PGJvb2sgaWQ9ImJrMTAxIj4NCiAgPGF1dGhvcj5HYW1iYXJkZWxsYSwgTWF0dGhldzwvYXV0aG9yPg0KICA8dGl0bGU+WE1MIERldmVsb3BlcidzIEd1aWRlPC90aXRsZT4NCiAgPGdlbnJlPkNvbXB1dGVyPC9nZW5yZT4NCiAgPHByaWNlPjQ0Ljk1PC9wcmljZT4NCiAgPHB1Ymxpc2hfZGF0ZT4yMDAwLTEwLTAxPC9wdWJsaXNoX2RhdGU+DQogIDxkZXNjcmlwdGlvbj5BbiBpbi1kZXB0aCBsb29rIGF0IGNyZWF0aW5nIGFwcGxpY2F0aW9ucyANCiAgICAgIHdpdGggWE1MLjwvZGVzY3JpcHRpb24+DQo8L2Jvb2s+" }, { "$content-type": "application/xml;charset=utf-8", "$content": "PGJvb2sgaWQ9ImJrMTAyIj4NCiAgPGF1dGhvcj5SYWxscywgS2ltPC9hdXRob3I+DQogIDx0aXRsZT5NaWRuaWdodCBSYWluPC90aXRsZT4NCiAgPGdlbnJlPkZhbnRhc3k8L2dlbnJlPg0KICA8cHJpY2U+NS45NTwvcHJpY2U+DQogIDxwdWJsaXNoX2RhdGU+MjAwMC0xMi0xNjwvcHVibGlzaF9kYXRlPg0KICA8ZGVzY3JpcHRpb24+QSBmb3JtZXIgYXJjaGl0ZWN0IGJhdHRsZXMgY29ycG9yYXRlIHpvbWJpZXMsIA0KICAgICAgYW4gZXZpbCBzb3JjZXJlc3MsIGFuZCBoZXIgb3duIGNoaWxkaG9vZCB0byBiZWNvbWUgcXVlZW4gDQogICAgICBvZiB0aGUgd29ybGQuPC9kZXNjcmlwdGlvbj4NCjwvYm9vaz4=" }, ]
This is basically two XML documents in an array. To see the content you can use a compose action and the expression XML(item())
.
Wrapping up
I hope this article helped you to get started with using XPath in Power Automate. Keep in mind that the expressions are case sensitive and if it isn’t working, double-check your paths.
You may also like the following articles:
This really helped me a lot and I appreciate the post! I do have one question though, how do you extract the id, like id=”bk101″?
Firstly this is Awesome I’ve been trying to find a page like this forever. It has been hugely helpful, at risk of being greedy though, is there a way to return the following result?
//title1 & author1
Just create two compose actions
hi Ruud,
i am using two separated compose actions in order to use this XPATH expression:
compose 1: xpath(xml(outputs(‘Get_file_content_compose’)),’//invoicenumber[1]/text()’)
compose 2: xpath(xml(outputs(‘Get_file_content_compose’)),’//invoiceamount[1]/text()’)
Since i am doing this in two separated compose actions i am not able to add this in one row, at the moment it is reflecting as two excel rows.
Can anyone please advise how to make one xpath expression to extract ‘invoicenumber’ and ‘invoiceamount’ ?
and if i use 1 xpath expression instead of 2, will i be able to update and view in one excel row instead of 2?
How to count number of rows in the XML File in Power Automate?
You have two options:
xpath(
xml(
outputs('XML_Data')
),
'count(//author)'
)
or
length(outputs('Get_all_books'))
Keep in mind that the count start with 0. So 3 books, results in a count of 2. (Record 0, 1, and 2)
How to handle if one of the node is not present in xml example not present , then flow will fail , how to handle this in xpath
Can you show me an example? Because I just tested it with the example in this article, removing the author from one of the books. But it seems to work fine. The \\author will get all authors, and if I get the books first with \\books and then a foreach over the results to get the author it also works fine. It will just show a blank record for the book without an author.