Using XPath in your flow

Did you know that in Flow you can query XML text with XPath? And since JSON content can be converted to XML format, XPath can actually be used with JSON content also.

Why would I want to do anything with any XPath?

For example, if

• You want to extract information from a JSON/XML message in a way that is otherwise not possible in Flow
• You want to extract information from a JSON/XML message in a way that cannot be done in Flow without an Apply to each loop (=slow)
• You want to improve the performance of Flow and it is possible with XPath (Paul Murana’s blog has a good example of such a situation).

Let’s get to know this wonderful XPath with an example. In this post, we solve the same problem with 3 different kinds of flow. One of them utilizes XPath.

Example – Team members

We use Flow to retrieve the team hierarchy of organization X from system Y. Hierarchy is returned in a slightly special format, with team member and team leader pairs.

[
   {
      "teamattribute": {
         "teamlead": "Otto@forwardforever.com",
         "teammember": "Timo@forwardforever.com"
      }
   },
   {
      "teamattribute": {
         "teamlead": "Otto@forwardforever.com",
         "teammember": "Ilkka@forwardforever.com"
      }
   },
   {
      "teamattribute": {
         "teamlead": "Heidi@forwardforever.com",
         "teammember": "Jouko@forwardforever.com"
      }
   },
   {
      "teamattribute": {
         "teamlead": "Heidi@forwardforever.com",
         "teammember": "Lasse@forwardforever.com"
      }
   }
]

We want to create a comma-separated list containing Otto’s team members. How we can do that?

In our example, the output data is ready in a variable  teamJSON ).

Traditional solution

This is probably how most of us would approach the problem.

A variable (TeamMembers) is created to collect the team members.

Let’s start by filtering from JSON the objects where Otto works as a team leader.

The condition is a bit more complicated than usual because the teamlead attribute is inside the teamattribute attribute.

item()?['teamattribute']?['teamlead']

We are filtering with this part of the object:

After filtering, the JSON only contains records where Otto is the team lead.

Next, we use the Apply to each action to go through the result set and add each team member (plus a comma) to the variable.

This way we have the team members separated by commas in the variable. You just have to remember to remove the last useless comma from the end of the string.

But…

It takes 4 seconds to go through six team members. What if there are many teams and all of them have dozens of members? This is slow.

In addition, the loop quickly generates a lot of Power Platform requests.

Could we do this without the loop?

Solution without a Apply to each loop

Let’s start just like before, filtering the JSON to show only Otto’s team members.

But now, using the select action, a table is created from the result set, with only team members listed.

Output looks like this.

We are already close!

We want the end result to be a string with email addresses separated by commas.

So we convert the result set into a string and replace the extra characters with an empty character.

replace(replace(replace(string(body('Select_-_Only_teammember')),'[',''),']',''),'"','')

And we are ready!

The performance is completely different compared to the original approach.

Solution using XPath

Finally, let’s approach the same problem in a slightly different way. with XPath.

XPath can be used to do all kinds of fun things with XML content. So first we convert our JSON content to XML format.

For this to be successful, there must be a root element in the JSON.

The edited JSON looks like the following.

{
  "root": {
    "values": [
      {
        "teamattribute": {
          "teamlead": "Otto@forwardforever.com",
          "teammember": "Timo@forwardforever.com"
        }
      },
      {
        "teamattribute": {
          "teamlead": "Otto@forwardforever.com",
          "teammember": "Ilkka@forwardforever.com"
        }
      }]

    }
}   
  

We can change this to XML with the xml function.

xml(outputs('Compose_-_Add_root'))

Result looks like this.

<root>
  <values>
    <teamattribute>
      <teamlead>Otto@forwardforever.com
      </teamlead>
      <teammember>Timo@forwardforever.com
      </teammember>
    </teamattribute>
  </values>
  <values>
    <teamattribute>
      <teamlead>Otto@forwardforever.com
      </teamlead>
      <teammember>Ilkka@forwardforever.com
      </teammember>
    </teamattribute>
  </values>
</root>

We want to extract from the XML the team members whose team leader is Otto. How to do this?

With XPath.

Let’s open http://xpather.com/ and copy the XML we have created into the left.

Now we can try different XPath commands. For example, //teammember returns all team members.

XPath may seem confusing at the beginning. Fortunately, there are several tutorials online on how to use it. For example W3schools.

After a short practice, we find a command that returns the members of the desired team.

//teamattribute[teamlead="Otto@forwardforever.com"]/teammember

Then we execute the same XPath command in flow.

xpath(outputs('Compose_-_Convert_to_XML'),'//teamattribute[teamlead="Otto@forwardforever.com"]/teammember')

However, it is quite a surprise when the end result of the operation looks like this.

XPath command has produced the correct result. It just needs to be changed into a readable format.

It can be done by making a selection for the result set, where the lines are converted to plain language using the base64ToString function.

There they are!

The end is then string wrangling. First, we replace the start and end tags ( <teammember> and </teammember> ) with an empty string.

replace(replace(base64ToString(item()?['$content']),'<teammember>',''),'</teammember>','')

After that, the result is converted to a string and the rest of the extra characters (“”, [, ]) are removed.

replace(replace(replace(string(body('Select')),'[',''),']',''),'"','')

Ready!

We made flow in parts so that its structure would be easier to understand. In practice, we only need two actions.

Select, where we

• generate the modified JSON
• convert it to XML format
• do an XPath query

xpath(xml(json(concat('{ "root": {"values": ', variables('teamJSON'), ' }}'))),'//teamattribute[teamlead="Otto@forwardforever.com"]/teammember')

After that, the string is trimmed to its final form with the compose action.

Performance

Finally, let’s compare the performance of these three different approaches. We use JSON having 36 team members in Otto’s team.

In the traditional way (loop) it takes 16 seconds to create an email list!

When there is no loop used, end result is generated in less than a second.

With XPaths:

Power Platform requests are generated the least in the XPath implementation.

Summary

XPath is an excellent tool in certain situations. It’s performant, but in return you usually get a somewhat unreadable implementation. It always depends on the situation, which weighs more on the scale.

And it is certainly not a basic tool for a citizen developer.

Do also check out an earlier blog post by my colleague Antti Pajunen on this same topic: XPath to the rescue in a Power Automate integration.