Description
FetchXML is a query language used in Dynamics 365 and in the Common Data Service. It’s expressed in an XML format with some aggregation capabilities, like sum, average, or total. This language supports filters upon the main entity but also filters upon linked entities. For example, a query upon the Contact entity can contain a filter to the Parent Account.
In Dynamics 365, users use it via the Advanced Find for example. When an Advanced Find query is built, Dynamics 365 generates a FetchXML query to retrieve the data. The query can be downloaded in an XML file with the button Download Fetch XML.
Besides being used internally by Dynamics 365, FetchXML queries can and are be used within other tools to retrieve data. For example, Views are generated with FetchXML queries. Some tools of the XRMToolBox also require a FetchXML to retrieve the data to work with[1]. In the same idea, some custom workflows may also require it.
Those queries can also be used directly inside some C# code or with the Web API. Dynamics 365 Portal also requires it to retrieve data.
FetchXML queries have also been used to create reports, but one of the downsides of this language -compared to SQL for example - is the absences if Unions or Subqueries features.
As explained above, FetchXML queries can be built directly in Dynamics 365 via the Advanced Find. But there is also a tool in the XRMToolBox name FetchXML Builder that can be used to create and execute FetchXML queries.
Examples
- In the Advanced Find, the following request
Generates the following XML file:
Where the fields in tag attribute correspond to the columns in the view My Active Contacts, as select in the Advanced Find.
- Inside some C# code, a FetchXML can be used and can contain an aggregate attribute:
- A FetchXML query can also make a count on the number of records that match its criteria. Used in custom development, it can be a more efficient way to count records.
Take-away points
- FetchXML is an XML file representing a query to retrieve data from the Common Data Service.
- Users can retrieve the XML file via the Advanced Find.
- It can be used everywhere: in C# code, with the Web API, in Workflows, in the XRMToolBox, in reports, etc…
[1] For example, the FetchXML/View Record Counter can count the number of records that match a FetchXML query, overcoming the 5’000 records limitations of the Advance Find.
Another example is the Bulk Workflow Execution that requires a FetchXML query to know upon which record a workflow must be executed.
Add new comment