Understanding a Complex FetchXML Query in Microsoft Dataverse (Dynamics 365)

If you work with Microsoft Dynamics 365 or the Dataverse platform, you’ve likely encountered FetchXML, a powerful query language used to retrieve data from the database. In this post, we’ll explore a real-world example using only out-of-the-box (OTB) entities and fields.

What Is FetchXML?

FetchXML is an XML-based query language used in Microsoft Dataverse. It allows you to:

• Query entities and related data

• Apply filters and conditions

• Retrieve specific attributes

• Join related entities

All without writing SQL.

Example: FetchXML Query Using Standard Entities

Here’s an example using only standard Dynamics 365 entities such as account, contact, and opportunity:

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true">
  <entity name="opportunity">
    <attribute name="name" />
    <attribute name="estimatedvalue" />
    <attribute name="estimatedclosedate" />
    <attribute name="statuscode" />
    <attribute name="ownerid" />
    
    <link-entity name="account" alias="account_link" to="customerid" from="accountid" link-type="outer">
      <attribute name="name" />
      <attribute name="parentaccountid" />
      <attribute name="accountnumber" />
    </link-entity>

    <link-entity name="contact" alias="contact_link" to="customerid" from="contactid" link-type="outer">
      <attribute name="fullname" />
      <attribute name="emailaddress1" />
      <attribute name="telephone1" />
    </link-entity>

    <filter type="and">
      <condition attribute="statuscode" operator="in">
        <value>1</value>
        <value>2</value>
      </condition>
      <condition attribute="estimatedvalue" operator="gt" value="10000" />
    </filter>

    <order attribute="estimatedclosedate" descending="false" />
  </entity>
</fetch>

What This Query Does

This FetchXML query retrieves opportunities along with their related account and contact data. Let’s break it down:

1. Main Entity:
   The primary entity is opportunity, which represents potential sales deals in Dynamics 365.

2. Attributes Retrieved:
   The query retrieves standard fields like:

   • name → Opportunity name

   • estimatedvalue → Estimated revenue

   • estimatedclosedate → Expected close date

   • statuscode → Opportunity status

   • ownerid → Owner of the opportunity

3. Linked Entities:

   • Account: Outer join to get the account name, parent account, and account number.

   • Contact: Outer join to get the contact’s full name, email, and phone number.

4. Filters Applied:

   • Only opportunities with statuscode equal to 1 or 2 (e.g., Open or In Progress).

   • Only opportunities with estimatedvalue greater than 10,000.

5. Sorting:
   Results are sorted by estimatedclosedate in ascending order.

Why This Query Matters

Using FetchXML with OTB entities is important because:

• It works across all standard Dynamics 365 deployments.

• You don’t need custom fields or entities.

• It can be used for reporting, dashboards, and Power BI integration.

This approach ensures that queries are robust, maintainable, and portable across environments.

FetchXML is a versatile tool for retrieving data in Microsoft Dataverse. By mastering OTB entities and fields, you can build meaningful reports, dashboards, and analytics without relying on customizations.

Even complex queries with multiple joins, filters, and sorts can be fully accomplished with standard Dynamics 365 entities.