FETCH XML from Dynamics 365

FetchXml

 

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">

<entity name="account">

<attribute name="name" />

<attribute name="aa_accountrecordtype" />

<attribute name="aa_outletno" />

<attribute name="accountid" />

<attribute name="ownerid" />

<attribute name="parentaccountid" />

<attribute name="statecode" />

<attribute name="territoryid" />

<attribute name="aa_preferredagencymodifiedon" />

<attribute name="aa_preferredagencycomments" />

<attribute name="aa_preferred_agency" />

<attribute name="aa_additionalaccowner" />

<attribute name="aa_pref_agy_eff_start_dt" />

<attribute name="aa_pref_agy_eff_end_dt" />

<attribute name="aa_coveragecode" />

<attribute name="accountnumber" />

<order descending="false" attribute="name" />

<filter type="and">

<condition attribute="aa_accountrecordtype" operator="eq" value="100000001" />

<condition attribute="aa_outletno" operator="not-null" />

</filter>

<link-entity name="businessunit" from="businessunitid" to="owningbusinessunit" visible="false" link-type="outer" alias="a_6ad8133d2f1e4c43a3da460bacb3d6a5">

<attribute name="name" />

</link-entity>

</entity>

</fetch>

 

Join using FetchXml

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">

<entity name="account">

<attribute name="name" />

<attribute name="aa_accountrecordtype" />

<attribute name="aa_outletno" />

<attribute name="accountid" />

<attribute name="ownerid" />

<attribute name="aa_preferredagencymodifiedon" />

<order descending="true" attribute ="aa_preferredagencycomments" />

<attribute name="aa_preferred_agency" />

<attribute name="aa_additionalaccowner" />

<attribute name="aa_pref_agy_eff_start_dt" />

<attribute name="aa_pref_agy_eff_end_dt" />

<attribute name="aa_coveragecode" />

<attribute name="accountnumber" />

<order descending="false" attribute="name" />

<filter type="and">

<condition attribute="aa_accountrecordtype" operator="eq" value="100000001" />

<condition attribute="aa_outletno" operator="not-null" />

</filter>

<link-entity name="systemuser" from="systemuserid" to="aa_additionalaccowner" link-type="outer" alias="su">

<attribute name="fullname" />

<attribute name="employeeid" />

</link-entity>

</entity>

</fetch>

Order rows using FetchXml

To specify the sort order for the rows in tables, use the order element within entity or link-entity elements. The default sort order is ascending.

<fetch>

  <entity name='account'>

    <attribute name='name' />

    <attribute name='accountnumber' />

    <attribute name='createdon' />

    <order attribute='createdon' />

    <order attribute='name' />

    <order attribute='accountnumber' />

  </entity>

</fetch>

Dataverse always orders attributes specified by the link-entity after attributes for the entity element.

The following example shows a conventional ordering pattern for both link-entity attributes and entity attributes.

XML

<fetch>
  <entity name='account'>
    <attribute name='name' />
    <attribute name='accountnumber' />
    <attribute name='createdon' />
    <link-entity name='account'
      from='accountid'
      to='parentaccountid'
      link-type='inner'
      alias='parentaccount'>
      <attribute name='name'
        alias='parentaccount' />
        <!-- The link-entity parentaccount name -->
      <order attribute='name' />
    </link-entity>
    <!-- The entity account name -->
    <order attribute='name' />
  </entity>
</fetch>

In this case, the results are ordered using following attributes:

• First => account.name
• Last => parentaccountname.name

To ensure the link-entity order is applied first, move the order element from the link-entity element to the entity element above the other order element, and use the entityname attribute on the order element to refer to the link-entity alias value.

Aggregate data using FetchXml

XML

<fetch aggregate='true'>
  <entity name='account'>
    <attribute name='numberofemployees'
      alias='Average'
      aggregate='avg' />
    <attribute name='numberofemployees'
      alias='Count'
      aggregate='count' />
    <attribute name='numberofemployees'
      alias='ColumnCount'
      aggregate='countcolumn' />
    <attribute name='numberofemployees'
      alias='Maximum'
      aggregate='max' />
    <attribute name='numberofemployees'
      alias='Minimum'
      aggregate='min' />
    <attribute name='numberofemployees'
      alias='Sum'
      aggregate='sum' />
  </entity>
</fetch>
Grouping
Group the results of an aggregate query by adding an attribute element with the groupby attribute rather than the aggregate attribute. When grouping, you should specify an order element with an alias value set to the alias of the group.
For example the following query returns the sum of employees, and count by city:
XML
<fetch aggregate='true'>
   <entity name='account'>
      <attribute name='numberofemployees'
         alias='Total'
         aggregate='sum' />
      <attribute name='address1_city'
         alias='Count'
         aggregate='count' />
      <attribute name='address1_city'
         alias='City'
         groupby='true' />
      <order alias='City' />
   </entity>
</fetch>

--All Attributes

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true">

  <entity name="TEST">

    <all-attributes />

    <filter type="and">

      <condition attribute="createdon" value="2018-01-01" operator="on-or-after" />

      <condition attribute="createdon" value="3" operator="olderthan-x-months" />

    </filter>

  </entity>

</fetch>

===

COUNT()

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false" aggregate="true">

  <entity name="TEST">

    <attribute name="aa_integration_responseid" alias="record_count" aggregate="count" />

    <filter type="or">

          <condition attribute="createdon"  value="2018-01-01"  operator="on-or-after" />

          <condition attribute="createdon" value="3" operator="olderthan-x-months"/>

    </filter>

  </entity>

</fetch>