All about FetchXML in Dynamics 365 CRM

FetchXML is a XML based query language used in Microsoft Dynamics 365 CRM to fetch data. FetchXML is capable of doing many things as explained below.

  • Can only be used to retrieve data not able to perform CUD (Create/Update/Delete) operation.
  • Can be used in JavaScript to retrieve data in client side also used in server side code to retrieve data.
  • Can perform Aggregations such as Sum, Average, Minimum, Maximum, Count(*), Count(attributename).
  • Supports Linked entity data retrieval with conditions, filters, GroupBy, Sorting
  • Can fetch 5000 record data at a time and supports paging concept to fetch more records page by page .
  • Can be saved in userquery  entity as user owned view and savedquery  entity as organization owned view.
  • Can also be used in SSRS query in Reporting.
  • Supports aliased name for attributes retrieved.
  • Supports similar query capabilities as query expressions.
  • Can be executed by using the RetrieveMultiple method.
  • Can be downloaded/saved from Advance Find query.

Here is a list of sample FetchXML Queries given below.

Everyday we use fetchXML in development. So here i have given a list of ready-made fetchXMLs which you can use by modifying the code as per your business requirement. FetchXML query should validated by the xsd schema.

 

Retrieve all attributes of account entity

<fetch mapping='logical'>
  <entity name='account'>
   <all-attributes/>
  </entity>
</fetch>

Retrieve selected attributes of account entity

<fetch mapping='logical'>
<entity name='account'>
<attribute name='accountid'/>
<attribute name='name'/>
</entity>
</fetch>

Retrieve records of account entity with filter condition

<fetch mapping='logical'>
<entity name='account'>
<attribute name='accountid'/>
<attribute name='name'/>
<filter type='and'>
  <condition attribute='name' operator='ne' value='Softchief' />
</filter>
</entity>
</fetch>

Retrieve contract records using linkedentity with customer using filter condition in linked entity

<fetch mapping='logical'>
<entity name='contract'>
<attribute name='title'/>
<attribute name='customerid'/>
<link-entity name='contact' from='contactid' to='customerid'>
<filter type='and'>
<condition attribute='lastname' operator='eq' value='Pradhan' />
</filter>
</link-entity>
</entity>
</fetch>

Limit the Maximum (Lets Say 5)Number Of Records to be retrieved in contact entity.

<fetch mapping='logical' count='5'>
<entity name='contact'>
<attribute name='name' />
</entity>
</fetch>

Limit the Maximum (Lets Say 5)Number Of Records to be retrieved in contact entity.

<fetch mapping='logical' count='5'>
<entity name='contact'>
<attribute name='name' />
<attribute name='lastname' />
</entity>
</fetch>

Use of Inner Join in FetchXML

<fetch version='1.0' mapping='logical' distinct='false'>
<entity name='contact'>
<attribute name='fullname'/>
<link-entity name='account' to='parentcustomerid' from='accountid' link-type='inner'>
<attribute name='name'/>
</link-entity>
</entity>
</fetch>

Use of Left Outer Join in FetchXML

<fetch mapping='logical'>
<entity name='account'>
<attribute name='name' />
<link-entity name='lead' from='leadid' to='originatingleadid' link-type='outer' />
<filter type='and'>
<condition entityname='lead' attribute='leadid' operator='null' />
</filter>
</entity>
</fetch>

Use Avarage Aggregate in FetchXML

<fetch distinct='false' mapping='logical' aggregate='true'>
<entity name='opportunity'>
<attribute name='estimatedvalue' alias='estimatedvalue_avg' aggregate='avg' />
</entity>
</fetch>

Count No. of records retrieved in FetchXML

<fetch distinct='false' mapping='logical' aggregate='true'>
<entity name='opportunity'>
<attribute name='name' alias='opportunity_count' aggregate='count'/>
</entity>
</fetch>

Using “older than” clauses for date and time values in FetchXML Query

<fetch>
<entity name="incident">
<attribute name="title" />
<attribute name="ticketnumber" />
<attribute name="createdon" />
<attribute name="incidentid" />
<filter type="and">
<condition attribute="createdon" operator="olderthan-x-minutes" value="40" />
</filter>
</entity>
</fetch>

fiscal-period FetchXML Query

<fetch aggregate="true">
<entity name="order">
<attribute name="totalamount" aggregate="sum" alias="total"/>
<attribute name="datefulfilled" groupby="true" dategrouping="fiscal-period"/>
</entity>
</fetch>

Order by in FetchXML Query

