Recently I was asked about executing a FetchXML query using the Web API in Power Pages, and while you won’t find any specific documentation on that in the Power Pages documentation, you’ll be happy to know that it is supported. In this blog post, I’ll look at how it’s done.

Is It Supported?

As I mentioned, you won’t find a specific reference to FetchXML in the Power Pages Web API documentation. But it was when I was playing around with the sample PCF Control for Power Pages that I noticed Microsoft had included a call to the Web API that uses FetchXML.

Specifically, on line 282 of /PortalWebAPIControl/index.ts you’ll see a RetrieveMultiple call executed that uses FetchXML to calculate an aggregate value.

So, while not specifically mentioned in the documentation, the use of it in a sample is good enough for me to think this is something that is supported.

Syntax

Another reason I feel confident using it is that the format follows the standard format for using FetchXML with the Dataverse Web API, which is documented.

To execute a query, you call the endpoint for the tables you are querying, and then use the fetchXml query string parameter. Be sure to encode the FetchXML using encodeURI().

So a very simple query would look something like (borrowing the webapi wrapper available in the Microsoft documentation):

webapi.safeAjax({

  type: "GET",

  url: "/_api/contacts?fetchXml=" + encodeURI('<fetch><entity name="contact"><attribute name="firstname" /><filter><condition attribute="lastname" operator="eq" value="Hayduk" /></filter></entity></fetch>'),

  contentType: "application/json"

}); 

A few things to note when using the fetchXml parameter:

• You can’t include additional parameters like $top, $orderby or $select. Instead, using the count attribute inside your FetchXML to limit the number of results, use the attribute elements in the FetchXML to select which columns you want returned, and specify the column you want to order by directly in your FetchXML using the element.
• You can do things like calculate averages using the aggregate features in FetchXML. Whatever alias you give the attributes will be available in the returned JSON.
• Table permissions are enforced, as well as the column-level permissions that are specific to the Web API (using the Webapi/[tablename]/fields Site Setting). If you ask for a table or column that you don’t have access to, you’ll get an error.
• You can use the paging cookies if you need to, as described here.

Compared to FetchXML in Liquid

You might already know that we can execute FetchXML using the Liquid tag. With that available to us, why would we want to use it via the Web API?

The nice thing about using the Web API in client-side JavaScript is that we can use it to build interactive user experience that don’t require a page reload. Liquid is a server-side language, so in order to execute FetchXML via Liquid, we have to make a roundtrip to the server.

A common technique is to build a “custom web service” by creating a special page that returns JSON created using Liquid and FetchXML. For that web page’s Page Template you turn the header and footer off, and then create a web template that queries Dataverse using Liquid and FetchXML and outputs data in JSON format. Query string parameters passed to the page can be used in the Liquid code to allow for certain defined aspects of the query to be changed. Then you can call that page from another page using AJAX calls in JavaScript.

We’ve talked about it on our Portals Community call, and others like Carl de Souza have blogged about it.

Does the Web API’s support of FetchXML make that technique obsolete? I don’t think so. That technique is still very useful in cases where you want to protect how you data can be queried.

Once you enable the Web API for a table, you can’t really control how it is queried. If a user has access to a table via the Web API, they can use OData filters or FetchXML to query it however they want (although only rows they have access to will be returned). However, if you leave the Web API turned off, and only expose the data via your own “custom web service”, you limit how they can query it, since the only changes possible to the query are only those made explicitly available via custom query string parameters.

I’ve used this in cases where a table is technically publicly available, but the client only wanted users to be able to find row if the knew the exact name. If we used the Web API for this, a user could have constructed their own query that removes any filter parameter, getting a full list of all the rows. Instead, we created our FetchXML query in Liquid. This query only accepted a single parameter, and only returned data if a row exists that exactly matched based on the name. Since that FetchXML is defined server-side and is therefore not editable by the end user, their only option was to filter by an exact name match.

So while FetchXML via the Web API is great, using it via Liquid is still a valuable option.