Only in December for LazyAdmin readers: Get 20% discount on your first invoice of Cloudways hosting with code LA20. Read more here

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 an Rest API that was returning XML data insteads 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 to the basics of the XPath expressions works 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.

Flow XML sample data

Now XPath is really powerfull. 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, lets first take a close look at the XPath syntax.

In the table below I have listed a couple of the most common used expressions and what the result would be based on the example XML

ExpressionResult
/catalog/bookSelect 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/bookThe same result is as above, but catalog can now also be a child of another element.
//authorSelect all author elements independent of their location
book//authorSelect all author elements that is a child of the book element, no matter where they are located.
//@idReturn 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
Microsoft Flow Xpath expression and xml

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.

Power Automate xpath expression

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.

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.

Flow XPath empty brackets

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 sensative and if it isn’t working, double check your paths.

You may also like the following articles:

Get more stuff like this

IT, Office365, Smart Home, PowerShell and Blogging Tips

I hate spam to, so you can unsubscribe at any time.

Leave a Comment

0 Shares
Tweet
Pin
Share
Share