XPath to the rescue in a Power Automate integration

My colleague presented me with an interesting problem. He was calling a marketing platform’s API with Power Automate to get contacts and their email addresses from the platform. The use case was to move contacts and their emails from the platform to Dynamics 365 on a recurring schedule. The amount of data was minimal with only tens of contacts that were to be moved weekly to D365. The use case was so simple that “proper” integration tools like Azure Integration Services didn’t really make sense. Simplicity doesn’t mean that Power Automate should be used without evaluating the overall scenario. There are some specific points with Power Automate that need to be considered. Examples of technical limitations to consider are:

• Power Platform requests.
• Limits for automated, scheduled, and instant flows.
• Service protection API limits for Dataverse.
• Microsoft Dataverse connector’s limits, especially throttling limits.

The challenge

The challenge my colleague faced was not with Power Automate’s limitations but in the JSON that was returned by the marketing platform. There wasn’t anything we could do to change that so we had to find a way to work with whatever it returned. Remember our goal: Create a new contact with email in D365 based on what is returned from the marketing platform. The JSON the platform returned can be found below. As you can see, there’s no array there. We can’t loop though this JSON with an Apply to each action in flow. It means we can’t get our hands on the value properties in the JSON.

{
"e66f3e28-f010-4d27-b063-6a790a93a68c": {
"sections": {
"18717": {
"attributes": {
"366831": {
"value": "John"
},
"366833": {
"value": "Doe"
},
"366834": {
"value": "john.doe@badjson.com"
}
}
}
}
},
"593fd77e-0720-4deb-831e-2aa530afbb2f": {
"sections": {
"18717": {
"attributes": {
"366831": {
"value": "Jane"
},
"366833": {
"value": "Doe"
},
"366834": {
"value": "jane.doe@badjson.com"
}
}
}
}
}
}

How do we solve this?

Let’s consider our options. We can’t impact what the platform returns so we have to work with what we have. Do we hand this over to someone with a dev background internally or do we try to solve this ourselves? How much time can we spend on this? Could we manipulate the JSON to solve the problem?

Generally I quite enjoy problems like this because they are pretty much always a source of great learning. I didn’t really want to spend too much time investigating this due to other time constraints so I decided to leverage the power of The Community. What is The Community? It’s all the fantastic people who contribute their time and help with problems exactly like ours on community forums and on social media. It’s the people who are passionate about helping others.

On this occasion I decided to reach out to the best of the best in Power Automate and in less that 30 minutes I got a reply from MVPs Benedikt Bergmann, Guido Preite, and Linn Zaw Win: Wrap the JSON around a root property and use XPath was the short answer. If you follow flow people in the community, it’ll come as no surprise that the fantastic MVP John Liu has already written about XPath back in 2017!

The solution

Before we dissect our flow, let’s open up what XPath means. In short, XPath is used for selecting nodes from an XML document. There’s plenty on information about it on Wikipedia and W3Schools. The latter also has examples for XPath Syntax. Now that we understand XPath a little better, let’s look at how we can get contacts and their emails from the previously mentioned JSON.

The flow

We’ll start the flow with a manual trigger for the sake of example. The first thing to do is to initialize an object variable, which contains the original JSON.

The next step is to wrap a root property around the JSON by initializing another object variable called varJsonWithRoot. I’m going to cover two different examples of selecting different nodes so a parallel branch is used. The names of the actions in that branch are with Alternative (for example varJsonWithRootAlternative).

The next action is where the XPath and XML magic happens. The JSON that now has a root property needs to be converted into XML and a node can then be selected with XPath. First, an array variable named varXmlArray is initialized. In the left branch I’ve selected the sections node and in the right branch that attributes node: Two different nodes for the sake of example. Let’s look at what IntelliSense says when writing the expressions: We’re converting the JSON to XML with the xml expression. With the xpath expression we’re selecting the attributes node when in the right brand of the flow and the sections node when in the left branch of the flow. The expression used is xpath(xml(variables('varJsonWithRootAlternative')),'//attributes').

The rest of the flow is pretty easy-going inside a loopy-loop. First, the XML needs to be converted back to JSON with a simple expression json(items('Apply_to_each_varXmlArray')). A parse JSON action is then used for the output of the Convert to JSON action. How do we know what to parse? Before the parse JSON is added, run the flow so that you can copy the output of Convert to JSON. It can then be used in the parse JSON action to generate a JSON schema.

The final three actions compose the value parameters. They can be accessed from dynamic content as the JSON is parsed. Now that we have our hands on the information we were after, it’s easy to create a new row in D365.

And there we have it! It’s really not more complicated than this to use XPath in Power Automate. Kudos to my fellow MVPs for the assist. The managed and unmanaged solutions containing this flow can found in Forward Forever’s community GitHub repo.