#PowerPlatformTip 39: 'Mastering FetchXML'

TL;DR: Build advanced Dataverse/Dynamics 365 queries with FetchXML: generate it in FetchXML Builder or Advanced Find, then use it in flows and plugins.

🧰 Free tool: Build FetchXML queries for Dataverse without writing XML by hand: FetchXML Builder.

💡 Challenge

Do you need to retrieve complex data from Dataverse or Dynamics 365, but find the standard query options too limited? The “List rows” action’s built-in OData filter box handles simple equality and comparison filters fine, but it has no clean way to express a multi-entity join, a grouped aggregation (sum, count, average per group), or a distinct-values query, exactly the operations FetchXML was built for.

✅ Solution

FetchXML is a powerful XML-based query language that allows you to perform advanced data retrieval operations in Dataverse and Dynamics 365.

🔧 How It’s Done

Here’s how to use FetchXML:

1. Open the Power Platform or Dynamics 365 environment.
2. Use the FetchXML Builder (XrmToolBox) or the built-in Advanced Find to create your query.
3. Copy the generated FetchXML code.
4. Use it in your Power Automate flows (the “List rows” action accepts a “Fetch Xml Query” parameter directly, no separate action needed), custom connectors, or plugins.

Aggregation queries. Add aggregate="true" to the <fetch> element, then on the column you’re summarizing set aggregate="sum" (or count, avg, min, max) with an alias, and add groupby="true" on any column you want to group by. This runs the aggregation server-side in Dataverse rather than pulling every row back and summing in the flow, a meaningful difference once a table has more than a few thousand records.

Paging through large result sets. A single fetch is capped (5,000 rows by default). For anything larger, request pages with page and count attributes on <fetch>, and use the paging-cookie returned in the response to fetch the next page, looping until no more rows come back.

🎉 Result

With FetchXML, you can retrieve exactly the data you need, including complex joins, filters, and aggregations, computed server-side instead of pulled into the flow and processed there.

🌟 Key Advantages

🔸 Advanced querying capabilities.

🔸 Support for joins and aggregations, run inside Dataverse rather than in flow expressions.

🔸 Usable in Power Automate, plugins, and custom connectors.

🔸 When to reach for OData instead: for a simple single-table filter, the “List rows” OData filter box is faster to write and easier for a colleague to read later, save FetchXML for genuine joins, aggregation, or distinct queries the OData syntax can’t express.

🔗 Related Tips

🔸 #PowerPlatformTip 42: ‘Dataverse Views’ - the simpler, server-side alternative when you don’t need FetchXML’s full power.

🔸 #PowerPlatformTip 41: ‘Standard Tables in Dataverse’ - know which built-in tables you’re querying before you write complex FetchXML joins.

🎥 Video Tutorial

---

🛠️ FAQ

1. Where can I learn FetchXML syntax?

Microsoft Learn and the FetchXML Builder tool provide great resources and examples.

2. Can I use FetchXML in Power Automate?

Yes, you can use FetchXML in Dataverse actions within Power Automate.

3. Are there limitations to FetchXML?

Some advanced SQL features are not supported, but most business scenarios are covered.

---