Tuesday, March 16, 2010

FetchXml and the Paging Cookie

So the other day I was trying to extract some data from a CRM live instance. I needed to get more than 5000 records and I was trying to query a many to many relationship table. So I had to use fetchXml as the QueryExpression class can't query a many to many table. To get more than 5000 records I had to enable paging via the paging cookie. Here's how it's done:



// Build fetchXml string with the placeholders.
String fetchXml = @"<fetch version='1.0' mapping='logical'
page='{0}' count='100' paging-cookie='{1}'>
<entity name='neu_neu_settlement_payees'>
<attribute name='neu_neu_settlement_payeesid' />
<attribute name='neu_settlementid' />
<attribute name='contactid' />
</entity>
</fetch>";

fetchXml = String.Format(fetchXml, pageNumber, pagingCookie);

// Excute the fetch query and get the xml result.
String fetchResult = crmService.Fetch(fetchXml);
// Load the fetch result into XMLDocument to parse its cotents.
XmlDocument doc = new XmlDocument();
doc.LoadXml(fetchResult);
// The morerecords attribute will return 1 if there are more records.
String moreRecords = doc.DocumentElement.Attributes["morerecords"].InnerText;

// The paging-cookie attribute holds the paging cookie to pass in the next query.
XmlAttribute at = doc.DocumentElement.Attributes["paging-cookie"];
pagingCookie = (at != null) ? at.InnerXml : string.Empty;

// Retrieve the result nodes.
XmlNodeList resultNodes = doc.DocumentElement.SelectNodes("result");

// Check for morerecords, if it returns 1.
if (moreRecords != null && moreRecords == "1")
{
// Increment the page number to retrieve the next page.
pageNumber++;
}
else
{
// If no more records in the result nodes, exit the loop.
break;
}

The big stumbling block for me was using .InnerXml to get the pagingCookie value; not .InnerText. This code all sits in a while(true) loop.

Have fun...

2 comments:

  1. Hi,
    What are you passing first time in the pagingCookies variable?

    fetchXml = String.Format(fetchXml, pageNumber, pagingCookie);

    Thanks!
    /TRE

    ReplyDelete
  2. Sorry for the delay. I didn't have comment notifications turned on. I'm setting the pagingCookie to String.Empty for the first query. On subsequent queries I'm inserting it.

    ReplyDelete