Edit

Query data using FetchXml

  • Article

FetchXml is a proprietary XML based query language used to retrieve data from Dataverse. See FetchXml reference for the elements used to retrieve data.

Note

FetchXml is also used to define views for model-driven apps and some reporting capabilities. Elements and attributes for those use cases are not included here. Learn more about customizing model-driven app views with code.

Compose a query

All queries are based on a single table. The root element is fetch. Use the entity element to select the table the query retrieves data from. The following example represents a simple FetchXml query:

<fetch top='5'>
  <entity name='account'>
      <attribute name='name' />
  </entity>
</fetch>

This query returns the Name column of the first five rows from the Account table, using the LogicalName of the table to set the entity name attribute.

Limit the number of rows

To limit the number of rows returned, use the fetch element top attribute. Without the top attribute, Dataverse returns up to 5,000 rows.

Alternatively, specify a number of records to return using paging. Don't use the top attribute when you request pages of data. Learn how to request paged results

You can't use top when you request a count of rows using the returntotalrecordcount attribute. Learn to count rows

Return distinct results

Use the fetch element distinct attribute to require the query to exclude any duplicate values in the results.

If you use the distinct attribute, you must add at least one order element to have consistent paging.

When you use the distinct attribute, the results returned don't include primary key values for each record because they represent an aggregation of all the distinct values.

Retrieve Data

To get results from your query, you need to send your request to Dataverse. Learn to retrieve data from Dataverse using FetchXml

Refine your query

After you select the table to start your query with, refine the query to get the data you need. The following articles explain how to complete your query.

Article Task
Select columns Specify which columns of data to return.
Join tables Specify which related tables to return in the results.
Order rows Specify the sort order of the rows to return.
Filter rows Specify which rows of data to return.
Page results Specify how many rows of data to return with each request.
Aggregate data How to group and aggregate the data returned.
Count number of rows How to get a count of the number of rows returned.
Performance optimizations How to optimize performance

Community tools

The XrmToolbox FetchXmlBuilder is a free tool to compose and test FetchXml requests.

Note

Tools created by the community are not supported by Microsoft. If you have questions or issues with community tools, contact the publisher of the tool.

Use FetchXml as a message parameter

You also use FetchXml as a parameter for Dataverse operations such as the following messages:

Message Name SDK for .NET Request class Web API Operation
BackgroundSendEmail BackgroundSendEmailRequest BackgroundSendEmail action
BulkDetectDuplicates BulkDetectDuplicatesRequest BulkDetectDuplicates action
FullTextSearchKnowledgeArticle FullTextSearchKnowledgeArticleRequest FullTextSearchKnowledgeArticle action
QueryExpressionToFetchXml QueryExpressionToFetchXmlRequest QueryExpressionToFetchXml action
SendBulkMail SendBulkMailRequest SendBulkMail action
Rollup RollupRequest Rollup function

Next steps

Learn how to select columns.

Select columns

Learn how to retrieve data with FetchXml.

Retrieve data with FetchXml