How to query Dynamics CRM Web API using custom FetchXml involving Number value filter?

Question

I am trying to execute fetch XML using WEB API /api/data/v8.x. All is fine except when I have query involving "where a attribute contains number value". For example, the following fetchXML to find account with phone number contains '03':

<fetch mapping="logical">
  <entity name="account">
    <attribute name="name" />
    <attribute name="telephone1" />
    <order attribute="name" descending="false" />
    <filter type="and">
      <condition attribute="telephone1" operator="like" value="%03%" />
    </filter>
  </entity>
</fetch>

When we run it via GET on WEB API :

https://CRM_URL/api/data/v8.0/accounts?fetchXml=<fetch%20mapping%3D"logical"><entity%20name%3D"account"><attribute%20name%3D"name"%20%2F><attribute%20name%3D"telephone1"%20%2F><order%20attribute%3D"name"%20descending%3D"false"%20%2F><filter%20type%3D"and"><condition%20attribute%3D"telephone1"%20operator%3D"like"%20value%3D"%2503%25"%20%2F><%2Ffilter><%2Fentity><%2Ffetch>

CRM Web API returns Invalid XML error as follows:

{ "error":{ "code":"","message":"Invalid XML.","innererror":{ "message":"Invalid XML.","type":"System.ServiceModel.FaultException`1[[Microsoft.Xrm.Sdk.OrganizationServiceFault, Microsoft.Xrm.Sdk, Version=8.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35]]" } } }

The query run alright if the filter value does not start with number or I remove the '%' on the filter value. My conclusion is this is having to do with encoding and decoding "% + number" in my filter value.

Is there anything wrong with my query or is this Dynamics CRM Web API bug? any work around?

Surprisingly, passing single digit number as a parameter works. value='%3' for example. Neither value='%03%' nor value='%30%' works. — dynamicallyCRM

Jatin Sanghvi Jatin Sanghvi · Accepted Answer · 2017-05-09T05:07:05

Here, Dynamics 365 is internally decoding the FetchXML query string twice. The engineering team is informed of this issue. They will provide the fix in the forthcoming release and possibly in the latest public release. Until that, you can try encoding the query string twice or replace all occurrences of % character with %25 after encoding. For example, the string value%3D"%2503%25" can be replaced with value%3D"%252503%2525". Please note that once the fix is available, the workaround suggested will no longer work.

How to query Dynamics CRM Web API using custom FetchXml involving Number value filter?

2 Answers