#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. In the Web API the cookie comes back as the annotation @Microsoft.Dynamics.CRM.fetchxmlpagingcookie, and that annotation is only present while more pages exist, so its absence is the stop signal for the loop (observed run below).

🧪 Tested against the Dataverse Web API

All four queries below were run as GET .../api/data/v9.2/accounts?fetchXml=<url-encoded fetch> against five test accounts (two in Zurich, two in Bern, one in Basel, three of them without contacts) plus three contacts.

1. Aggregate with group by. aggregate="true" on <fetch>, aggregate="count" plus alias on the counted column, groupby="true" on the grouping column:

<fetch aggregate="true">
  <entity name="account">
    <attribute name="accountid" alias="cnt" aggregate="count"/>
    <attribute name="address1_city" alias="city" groupby="true"/>
  </entity>
</fetch>

Returned [{"cnt":1,"city":"Basel"},{"cnt":2,"city":"Bern"},{"cnt":2,"city":"Zurich"}]. Accounts whose city is empty come back as an extra group without the city property at all, not as "city": null, so check for the missing key when you process the result in a flow.

2. Distinct values. <fetch distinct="true"> with a single <attribute name="address1_city"/> and an <order> on it returned one row per city, and the empty city also came back as its own row. aggregate="countcolumn" with distinct="true" on the attribute counts them instead: 119 distinct last names across 142 contacts in the same call.

3. Anti-join (accounts without any contact). An outer link plus a null condition that targets the linked entity via entityname:

<fetch>
  <entity name="account">
    <attribute name="name"/>
    <filter>
      <condition entityname="c" attribute="contactid" operator="null"/>
    </filter>
    <link-entity name="contact" from="parentcustomerid" to="accountid" link-type="outer" alias="c"/>
  </entity>
</fetch>

Only the three accounts with no contacts were returned. The same link with link-type="inner" and <attribute name="firstname"/> inside it returned one row per contact (c.firstname in the JSON), which is the join you use for the opposite question.

4. Paging with the cookie. <fetch count="3" page="1"> on eight matching accounts returned three rows plus the annotation @Microsoft.Dynamics.CRM.fetchxmlpagingcookie with this content (already URL-encoded twice by Dataverse):

<cookie pagenumber="2" pagingcookie="%253ccookie%2520page%253d%25221%2522%253e..." istracking="False" />

Page 2 still carried the annotation, page 3 (two rows, end of the set) did not, and page 4 returned an empty value array. To reuse the cookie, URL-decode the pagingcookie attribute twice, which yields <cookie page="1"><name last="..." first="..." /><accountid last="{...}" first="{...}" /></cookie>, XML-escape it and put it into paging-cookie="..." on the next <fetch>. top="50" returns 50 rows without any cookie, so use count for paging and top only when you really want a hard cut.

🎉 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.

🎥 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.

4. How do I know I have reached the last page?

When you page with count and page through the Web API, the response contains @Microsoft.Dynamics.CRM.fetchxmlpagingcookie only while more rows exist. The last page comes back without the annotation, and one page further returns an empty value array.

5. How do I find rows that have no related rows (anti-join)?

Use link-type="outer" on the <link-entity> with an alias, then add <condition entityname="<alias>" attribute="<linked id column>" operator="null"/> to the main entity’s <filter>. Tested on accounts and contacts, it returned exactly the accounts without a contact.

🔗 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.