Support Portal ContactGet in touch

Dynamics CRM and Fetch XML

   Words by CRM Consultancy

   on 08/04/2016 15:42:00

When we want to retrieve multiple records back from CRM using the Webservice API, we use a Query Expression object in the code to define the Entity, Records and Columns we want to retrieve back from CRM.

This Query Expression is declared like any other object in our Plugin Code or Page Code-behind, and so defined in code to build our Query – this is then passed to the Retrieve Multiple method of the CRM Webservice.

The Webservice itself takes this object and then responds with a resulting Entity Collection object – this collection containing the records we have requested.

Behind the scenes however, the CRM Service is translating our Query Expression into a block of XML that the Service then processes into the relevant query – the XML defining the query in a language that the CRM Webservice understands. 

This language is a format of XML known as Fetch XML, and is used for defining such Select Queries to CRM.

Query Expression in C#

Fetch XML

QueryExpression q = new QueryExpression("account");

q.ColumnSet = new ColumnSet("name", "accountnumber");

q.Criteria.AddCondition("address1_city", ConditionOperator.Equal, "Manchester");

<fetch distinct="false" no-lock="false" mapping="logical">

<entity name="account">

<attribute name="name" />

<attribute name="accountnumber" />

<filter type="and">

<condition attribute="address1_city" operator="eq" value="Manchester" />

</filter>

</entity>

</fetch>

This XML is also the language that CRM uses internally for User-built Queries via the Advanced Find.

Fetch XML in Advanced Finds

When we build an Advanced Find in CRM using the standard User Interface, CRM takes the Criteria and Conditions involved and condenses into a block of Fetch XML that can then be re-used.

Such that when a User saves the Advanced Find, CRM is saving the Fetch XML as the definition of the Find:

clip_image002

Figure 1- Building and Saving the same Advanced Find within Dynamics CRM

When building this Advanced Find, CRM uses us an option to download the XML query behind our Advanced Find:

clip_image004

This pulls down the Fetch XML so we can see how CRM has translated our Advanced Find Query:

clip_image006

Why this can be useful..

This ability to output an Advanced Find or Query as a block of XML can be very useful when looking at Database Operations, as we can build and configure our Query in the Advanced Find, produce as a block of XML and then use this XML as the input to an automated process or procedure.

This gives us the ability to use our Advanced Find Generated XML to drive Scheduled Tasks that run for Regular Integration or Overnight Processes, passing the block of Fetch XML into the definition of the Task to then drive which records should be processed.

clip_image008

This allows us to change the input of a regular operation without rebuilding the operating code, which can give us a better degree of flexibility in its running.  Obviously writing blocks of XML is not much better than writing raw code, however the Advanced Find gives us a great tool to build our Queries in a Human Readable fashion and then simply export the XML as we need – which then makes using Fetch XML as a way of communicating our Queries a massive benefit.

Fetch XML is also forms the basis of SSRS Reports for CRM Online, where direct access to the underlying SQL Database is not possible.

Further Reading

MSDN – Use Fetch XML to Construct a Query

https://msdn.microsoft.com/en-gb/library/gg328117.aspx

MSDN Sample – Convert Queries between Fetch (XML) and Query Expression

https://msdn.microsoft.com/en-gb/library/hh547457.aspx

Retrieve Records using Fetch XML via Javascript

https://arunpotti.wordpress.com/2014/11/15/retrieve-records-using-fetch-xml-java-script-in-crm-201113/

Share this Article

Search Articles

Filter Articles

CRM Tech DocMan

Recent Articles

CRMCS Quick Start Guide: How To Produce a Microsoft Teams Live Event Dynamics 365 Marketing: Lead Scoring and Sales Acceptance Designing and Developing Microsoft Power Apps Portals Thank You for Attending CRMCS’ Webinar - Achieving B2B sales excellence with Dynamics 365 & Microsoft Teams Thank You for Attending Our Webinar - Achieving B2B sales excellence with Dynamics 365 & Microsoft Teams Webinar: Discover How CRMCS Have United Dynamics 365, SharePoint and Microsoft Teams To Create Sales Excellence Ignite your workflow by adding DocDrive365 to Office 365 The CRMCS guide to everything you need to know about integrating Teams with Dynamics 365 Saving Time By Keeping Documents In One Place TDE Database Encryption with On Premise Dynamics The Key to Successful Compliance in 2020 Part 2: Let’s get GDPR Compliant with Microsoft Power Automate Top 3 Essential Tips for Remote Working Dynamics 365 Marketing: Top 5 Best Features Dynamics Day in the Life - Puma Investments Can you use Teams to amplify collaboration in Dynamics? Part 1: Using a Scheduled Power Automate to Trigger Expiry Date Reminders The secrets of successful document collaboration in Dynamics CRMCS launches new AppSource approved DocDrive365 Dynamics Day in the Life - Moneypenny Release Management Add the App to Dynamics DocDrive365 Security: Day One - Getting Started with Dynamics to SharePoint Permissions Building a New Scheduled Process using Flow
  • "Paul has made a real difference to how my team of 24 people record and store valuable customer data and sales opportunities. Highly recommended."

    James, Operations Director

  • "Understanding your business allows us to advise when to implement aspects of CRM and, likewise, when not to."

    Paul McQuillan, Managing Director

  • "Dynamics 365 and CRMCS have made a real lasting difference to our business, allowing us to replace older systems that were holding back our performance."

    Grahame, Chief Operating Officer

  • "James worked well with us to help connect CRM with Outlook and relate how this might benefit our team using CRM for Property Care."

    Natalie, Property Care Supervisor

  • "Matt was really good with helping us run User Testing on the new Compliance Module of our CRM System."

    Tom, Compliance Administrator

Prefer to go old-school?

Write to us using the below addresses.

Head Office
CRM Consultancy
61 Oxford Street
Manchester
M1 6EQ

London Office
CRM Consultancy London
Grosvenor Avenue
London

Content © CRM Consultancy.