<fetch distinct='false' mapping='logical' aggregate='true'>
<entity name='opportunity'>
<attribute name='opportunityid' alias='opportunity_count' aggregate='count'/>
<attribute name='estimatedvalue' alias='estimatedvalue_sum' aggregate='sum'/>
<attribute name='estimatedvalue' alias='estimatedvalue_avg' aggregate='avg'/>
<attribute name='actualclosedate' groupby='true' dategrouping='quarter' alias='quarter' />
<attribute name='actualclosedate' groupby='true' dategrouping='year' alias='year' />
<order alias='year' descending='false' />
<order alias='quarter' descending='false' />
<filter type='and'>
<condition attribute='statecode' operator='eq' value='Won' />
</filter>
</entity>
</fetch>

Order by in FetchXML Query

<fetch distinct='false' mapping='logical' aggregate='true'>
<entity name='opportunity'>
<attribute name='opportunityid' alias='opportunity_count' aggregate='count'/>
<attribute name='estimatedvalue' alias='estimatedvalue_sum' aggregate='sum'/>
<attribute name='estimatedvalue' alias='estimatedvalue_avg' aggregate='avg'/>
<attribute name='actualclosedate' groupby='true' dategrouping='quarter' alias='quarter' />
<attribute name='actualclosedate' groupby='true' dategrouping='year' alias='year' />
<order alias='year' descending='false' />
<order alias='quarter' descending='false' />
<filter type='and'>
<condition attribute='statecode' operator='eq' value='Won' />
</filter>
</entity>
</fetch>

List of Operators used in FetchXML are given below.

FetchXML Operator ConditionOperator Description
like BeginsWith The string occurs at the beginning of another string.
between Between The value is between two values.
like Contains The string contains another string.
not-like DoesNotBeginWith The string does not begin with another string.
not-like DoesNotContain The string does not contain another string.
not-like DoesNotEndWith The string does not end with another string.
like EndsWith The string ends with another string.
eq Equal The values are compared for equality.
eq-businessid EqualBusinessId The value is equal to the specified business ID.
eq-userid EqualUserId The value is equal to the specified user ID.
eq-userteams EqualUserTeams The record is owned by teams that the user is a member of.
ge GreaterEqual The value is greater than or equal to the compared value.
gt GreaterThan The value is greater than the compared value.
in In TheThe value exists in a list of values.
in-fiscal-period InFiscalPeriod The value is within the specified fiscal period.
in-fiscal-period-and-year InFiscalPeriodAndYear The value is within the specified fiscal period and year.
in-fiscal-year InFiscalYear The value is within the specified year.
in-or-after-fiscal-period-and-year InOrAfterFiscalPeriodAndYear The value is within or after the specified fiscal period and year.
in-or-before-fiscal-period-and-year InOrBeforeFiscalPeriodAndYear The value is within or before the specified fiscal period and year.
last-seven-days Last7Days The value is within the last seven days including today.
last-fiscal-period LastFiscalPeriod The value is within the last fiscal period.
last-fiscal-year LastFiscalYear The value is within the last fiscal year.
last-month LastMonth The value is within the last month including first day of the last month and last day of the last month.
last-week LastWeek The value is within the previous week including Sunday through Saturday.
last-x-days LastXDays The value is within last X days.
last-x-fiscal-periods LastXFiscalPeriods The value is within the last X (specified value) fiscal periods.
last-x-fiscal-years LastXFiscalYears The value is within the last X (specified value) fiscal periods.
last-x-hours LastXHours The value is within the last X hours.
last-x-months LastXMonths The value is within the last X (specified value) months.
last-x-weeks LastXWeeks The value is within the last X (specified value) weeks.
last-x-years LastXYears The value is within the last X years.
last-year LastYear The value is within the previous year.
le LessEqual The value is less than or equal to the compared value.
lt LessThan The value is less than the compared value.
like Like The character string is matched to the specified pattern.
next-seven-days Next7Days The value is within the next seven days.
next-fiscal-period NextFiscalPeriod The value is within the next fiscal period.
next-fiscal-year NextFiscalYear The value is within the next fiscal year.
next-month NextMonth The value is within the next month.
next-week NextWeek The value is within the next week.
next-x-days NextXDays The value is within the next X (specified value) days.
next-x-fiscal-periods NextXFiscalPeriods The value is within the next X (specified value) fiscal period.
next-x-fiscal-years NextXFiscalYears The value is within the next X (specified value) fiscal years.
next-x-hours NextXHours The value is within the next X (specified value) hours.
next-x-months NextXMonths The value is within the next X (specified value) months.
next-x-weeks NextXWeeks The value is within the next X weeks.
next-x-years NextXYears The value is within the next X years.
next-year NextYear The value is within the next year.
not-between NotBetween The value is not between two values.
ne NotEqual The two values are not equal.
ne-businessid NotEqualBusinessId The value is not equal to the specified business ID.
ne-userid NotEqualUserId The value is not equal to the specified user ID.
not-in NotIn The given value is not matched to a value in a subquery or a list.
not-like NotLike The character string does not match the specified pattern.
not-null NotNull The value is not null.
ne NotOn The value is not on the specified date.
null Null The value is null.
olderthan-x-months OlderThanXMonths The value is older than the specified number of months.
on On The value is on a specified date.
on-or-after OnOrAfter The value is on or after a specified date.
on-or-before OnOrBefore The value is on or before a specified date.
this-fiscal-period ThisFiscalPeriod The value is within the current fiscal period.
this-fiscal-year ThisFiscalYear The value is within the current fiscal year.
this-month ThisMonth The value is within the current month.
this-week ThisWeek The value is within the current week.
this-year ThisYear The value is within the current year.
today Today The value equals today’s date.
tomorrow Tomorrow The value equals tomorrow’s date.
yesterday Yesterday The value equals yesterday’s date.

