Friday, February 5, 2010

SharePoint Querying - CAML Query usage when special characters in DATA

We have many ways of pulling information from SharePoint sites. Depends on the requirements we may use web services or using SharePoint object model. If you have requirement to filter data from a SharePoint list then the best option is through CAML queries we will write a simple query and run it against the SharePoint list and get the collection of data. So, if you are familiar with CAML queries then no need to say how to write and how they will execute. But, this post main focus is on what if the data which you are querying contains the special characters like '&'. The query won't run and it fails. Take the example of below code.
 <Where><Contains><FieldRef Name='Description' /><Value Type='Note'>Praveen&Battula</Value></Contains></Where>
The above query will leads to errors and you see the error in the logs. So, what is the problem? There are no syntax errors and everything is perfect. But, where is the problem? The problem is as we discussed the symbol '&' in the data [Praveen&Battula]. So, how to solve this problem? Below is the simple solution.
<Where><Contains><FieldRef Name='Description' /><Value Type='Note'><![CDATA[Praveen&Battula]]></Value></Contains></Where>

I think, by seeing above syntax, you may get the answer. :). What I did is, we are familar with XML and we know how to handle special characters in XML[Using <![CDATA[Some DATA]]>]. So, I used the same logic here and everything started working as expected.

**UPDATED**
From the comments, I found a special scenario of why it fails even we use CDATA. Check it below.
http://social.technet.microsoft.com/Forums/en-US/sharepointdevelopment/thread/8b442d22-eb56-40d8-a487-a325d3a70626/
I am trying to solve this, but for now, the only solution is through encode it and query it.

Please let me know, your ideas on it.

6 comments:

  1. There is another way to use special symbols in CAML. Details here: http://social.technet.microsoft.com/Forums/en-US/sharepointdevelopment/thread/8b442d22-eb56-40d8-a487-a325d3a70626/

    ReplyDelete
  2. Oh!!! It's through coding. Yes, I have read the forum post and now started thinking, there is one case the solution I have given would fail. That is, when data contains the ']' character in the data... It thinks that the CDATA[[ tag is ending by seeing the closing square bracket ']'in data. Now, I need to research on it, how to deal with it without coding...

    thanks
    -Praveen.

    ReplyDelete
  3. Excellent...
    This is what I am looking for.
    Thanks Praveen.

    ReplyDelete
  4. To handle the bug stated, put a space before the double ending bracket in your code.

    Example (VB):
    Dim str as string = "This is some code]"

    Dim strQry as string = ""

    The space ensures that the CDATA method does not confuse which is the text v.s. closing brackets.

    ReplyDelete
  5. Thanks for the tip. Will try and let you know if that works....

    -Praveen.

    ReplyDelete
  6. Thanks a lot Praveen for posting such an useful post.
    Really appreciable!! I was searching for this.
    Thanks Again..

    ReplyDelete