FetchXML + jQuery in a Dynamics CRM 2011 web resource

function executeFetchCommand() { 
	/// <summary>
    /// reads user-supplied FetchXML contained in the txtFetch textarea and starts processing
    /// </summary>
	
	//generate the SOAP envelope
	var fetchRequest = buildFetchRequest($("textarea#txtFetch").val());

	//send the request to CRM
	sendQuery(fetchRequest);
}

function buildFetchRequest(fetch) {
	/// <summary>
    /// builds a properly formatted FetchXML request
	/// based on Paul Way's blog post "Execute Fetch from JavaScript in CRM 2011"
	/// http://blog.customereffective.com/blog/2011/05/execute-fetch-from-javascript-in-crm-2011.html
    /// </summary>
	var request = "<s:Envelope xmlns:s=\"http://schemas.xmlsoap.org/soap/envelope/\">";
	request += "<s:Body>"; 

	request += '<Execute xmlns="http://schemas.microsoft.com/xrm/2011/Contracts/Services">' + 
	'<request i:type="b:RetrieveMultipleRequest" ' + 
	' xmlns:b="http://schemas.microsoft.com/xrm/2011/Contracts" ' + 
	' xmlns:i="http://www.w3.org/2001/XMLSchema-instance">' + 
	'<b:Parameters xmlns:c="http://schemas.datacontract.org/2004/07/System.Collections.Generic">' + 
	'<b:KeyValuePairOfstringanyType>' + 
	'<c:key>Query</c:key>' + 
	'<c:value i:type="b:FetchExpression">' + 
	'<b:Query>';

	request += CrmEncodeDecode.CrmXmlEncode(fetch); 

	request += '</b:Query>' + 
	'</c:value>' + 
	'</b:KeyValuePairOfstringanyType>' + 
	'</b:Parameters>' + 
	'<b:RequestId i:nil="true"/>' + 
	'<b:RequestName>RetrieveMultiple</b:RequestName>' + 
	'</request>' + 
	'</Execute>'; 

	request += '</s:Body></s:Envelope>'; 
	return request;
}

function sendQuery(fetchRequest) {
	/// <summary>
    /// uses jQuery ajax method to executes a FetchXML query and register a callback function
    /// </summary>
	
	//path to CRM root
	var server = window.location.protocol + "//" + window.location.host;
	
	//full path to CRM organization service - you may need to modify this depending on your particular situation
	var path = server + "/XRMServices/2011/Organization.svc/web";
	
	$("#outputdiv").append("Starting ajax request . . . <br />");
	$.ajax({
		type: "POST",
		dataType: "xml",
		contentType: "text/xml; charset=utf-8",
		processData: false,
		url: path,
		data: fetchRequest,
		beforeSend: function( xhr ){
		xhr.setRequestHeader( //without the SOAPAction header, CRM will return a 500 error
		"SOAPAction",
		"http://schemas.microsoft.com/xrm/2011/ Contracts/Services/IOrganizationService/Execute" //remove the extra space inserted to help this line wrap
		); 
		}
	}).done(function(data) {
		//if successful, pass returned xml and the plot label to the charting function
		processData(data);
	}).fail(function(jqXHR, textStatus, errorThrown ) {
		//if unsuccessful, generate an error alert message
		alert( "Request failed: " + textStatus + "\n" + errorThrown  );
	});
	$("#outputdiv").append("Ajax request complete. Output will be processed asynchronously. <br />");
}

1. The inside the "done" function is where we tell jQuery what to do when a successful response returns data. We will look at the processData method more closely in the next section.
2. The "fail" function generates an alert window with error output. You could do something more sophisticated.
3. You probably want to leave all the other ajax configuration options alone unless you have a good reason not to. It took a good bit of trial and error for me to get this right (probably because I was up too late at night working it out, but I digress . . . ).

<fetch distinct='false' mapping='logical' aggregate='true'>
	<entity name='contact'>
	   <attribute name='fullname' alias='contact_count' aggregate='count' />
	   <attribute name='createdon' groupby='true' dategrouping='day' alias='day' />
	   <attribute name='createdon' groupby='true' dategrouping='month' alias='month' />
	   <attribute name='createdon' groupby='true' dategrouping='year' alias='year' />
	</entity>
</fetch>

1. Each row of data is represented as an "a:Entity" element.
2. Individual attribute names and values can be found in the an entity's "a:Attributes" element and also in its "a:FormattedValues" element. In this example I will be working with the "a:FormattedValues" element because it's marginally easier to parse, however you may find times that it makes more sense to work with "a:Attributes" instead. 
3. Everything under "a:FormattedValues" is represented as a string, so we would need to convert to other datatypes where necessary (ie. building a date object from the returned month / day / year values).
4. Each key-value pair under "a:FormattedValues" is contained in an "a:KeyValuePairOfstringstring" element.

function processData(xml) {
	/// <summary>
    /// writes key-value pairs to a div
    /// </summary>

	alert("Output started");
	$("#outputdiv").append("OUTPUT FROM CRM <br />");
	
	//outer loop
	$(xml).find("a\\:Entity").each(function() {
		//write entity
		$("#outputdiv").append("Entity <br />");
		//inner loop
		$(this).find("a\\:KeyValuePairOfstringstring").each(function() {
			var xmlElement = $(this);
			var key = xmlElement.find("b\\:key").text();
			var value = xmlElement.find("b\\:value").text();
			$("#outputdiv").append("  Key: " + key + " Value: " + value + "<br />");
			
		});
	});
		
	alert("Output complete");
}

You'll see there are two loops that use the jQuery find method. First the outer loop finds each a:Entity element, and then it passes that element to another function that executes the inner loop to find each a:KeyValuePairOfstringstring element. Then that element is passed to another function that extracts the text of the "b:key" and and "b:value" elements and writes them to the web page output div using jQuery's append method.

 

Trying it out

Here is a complete web page with the code for you to download and try out in your own CRM instance: jquery_fetchxml.htm (4.53 kb). Just upload it as you would any other web resource, publish and open. At that point you will be presented with a screen that looks like this: 

 

 

Either leave the pre-populated FetchXML in the textarea or supply your own. Once you click the "fetch and process" button, you will start to see some output and a couple of status alert windows before the output finally displays. The end result should look like this:

 

 

As mentioned earlier, the attribute values retrieved are all strings, so you'll see the years have commas in them (2,013 instead of 2013). The solution is to either get the integer value from the XML response or parse the text value into an integer.