When working with Dynamics 365, which enables the creation of extensive records and provides endless integration possibilities via Dataverse, having a robust method to query and retrieve these records is essential. Dataverse is not merely a SQL Server database where you could craft standard SQL queries—it's a platform with unique APIs, SDKs, and query methods designed to optimize performance and flexibility.
Among these, FetchXML stands out as a particularly powerful and versatile option.
What is FetchXML?
FetchXML is a proprietary XML-based query language designed for Microsoft Dataverse (formerly CDS) and Dynamics 365. It allows developers and users to construct complex queries that retrieve, aggregate, and filter data from Dataverse tables. Unlike traditional SQL, FetchXML is deeply integrated with the Dynamics 365 ecosystem, making it suitable for scenarios where relational and metadata-driven data models come into play.
FetchXML supports a variety of features, such as :
- Filtering data using conditional operators.
- Sorting results based on specified fields.
- Aggregating data with functions like SUM, COUNT, and AVG.
- Fetching related data using linked-entity relationships.
- Paging results for large datasets.
FetchXML is widely used in Dynamics 365 because it’s the underlying query language for Advanced Find, Custom views in Dynamics 365 applications, Reports and dashboards, Server-side plugins and workflows, Client-side scripting through JavaScript, Power Automate flows.
Check official documentation.
Why FetchXML Matters
As queries grow more complex, they can become harder to manage, potentially leading to performance bottlenecks. FetchXML addresses these issues by :
Integration with Dataverse: It understands the metadata-driven nature of Dataverse, meaning it respects security roles, field constraints, and relationships automatically. Compatibility with APIs: FetchXML can be used with Dynamics 365 Web API, SDKs, and OData endpoints, ensuring it works seamlessly in both server-side and client-side scenarios. Customization: It allows for building sophisticated queries, including aggregation and joins, without writing low-level database code. Accessibility for Non-Developers: FetchXML can be generated using user-friendly tools, such as the Advanced Find feature, empowering even non-technical users to create and export queries.
How to Use FetchXML
1. Start with Advanced Find
One of the easiest ways to create FetchXML queries is through Advanced Find, a feature accessible within Dynamics 365. Advanced Find allows users to :
Once your query is ready, you can click on "Download FetchXML" to export the XML file. This file can then be used in :
2. Use FetchXML with Power Automate FetchXML is a great choice for querying Dataverse within Power Automate. Using the Dataverse connector, you can paste FetchXML queries to retrieve specific data, especially when dealing with complex conditions that aren’t possible through the basic query interface.
3. Execute FetchXML via the Web API FetchXML can be executed programmatically using the Dynamics 365 Web API. You can send HTTP GET or POST requests with the FetchXML query embedded, enabling you to retrieve data directly in your applications or integrations.
In the Code snippet section of the website, you can find samples presenting how to use fetchXML from the Code. Here is a quick example of how to use FetchXML in JavaScript (JS) form script with the retrieveMultipleRecords method available in the D365 SDK API : Click here
For an example in CSharp (C#) using RetrieveMultiple method along with FetchExpression please check this Code snippet : Click here
4. Customize FetchXML in Reports If you're building custom SSRS (SQL Server Reporting Services) reports, FetchXML becomes the backbone of your data queries. This is especially useful when you need detailed or aggregated reports directly from Dataverse.
5. Generate and Edit FetchXML Programmatically While tools like Advanced Find are great for basic queries, developers often edit the exported FetchXML manually to add advanced features like :
Example of a Simple FetchXML Query Below is a basic FetchXML query that retrieves top 15 accounts from a Dataverse environment (where `statecode` equals 0) whose names begin with "Alpes" and includes the full name of the primary contact linked to each account, with the result limited to one record :
This query can be customized by adding as many criteria and filters as possible, and executed from different places in the SDK or APIs.
- Select a table (entity) to query.
- Add conditions and filters.
- Choose fields to display.
- Sort and group data.
Once your query is ready, you can click on "Download FetchXML" to export the XML file. This file can then be used in :
- Power Automate flows.
- Custom code in plugins or JavaScript.
- Reports in Dynamics 365.
2. Use FetchXML with Power Automate FetchXML is a great choice for querying Dataverse within Power Automate. Using the Dataverse connector, you can paste FetchXML queries to retrieve specific data, especially when dealing with complex conditions that aren’t possible through the basic query interface.
3. Execute FetchXML via the Web API FetchXML can be executed programmatically using the Dynamics 365 Web API. You can send HTTP GET or POST requests with the FetchXML query embedded, enabling you to retrieve data directly in your applications or integrations.
In the Code snippet section of the website, you can find samples presenting how to use fetchXML from the Code. Here is a quick example of how to use FetchXML in JavaScript (JS) form script with the retrieveMultipleRecords method available in the D365 SDK API : Click here
For an example in CSharp (C#) using RetrieveMultiple method along with FetchExpression please check this Code snippet : Click here
 |
Run Fetch XML Query in JavaScript | |
 |
Run fetch XML query in C# |
4. Customize FetchXML in Reports If you're building custom SSRS (SQL Server Reporting Services) reports, FetchXML becomes the backbone of your data queries. This is especially useful when you need detailed or aggregated reports directly from Dataverse.
5. Generate and Edit FetchXML Programmatically While tools like Advanced Find are great for basic queries, developers often edit the exported FetchXML manually to add advanced features like :
- Nested conditions.
- Joins with related entities. (Fetch data from related tables, such as retrieving all orders related to a specific customer)
- Aggregation and grouping. (Create summaries and insights directly within the query)
- Pagination for large datasets.
- Fetch Paging Cookie: Efficiently handle large datasets by implementing paging.
Example of a Simple FetchXML Query Below is a basic FetchXML query that retrieves top 15 accounts from a Dataverse environment (where `statecode` equals 0) whose names begin with "Alpes" and includes the full name of the primary contact linked to each account, with the result limited to one record :
<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false" top="15">
<entity name="account">
<attribute name="accountid" />
<attribute name="name" />
<filter type="and">
<condition attribute="statecode" operator="eq" value="0" />
<condition attribute="name" operator="like" value="Alpes%" />
</filter>
<link-entity name="contact" from="contactid" to="primarycontactid" alias="primarycontact">
<attribute name="fullname" />
</link-entity>
</entity>
</fetch>
This query can be customized by adding as many criteria and filters as possible, and executed from different places in the SDK or APIs.
XrmToolBox and the FetchXML Builder plugin
XrmToolBox is a third-party platform widely adopted by the Dynamics 365 community to develop and customize the CRM environment. Among its many plugins, FetchXML Builder is particularly appreciated for creating FetchXML queries.
This tool offers a visual and interactive approach to constructing FetchXML queries, enabling users to design and modify their queries with the benefit of real-time validation that ensures both syntactic and logical accuracy as they build. It also supports a range of advanced features, including the ability to perform aggregations, implement complex joins, and convert queries into various formats such as QueryExpression, C# code, and Web API queries, thus addressing diverse integration and development requirements. Furthermore, the tool allows users to test their queries directly within the interface and export the results for subsequent use in applications or automated processes, providing a comprehensive solution for optimizing data manipulation and retrieval within Dynamics 365.
Benefits for Developers and Non-Technical Users
What makes FetchXML exceptional is its accessibility to both developers and non-technical users. Developers can use FetchXML in plugins, workflows, and custom code for highly customized scenarios. Meanwhile, non-technical users can generate and download FetchXML through Advanced Find, allowing them to contribute meaningfully without diving into code.
FetchXML is a cornerstone of querying within Dynamics 365 and the Power Platform, blending ease of use, advanced capabilities, and seamless integration. Whether you're a seasoned developer or a business user, mastering FetchXML can significantly enhance your ability to interact with and extract value from your data.
Français
English