Executing Fetch XML With WebAPI In Dynamics 365 Using JavaScript

In Dynamics 365 WebAPI, we can retrieve data using OData queries, but if there is some complex requirement, it’s better to query using fetchXML. Now, the good news is Dynamics 365 WebAPI supports querying using fetchXML. With fetchXML, we have two main advantages - it can be easily generated using Advanced Find, and it is more readable. You can even use the Joins and Aggregate functions in fetchXML queries which are not possible using Advanced Find (refer http://msxrmtools.com/fetchxml/reference). Let’s see how we can query fetchXML with WebAPI.

Generate fetchXML using Advanced Find

Click on funnel icon to open Advanced Find window.

I have created a simple fetchXML query to retrieve all the active cases with few columns.

 

Click on “Download Fetch XML” button to get the below fetchXML query.

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
  <entity name="incident">
    <attribute name="title" />
    <attribute name="ticketnumber" />
    <attribute name="createdon" />
    <attribute name="incidentid" />
    <attribute name="caseorigincode" />
    <order attribute="title" descending="false" />
    <filter type="and">
      <condition attribute="statecode" operator="eq" value="0" />
    </filter>
  </entity>
</fetch>

Executing fetchXML with GET method

To execute fetchXML, you need to simply append entity’s plural name in WebAPI endpoint and pass fetchXml in query string, and make http/ajax request.

Syntax

WebAPI Endpoint + / + Entity Plural Name + ?fetchXml= + Your fetchXml here.

Example

https://AshV.crm.dynamics.com/api/data/v9.0/incidents?fetchXml=<fetchversion="1.0"output-format="xml-platform"mapping="logical"distinct="false"><entityname="incident"><attributename="title"/><attributename="ticketnumber"/><attributename="createdon"/><attributename="incidentid"/><attributename="caseorigincode"/><orderattribute="title"descending="false"/><filtertype="and"><conditionattribute="statecode"operator="eq"value="0"/></filter></entity></fetch>

The response of this request would be similar to the one give below. As this is a simple GET call, you can even execute this in the browser and test, you can check out one of my tools, FetchXmlTester, which lets you test your fetchXML without sharing your credentials. While running in the browser, we can’t set header Prefer: odata.include-annotations=”*“ to see formatted value.

{
   "@odata.context":"https://abnai.crm.dynamics.com/api/data/v9.0/$metadata#incidents(title,ticketnumber,createdon,incidentid,caseorigincode)",
   "value":[
      {
         "@odata.etag":"W/\"1095635\"",
         "title":"Average order shipment time",
         "ticketnumber":"CAS-01213-P8B3X0",
         "createdon":"2017-01-20T22:50:45Z",
         "incidentid":"b69e62a8-90df-e311-9565-a45d36fc5fe8",
         "caseorigincode":3
      },
      {
         "@odata.etag":"W/\"1092817\"",
         "title":"Shipping time information resend",
         "ticketnumber":"CAS-01261-N0C8H9",
         "createdon":"2017-01-20T22:51:13Z",
         "incidentid":"169f62a8-90df-e311-9565-a45d36fc5fe8",
         "caseorigincode":2
      }
   ]
}

Making GET request with XMLHttpRequest

var fetchXmlQuery = `
<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
  <entity name="incident">
    <attribute name="title" />
    <attribute name="ticketnumber" />
    <attribute name="createdon" />
    <attribute name="incidentid" />
    <attribute name="caseorigincode" />
    <order attribute="title" descending="false" />
    <filter type="and">
      <condition attribute="statecode" operator="eq" value="0" />
    </filter>
  </entity>
</fetch>`;

var req = new XMLHttpRequest();
req.open(
  "GET",
  Xrm.Page.context.getClientUrl() +
    "/api/data/v9.0/incidents?fetchXml=" +
    encodeURIComponent(fetchXmlQuery),
  true
);
req.setRequestHeader("Prefer", 'odata.include-annotations="*"');
req.onreadystatechange = function() {
  if (this.readyState === 4) {
    req.onreadystatechange = null;
    if (this.status === 200) {
      var results = JSON.parse(this.response);
      console.dir(results);
    } else {
      alert(this.statusText);
    }
  }
};
req.send();

Making GET request with JavaScript Fetch API

var fetchXmlQuery = `
<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
  <entity name="incident">
    <attribute name="title" />
    <attribute name="ticketnumber" />
    <attribute name="createdon" />
    <attribute name="incidentid" />
    <attribute name="caseorigincode" />
    <order attribute="title" descending="false" />
    <filter type="and">
      <condition attribute="statecode" operator="eq" value="0" />
    </filter>
  </entity>
</fetch>`;

fetch(
  Xrm.Page.context.getClientUrl() +
    "/api/data/v9.0/incidents?fetchXml=" +
    encodeURIComponent(fetchXmlQuery),
  {
    credentials: "same-origin",
    headers: {
      Prefer: 'odata.include-annotations="*"'
    }
  }
)
  .then(response => response.json())
  .then(result => console.dir(result))
  .catch(error => console.error("Error:", error));

Executing Large fetchXML using POST method with $batch

As I mentioned in the beginning, we can choose to use fetchXML over OData query for complex queries which will be obviously bigger in size. But we have URL length limit which is around 2000 characters roughly (varies in different browsers) so if our URL extends this, the Bad Gateway exception is thrown.

$batch is solution for this. In Dynamics 365 WebAPI, we can execute bigger GET requests using $batch. We need to set header Content-Type: multipart/mixed;boundary=batch_fetchquery and content of GET request should go in body enclosed within value given in boundary, which is batch_fetchquery in our case. To learn more about $batch, you can refer here.

Making POST request with XMLHttpRequest

var fetchXmlQuery = `
<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
  <entity name="incident">
    <attribute name="title" />
    <attribute name="ticketnumber" />
    <attribute name="createdon" />
    <attribute name="incidentid" />
    <attribute name="caseorigincode" />
    <order attribute="title" descending="false" />
    <filter type="and">
      <condition attribute="statecode" operator="eq" value="0" />
    </filter>
  </entity>
</fetch>`;

var req = new XMLHttpRequest();
req.open(
  "POST",
  Xrm.Page.context.getClientUrl() + "/api/data/v9.0/$batch",
  true
);
req.setRequestHeader(
  "Content-Type",
  "multipart/mixed;boundary=batch_fetchquery"
);
req.onreadystatechange = function() {
  if (this.readyState === 4) {
    req.onreadystatechange = null;
    if (this.status === 200) {
      var result = JSON.parse(
        this.response.substring(
          this.response.indexOf("{"),
          this.response.lastIndexOf("}") + 1
        )
      );
      console.dir(result);
    } else {
      console.error(this.statusText);
    }
  }
};

var body =
  "--batch_fetchquery\n" +
  "Content-Type: application/http\n" +
  "Content-Transfer-Encoding: binary\n" +
  "\n" +
  "GET " +
  Xrm.Page.context.getClientUrl() +
  "/api/data/v9.0/incidents?fetchXml=" +
  encodeURIComponent(fetchXmlQuery) +
  " HTTP/1.1\n" +
  'Prefer: odata.include-annotations="*"\n' +
  "\n" +
  "--batch_fetchquery--";

req.send(body);

Making POST request with JavaScript Fetch API

var fetchXmlQuery = `
<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
  <entity name="incident">
    <attribute name="title" />
    <attribute name="ticketnumber" />
    <attribute name="createdon" />
    <attribute name="incidentid" />
    <attribute name="caseorigincode" />
    <order attribute="title" descending="false" />
    <filter type="and">
      <condition attribute="statecode" operator="eq" value="0" />
    </filter>
  </entity>
</fetch>`;

fetch(Xrm.Page.context.getClientUrl() + "/api/data/v9.0/$batch", {
  body:
    "--batch_fetchquery\n" +
    "Content-Type: application/http\n" +
    "Content-Transfer-Encoding: binary\n" +
    "\n" +
    "GET " +
    Xrm.Page.context.getClientUrl() +
    "/api/data/v9.0/incidents?fetchXml=" +
    encodeURIComponent(fetchXmlQuery) +
    " HTTP/1.1\n" +
    'Prefer: odata.include-annotations="*"\n' +
    "\n" +
    "--batch_fetchquery--",
  headers: {
    "Content-Type": "multipart/mixed;boundary=batch_fetchquery"
  },
  credentials: "same-origin",
  method: "POST"
})
  .then(response => response.text())
  .then(data => {
    var result = JSON.parse(
      data.substring(data.indexOf("{"), data.lastIndexOf("}") + 1)
    );
    console.log(result);
  })
  .catch(error => console.error("Error:", error));

Testing the code in Dyanmics 365

To test the above code, I made small tweaks to run it from HTML web resource as you can see below.

<!DOCTYPE html>
<html>
<head>
    <title>Execute fetchXML with WebAPI in Dynamics 365 using JavaScript</title>
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/milligram/1.3.0/milligram.min.css" integrity="sha256-Ro/wP8uUi8LR71kwIdilf78atpu8bTEwrK5ZotZo+Zc=" crossorigin="anonymous" />
    <style> button { width:100% } </style>
</head>
<body>
    <h1>Testing : Execute fetchXML with WebAPI in Dynamics 365 using JavaScript</h1>
        <button onclick="GetXHR()" id="GetXHR">Test using GET with XMLHttpRequest</button>
        <button onclick="GetFetch()" id="GetFetch">Test using GET with Fetch API</button>
        <button onclick="PostXHR()" id="PostXHR">Test using POST with XMLHttpRequest</button>
        <button onclick="PostFetch()" id="PostFetch">Test using POST with Fetch API</button>
    <script>
        var fetchXmlQuery = `
            <fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
              <entity name="incident">
                <attribute name="title" />
                <attribute name="ticketnumber" />
                <attribute name="createdon" />
                <attribute name="incidentid" />
                <attribute name="caseorigincode" />
                <order attribute="title" descending="false" />
                <filter type="and">
                  <condition attribute="statecode" operator="eq" value="0" />
                </filter>
              </entity>
            </fetch>`;
        var clientURL = location.protocol + '//' + location.host

        function GetXHR() {
            var req = new XMLHttpRequest();
            req.open(
                "GET",
                clientURL +
                "/api/data/v9.0/incidents?fetchXml=" +
                encodeURIComponent(fetchXmlQuery),
                true
            );
            req.setRequestHeader("Prefer", 'odata.include-annotations="*"');
            req.onreadystatechange = function () {
                if (this.readyState === 4) {
                    req.onreadystatechange = null;
                    if (this.status === 200) {
                        var results = JSON.parse(this.response);
                        console.dir(results);
                        alert(JSON.stringify(results));
                    } else {
                        alert(this.statusText);
                    }
                }
            };
            req.send();
        }

        function GetFetch() {
            fetch(
                clientURL +
                "/api/data/v9.0/incidents?fetchXml=" +
                encodeURIComponent(fetchXmlQuery),
                {
                    credentials: "same-origin",
                    headers: {
                        Prefer: 'odata.include-annotations="*"'
                    }
                }
            )
                .then(response => response.json())
                .then(result => { console.dir(result); alert(JSON.stringify(result)); })
                .catch(error => console.error("Error:", error));
        }

        function PostXHR() {
            var req = new XMLHttpRequest();
            req.open(
                "POST",
                clientURL + "/api/data/v9.0/$batch",
                true
            );
            req.setRequestHeader(
                "Content-Type",
                "multipart/mixed;boundary=batch_fetchquery"
            );
            req.onreadystatechange = function () {
                if (this.readyState === 4) {
                    req.onreadystatechange = null;
                    if (this.status === 200) {
                        var result = JSON.parse(
                            this.response.substring(
                                this.response.indexOf("{"),
                                this.response.lastIndexOf("}") + 1
                            )
                        );
                        console.dir(result);
                        alert(JSON.stringify(result));
                    } else {
                        console.error(this.statusText);
                    }
                }
            };

            var body =
                "--batch_fetchquery\n" +
                "Content-Type: application/http\n" +
                "Content-Transfer-Encoding: binary\n" +
                "\n" +
                "GET " +
                clientURL +
                "/api/data/v9.0/incidents?fetchXml=" +
                encodeURIComponent(fetchXmlQuery) +
                " HTTP/1.1\n" +
                'Prefer: odata.include-annotations="*"\n' +
                "\n" +
                "--batch_fetchquery--";

            req.send(body);
        }

        function PostFetch() {
            fetch(clientURL + "/api/data/v9.0/$batch", {
                body:
                    "--batch_fetchquery\n" +
                    "Content-Type: application/http\n" +
                    "Content-Transfer-Encoding: binary\n" +
                    "\n" +
                    "GET " +
                    clientURL +
                    "/api/data/v9.0/incidents?fetchXml=" +
                    encodeURIComponent(fetchXmlQuery) +
                    " HTTP/1.1\n" +
                    'Prefer: odata.include-annotations="*"\n' +
                    "\n" +
                    "--batch_fetchquery--",
                headers: {
                    "Content-Type": "multipart/mixed;boundary=batch_fetchquery"
                },
                credentials: "same-origin",
                method: "POST"
            })
                .then(response => response.text())
                .then(data => {
                    var result = JSON.parse(
                        data.substring(data.indexOf("{"), data.lastIndexOf("}") + 1)
                    );
                    console.log(result);
                    alert(JSON.stringify(result));
                })
                .catch(error => console.error("Error:", error));
        }
    </script>
</body>
</html>

Create a new HTML web resource with the above code & publish it.

Click on the URL to open it, you will see the below window.

Press F12 to open the console. Click on buttons & verify the retrieved data in console or alert.

Thanks for reading; I hope it helps!