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

Dynamics 365 Marketing vs ClickDimensions It’s time to pause, reflect and acknowledge a new era of inclusivity and collaboration. Part 2 - How to get the most from a Technology Expert – Asset Management Hub Property & Asset Management Hub Part 1 – Balancing CRM and Asset Management Scopes - Asset Management Hub Creating a Multi-Lingual PowerApps Portal How to Set Up a Microsoft Teams Site Using DocDrive365 Microsoft Teams - Adding a Microsoft Teams URL to a Dynamics Appointment Dynamics 365 Marketing – Customer Voice Survey Not Appearing In Emails? Using SQL Management Studio to connect to the Dynamics DB Calling a Power Platform AI Builder Model via oData How to use DocDrive365 to integrate permissions between Business Units in Dynamics with Sites in SharePoint Getting started with the Power Platform AI Builder. Power Apps Portal Information Hub DocDrive365 Security: Day One - Getting Started with Dynamics to SharePoint Permissions Part 5 - Power Apps Portals: How To Connect Azure B2C With Linked-In Part 4 – Power Apps Portals: Styling Azure B2C for Power Apps Portals The 3 Phases for Using Multi-Select Option Sets in Flow with Microsoft Forms Part 3 – PowerApps Portals: Azure B2C and Power Apps Portals – User Flow for Signup and Signin Part 2 - Power Apps Portals: New Application Registration in Azure B2C for our Power Apps Portal Part 1 – Power Apps Portals: Creating a New Azure AD B2C Tenant The Automation Bot: Launching Contextual Flow from Teams Creating a New Bot for Teams Debugging your Teams Bot using Ngrok Adding a Microsoft Teams URL to a Dynamics Appointment
Contact Us

Want expert advice or a demo?

Get in touch now and see how we can help your business grow.

  • Name
  • Email Address
  • Phone Number
 
Close

Understanding Your Challenges

Our strong understanding of CRM and emerging technologies within the Microsoft environment means we deliver the right solutions for you.

Proven Real-World Solutions

As a leader in the field of Dynamics solutions, our pedigree developing and delivering real-world solutions is unsurpassed.

Long Term Support

We provide support beyond our design, implementation and 'go-live' delivery using Sprints and continual updates to our AppSource apps.

CRMCS | Design by Thinktank Marketing | Citrus-Lime Limited

To improve your experience today and in the future, this site uses cookies. Read our full Privacy Policy & Cookie information here I Understand