Use FetchXML to Retrieve Data from MS CRM 2016 using Web API

With Web API in MS CRM, we are retrieve data using FetchXML.

Easy way to construct FetchXML queries are using advanced find in CRM, you can create query in advanced find, add columns, define sorting and then download FetchXML.

Once your FetchXML is ready, you can use this FetchXML in web API to retrieve data from CRM.

Here is sample code for how to execute FetXML and get values from FetchXML result set

ExecuteFetchXML: function () {      

        var accountFetchXML = ['< fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">' +

            '< entity name="account">' +

            '< attribute name="name" />' +

            '< attribute name="accountid" />' +

            '< attribute name="numberofemployees" />' +

            '< attribute name="processid" />' +

            '< attribute name="donotpostalmail" />' +

            '< attribute name="statuscode" />' +

            '< attribute name="statecode" />' +

            '< attribute name="telephone1" />' +

            '< attribute name="ownerid" />' +

            '< attribute name="description" />' +

            '< attribute name="primarycontactid" />' +

            '< attribute name="address1_longitude" />' +

            '< attribute name="exchangerate" />' +

            '< attribute name="createdon" />' +

            '< attribute name="openrevenue" />' +

            '< attribute name="industrycode" />' +

            '< attribute name="shippingmethodcode" />' +

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

            '< filter type="and">' +

               '< condition attribute="industrycode" operator="not-null" />' +

            '</ filter>' +

            '< link-entity name="contact" from="contactid" to="primarycontactid" visible="false" link-type="outer" alias="prmContact">' +

               '< attribute name="telephone3" />' +

               '< attribute name="spousesname" />' +

               '< attribute name="ownerid" />' +

               '< attribute name="gendercode" />' +

               '< attribute name="description" />' +

               '< attribute name="createdby" />' +

               '< attribute name="address1_latitude" />' +

               '< attribute name="exchangerate" />' +

               '< attribute name="createdon" />' +

               '< attribute name="annualincome" />' +

             '</ link-entity>' +

           '</ entity>' +

         '</ fetch>'].join('');

        var encodedFetchXML = encodeURIComponent(accountFetchXML);

        var req = new XMLHttpRequest();

        req.open("GET", Xrm.Page.context.getClientUrl() + "/api/data/v8.0/accounts?fetchXml=" + encodedFetchXML, true);

        req.setRequestHeader("OData-MaxVersion", "4.0");

        req.setRequestHeader("OData-Version", "4.0");

        req.setRequestHeader("Accept", "application/json");

        req.setRequestHeader("Prefer", "odata.include-annotations=\"OData.Community.Display.V1.FormattedValue\"");

        req.onreadystatechange = function () {

          if (this.readyState === 4) {

            req.onreadystatechange = null;

          if (this.status === 200) {

            var results = JSON.parse(this.response);

              for (var i = 0; i < results.value.length; i++) {                        

               var accountDetails = results.value[i];

               //Single Line Text

               var nameValue = accountDetails['name'];

               var telephone1Value = accountDetails['telephone1'];

               //UniqueIdentifier

               var accountidValue = accountDetails['accountid'];

               var processidValue = accountDetails['processid'];

               //Whole Number

               var numberofemployeesValue = accountDetails['numberofemployees'];

               //Two Option

               var donotpostalmailValue = accountDetails['donotpostalmail'];

               var donotpostalmailTextValue = accountDetails['donotpostalmail@OData.Community.Display.V1.FormattedValue'];

               //Status Reason

               var statuscodeValue = accountDetails['statuscode'];

               var statuscodeTextValue = accountDetails['statuscode@OData.Community.Display.V1.FormattedValue'];

               //Status

               var statecodeValue = accountDetails['statecode']; //Status

               var statecodeTextValue = accountDetails['statecode@OData.Community.Display.V1.FormattedValue'];

               //Owner

               var owneridValue = accountDetails['_ownerid_value'];

               var OwnerName = accountDetails["_ownerid_value@OData.Community.Display.V1.FormattedValue"]

               //Multiple Line

               var descriptionValue = accountDetails['description'];

               //Lookup

               var primarycontactidValue= accountDetails['_primarycontactid_value'];

               var primarycontactidName = accountDetails['_primarycontactid_value@OData.Community.Display.V1.FormattedValue'];

                  //Floating Point Number

                  var address1_longitudeValue= accountDetails['address1_longitude'];

                  //Decimal

                  var exchangerateValue = accountDetails['exchangerate'];

                  //Date Time

                  var createdonValue = accountDetails['createdon'];

                  //Currency

                  var openrevenueValue = accountDetails['openrevenue'];

                  //Option set

                  var industrycodeValue = accountDetails['industrycode'];

                  var industrycodeTextValue = accountDetails['industrycode@OData.Community.Display.V1.FormattedValue'];

                 //Contact - Linked entity data

                 //Single Line Text

                 var prmContact_telephone3Value = accountDetails['prmContact_x002e_telephone3'];

                 var prmContact_spousesnameValue = accountDetails['prmContact_x002e_spousesname'];

                 //Owner

                 var prmContact_owneridValue = accountDetails['prmContact_x002e_ownerid'];

                 //Option set

                 var prmContact_gendercodeValue = accountDetails['prmContact_x002e_gendercode'];

                 var prmContact_gendercodeTextValue = accountDetails['prmContact_x002e_gendercode@OData.Community.Display.V1.FormattedValue'];

                 //Multiple Line

                 var prmContact_descriptionValue = accountDetails['prmContact_x002e_description'];

                 //Lookup

                 var prmContact_createdbyValue = accountDetails['prmContact_x002e_createdby'];

                 //Floating Point Number

                 var prmContact_address1_latitudeValue = accountDetails['prmContact_x002e_address1_latitude'];

                 //Decimal

                 var prmContact_exchangerateValue = accountDetails['prmContact_x002e_exchangerate'];

                 //Date Time

                 var prmContact_createdonValue = accountDetails['prmContact_x002e_createdon'];

                 //Currency

                 var prmContact_annualincomeValue = accountDetails['prmContact_x002e_annualincome'];

                    }

                }

                else {

                    alert(this.statusText);

                }

            }

        };

        req.send();

    },

Remember when using fetchXML query in WEB API

1.      You need to URL encoded FetchXML

2.      With FetchXML you can apply paging by setting page and count attribute of the fetch element.