How to Parse XML in Power Automate using XPath

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

1. So we need to convert the XML data to a valid XML form. That is why we use xml().
2. Then we get the outputs from the XML_Data compose action.
3. 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:

• Power Automate Editor Update
• Getting started with Power Automate Expressions
• Learning Power Automate – part 1
• Learning Power Automate – part 2