Search code examples
t-sqlsharepointsharepoint-2007caml

SharePoint CAML Query to T-SQL

I was hoping to do convert a CAML query into T-SQL. For my SharePoint site, I also have a data warehouse (SQL Server 2005) which hosts the same data (it is populated through SPList EventReceivers: ItemAdded, ItemUpdated).

I am involved in a task to handle the growth of the lists and the speed of custom webparts that display list data. I am hoping instead to display our list contents using the data warehouse and then we are going to remove items from the SPLists where the workflow has been completed.

Currently, the user of the custom webpart that displays the SPList contents is allowed to select an SPView to display the content in the way they would like (i.e. filtering, sorting and only showing necessary columns). I would like to keep this functionality and was hoping to take the view's CAML query and convert it to T-SQL to query against the data warehouse.

e.g.

<Query>
  <Where>
    <Or>
      <Eq>
        <FieldRef Name="ContentType" />
        <Value Type="Text">My Content Type</Value>
      </Eq>
      <IsNotNull>
        <FieldRef Name="Description" />
      </IsNotNull>
    </Or>
  </Where>
  <Order>
    <FieldRef Name="Author" />
    <FieldRef Name="AuthoringDate" />
    <FieldRef Name="AssignedTo" Ascending="True" />
  </Order>
  <Group>
    <FieldRef Name="Title" />
  </Group>
</Query>

to

WHERE ContentType="My Content Type"
OR Description<>null
GROUPBY Title DESC
ORDERBY Author, AuthoringDate, AssignedTo ASC

Does anyone know how to do this? Or have an alternative suggestion that would solve this issue? I have found multiple solutions for converting T-SQL to CAML query, just not the other way around (i.e. http://yacamlqt.codeplex.com/ -- which is also where I retrieved the example from)

Thanks!

Solution

  • This is not technically an answer but I feel its needed. Your approach is a bit backwards. What it sounds to me like you are really trying to do is use sharepoint as a UI to your data warehouse. If that is the case I would switch your approach to one of several options outside the scope of this question.

    Data access in sharepoint is only supported by microsoft through the object model, web services or the user interface. Any interaction outside this can result in a vast range of unsupported modes, anything from corrupt data, data locks, varying result sets, security access, etc...

    What it sounds like really need is the BDC services. This would allow you to use the data warehouse for central storage and also provide you with the sharepoint native functionality of the list interactions.

    I'm not sure which version you are using but if you REALLY want to hit the data directly there are a large amount of articles in the SQL community about accessing the data directly. There is also a codeplex project for LINQtoSharePoint http://linqtosharepoint.codeplex.com/ You can use this for object support that you can use LINQ to give you sql-esque type functionality.