Fetch XML, the easiest way to write complex queries to retrieve data by joining multiple entities.
Though being the easiest, we were still stuck at a point where we didn’t know what to do. We were getting Invalid XML error.
For quite some time we were wondering what could have caused this. Below is our Fetch XML.
What is this XML for? Through this XML, we are trying to find the Forecast record with the specified Forecast Name.
Code:
string forecastName = “City & Lights (Sample) December 2014”;
string xml = @”<fetch version=’1.0′ output-format=’xml-platform’ mapping=’logical’ encoding=’Windows-1252′ distinct=’false’>” +
“<entity name=’new_forecast’>” +
“<attribute name=’new_forecastid’ />” +
“<attribute name=’new_name’ />” +
“<order attribute=’new_name’ descending=’false’ />” +
“<filter type=’and’>” +
“<condition attribute=’new_name’ operator=’eq’ value='” forecastName + “‘ />” +
“</filter>” +
“</entity>” +
“</fetch>”;
Things were pretty good till the time we didn’t have any special character in the forecast name (For this demo we have hard coded the Forecast Name, in reality it is dynamically created). Then came the sore part, we got a Forecast Name with a special character “&” in it. It took us around 45 minutes to figure out the reason of getting Invalid XML error.
The reason for the Invalid XML error was the special character “&”. We need to encode the special characters before using it in a Fetch XML.
Then, we tried encoding the forecastName. We used the below assembly & code to encode the forecastName.
Assembly Used: System.Web
Code Used: HttpUtility.HtmlEncode(forecastName)
Now, we got the below error:
While investigating this issue, we found that System.Web Assembly is not supported for Plug-In registered in Sandbox mode. And, HttpUtility.HtmlEncode(string) needs System.Web Assembly.
Now, what? We are again stuck with special characters in Fetch XML, giving us Invalid XML.
Hush! nothing to worry, we have WebUtility as our savior.
What all things are we suppose to do now?
Well, we just have to use a namespace and that`s it we are good to go.
Namespace: System.Net
Code Used: WebUtility.HtmlEncode(forecastName)
Now, the final XML look like this,
string xml = @”<fetch version=’1.0′ output-format=’xml-platform’ mapping=’logical’ encoding=’Windows-1252′ distinct=’false’>” +
“<entity name=’new_forecast’>” +
“<attribute name=’new_forecastid’ />” +
“<attribute name=’new_name’ />” +
“<order attribute=’new_name’ descending=’false’ />” +
“<filter type=’and’>” +
“<condition attribute=’new_name’ operator=’eq’ value='” + WebUtility.HtmlEncode(forecastName) + “‘ />” +
“</filter>” +
“</entity>” +
“</fetch>”;
The special characters in Fetch XML being the sore point once, now is very easy to tackle.