Sql To Caml Query Converter Word

1. Sql To Caml Query Converter Word 2013

CAML is also used to define tables in the Windows SharePoint Services database during site provisioning.” So, CAML is a special XML based query language used to well query data from SharePoint. I describe it as a combination of XML, SQL, and WTF At first glance it appears to be overly complicated and cumbersome. Oracle, IBM DB2, Microsoft SQL Server, Sybase ASE, Sybase SQL Anywhere, Sybase Advantage, Informix, Hive, MySQL, MariaDB, PostgreSQL, Redshift, Teradata, Greenplum, Netezza. Support@sqlines.com / Download Tool, Target: Microsoft SQL Server, Oracle, IBM DB2, Hive, MySQL, MariaDB, PostgreSQL, Redshift.

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).

Sql To Caml Query Converter Word 2013

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.

to

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!

7 Answers

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.

brian brinleybrian brinley

I was also hoping to find a piece of code to convert CAML to SQL in order to build my own SQL Statement to access the data.

My main project is to build a SharePoint ribbon extension to export Lists (Internal and External) content to CSV and in the case of external lists, to be able to bypass the throttling limit (2000) imposed in External Content Types (BCS).

I'm using the information in the metada store and the secure store to build the connection string and access the Database directly.

When I needed to improve my code to include filters, I ended up building my own methods to get the 'Where' part of a View Query and convert it to a SQL-Like Where statement:

Input:

In my case it's a SPView object but it can easily be converted to use a string instead. I'm extracting the 'CAML' like query from it.

Ouput:

Here are the methods:

This method will extract the 'Where' node from a view Query andpass it to a method to process it and return a SQL like statement.

This method will call another method to recursively go through all the nodes to get the values and operators within the View Query 'Where' node. It will put round bracket around 'OR' statements to conserve the operation priority.

This method does most of the work to go throught an iterate each nodes:

This last methods could probably been included in the recursive one but in my first iteration of building the code it made more sense to make a separate one and I kept it this way.

It simply gets some information on the operators and associates an operator string which will be used to construct the individual pieces of the SQL Where Statement.

I know it's not a full conversion tool but it's a start and for now it fits my need. I hope this will help someone and save them some valuable time.

My very simple class convert from string sql to caml , for example:

sql = ....

CAML output is .....

Class src:

(bob.)

It's possible if you use some sort of ado.net connector for SharePoint, have a look at http://www.bendsoft.com/net-sharepoint-connector/

It enables you to talk to SharePoint lists as if they where ordinary sql tables

In example to insert some data

Or to select list data to a DataTable

Or using a helper method to fill a DataGrid

Here is a webcast illustrating how to build a simple querybrowser for SharePoint, http://www.youtube.com/watch?v=HzKVTZEsL4Y

Hope it helps!

Eric HerlitzEric Herlitz

Microsoft supports SharePoint List data access through:1. SharePoint Object Model – SPSite and SPWeb2. Lists web service. Path to access is http://Your_Site/_vti_bin/lists.asmx

Any Add/Update/Delete/Select operations on the SharePoint List is done using any of the above 2 methods goes through the unmanaged COM component. This COM is responsible for establishing the connection with Content DB; applying Data Locks on the Tables and data retrieval. This COM component has its own Logic/Mechanism to apply Data Lock on the Content DB tables and Sharepoint developer does not have control over the Data Lock mechanism. If T-SQL statements (Add/Update/Delete/Select) are executed directly on the Content DB tables, this In-Build logic may break and can be resulted into unknown results or errors. Microsoft does not support direct T-SQL statement execution on Content DB tables.

i have checked the code posted by Francis with the following CAML:

And it doesn't work... the result, in this case, will be: F1<>'Yes' AND ( F2<>'Yes' OR F3='Yes' ).

I made some fixes inside the following method:

This will group the couple inside the parentheses ... and that's all

thanks for your help, i need that too, your code was good but i improve it a little :

• operator is null and isnotnull wasn't handle
• a little bug about escaping with like operator

be careful the code doesn't escape the column name (you have to do it depending of your database engine)

here is the code in a static class:

Not the answer you're looking for? Browse other questions tagged tsqlsharepointsharepoint-2007caml or ask your own question.

I want to convert my sql query to caml query and my sql query is

sql query:

Aakash Maurya

2 Answers

Look at CAML Query Helper, it will build the query for you https://spcamlqueryhelper.codeplex.com/

Marek SaradMarek Sarad

Are you looking for a converter or just to convert this query?

You should change column types what you need.

newbienewbie

Not the answer you're looking for? Browse other questions tagged sharepoint-onlinesql or ask your own question.