List of older than clauses in a FetchXML expression

Older than X minutes
Older than X hours
Older than X days
Older than X weeks
Older than X months
Older than X years

 

How to Retrieve Data using Fetch XML in server side (Sample given below)

// Retrieve all accounts owned by the user with read access rights to the accounts and 
// where the last name of the user is not Cannon. 
string varFetchXML = @"
   <fetch mapping='logical'>
     <entity name='account'> 
        <attribute name='accountid'/> 
        <attribute name='name'/> 
        <link-entity name='systemuser' to='owninguser'> 
           <filter type='and'> 
              <condition attribute='lastname' operator='ne' value='Cannon' /> 
           </filter> 
        </link-entity> 
     </entity> 
   </fetch> "; 

EntityCollection result = 
    _serviceProxy.RetrieveMultiple(new FetchExpression(varFetchXML));

foreach (var c in result.Entities)   
{   
   System.Console.WriteLine(c.Attributes["name"]);   
}

How to Retrieve Data using Fetch XML in client side using WebAPI(Sample given below)


var fetchXml = 
"<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='false'>"+
 "<entity name='account'>"+
 "<attribute name='name' />"+
 "<attribute name='primarycontactid' />"+
 "<attribute name='telephone1' />"+
 "<attribute name='accountid' />"+
 "<order attribute='name' descending='false' />"+
 "<filter type='and'>"+
 "<condition attribute='name' operator='like' value='C%' />"+
 "</filter>"+
 "</entity>"+
"</fetch>";
var encodedFetchXml = encodeURI(fetchXml);

var queryPath = "/api/data/v8.0/accounts?fetchXml=" + encodedFetchXml;
var requestPath = Xrm.Page.context.getClientUrl() + queryPath;

var req = new XMLHttpRequest();
req.open("GET", requestPath, true);
req.setRequestHeader("Accept", "application/json");
req.setRequestHeader("Content-Type", "application/json; charset=utf-8");
req.onreadystatechange = function ()
{
if (this.readyState === 4)
{
this.onreadystatechange = null;

if (this.status === 200)
{
var returned = JSON.parse(this.responseText);
var results = returned.value;

for (var i = 0; i < results.length; i++)
{
var accountName = results[i]["name"];
var primaryContactId = results[i]["_primarycontactid_value"];
var telephone = results[i]["telephone1"];
var accountId = results[i]["accountid"];
//TODO: Implement logic for handling results as desired
}
}
else
{
alert(this.statusText);
}
}
};
req.send();

Related Links:

Create a SSRS report using FetchXML.

Retrieve More than 5000 record using FetchXML Paging.

FetchXML V/S Query Expression

FetchXML Query Expression
Easy to construct FetchXML Query using out-of-the-box Advance Find visual tool Manually need to write the code to declare objects of Query Expression, Filters, Conditions, attributes etc.
Can be used in both client side and server side data retrieval Can only be used in server side code data retrieval such as plugins, custom workflows, custom .net components
Get Optionset Text Directly NO Direct retrieval of Optionset Text without Metadata Request
Easy to construct multiple liked entity Complex when Multiple linked entity required
supports Aggregation Don’t not support Aggregation