Foxy Sql Free is a database script and query tool designed for DB professionals. Sql To Caml Query Converter Temperature Conversion. WMI Advanced Query, HTA to EXE Converter, Net. Crunch WMI Tool. Query Reporter is an easy- to- use freeware tool to create and run HTML reports from the results of a SQL query against an Oracle database. Is there any way to execute CAML Query on it. Or Alternatively, How can I convert this DataTable into a SPList so tha CAML can be executed on SPList. Run CAML Query on DataTable SharePoint. Ask Question 0. SharePoint CAML Query to T-SQL. CAML Query not bringing back expected results. How to query greatest ID of each value in a. Is there any way to execute CAML Query on it. Or Alternatively, How can I convert this DataTable into a SPList so tha CAML can be executed on SPList. Run CAML Query on DataTable SharePoint. Ask Question 0. SharePoint CAML Query to T-SQL. CAML Query not bringing back expected results. How to query greatest ID of each value in a. CAML query using OR and AND Condition. Ask Question 0. I need to write a CAML query which satisfies the below condition: projectstatus == 'hold' OR projectstatus == 'obsolete 'AND ilprilt not equal to 'ilp frozen' When I run this query, am getting records where.

I'm trying to convert the following SQL statement to a CAML query:

Using CAMLJS, I got this far: (CompanyNo is equivalent to Nav_CustomerNo)

But I'm not sure how to proceed from here. How do I convert the

line? I am thinking something like:.Where('c1Email').EqualTo(??

Here is the equivalent CAML:

1 Answer

Just spent a considerable time this morning trying to work this out and I think I have a solution for you.

First - let's start with some generic pseudo structure:

ParentTable: Id, Title, ChildTableReference

ChildTable: Id, Title, BabyTableReference

Caml Query Example

BabyTable: Id, Title

So, we have a parent table that contains a reference. The table tied to that reference contains a reference to another table under that.

Here is a simple practical example:

(parent) CustomerOrder: Id, Title, CustomerID

(child) Customer: Id, Title, RegionID

(baby) Region: Id, Title

So, what if we wanted the following query response:

CustomerOrder_Id, CustomerOrder_Title, Customer_Title, Region_Title

The first part of the XML is easy:

But how do we stitch together the tables? We use joins.

The first Join is very simple:

This first join is ok and if all we wanted to do was view the customer name we would just have to add our Projection and we would be good to go. But, we also want the Region of this customer. For this, we need a second join. The trick to the second join is to add a list reference to the FIRST FieldRef statement. The exact line is: FieldRef List='Customers' Name='RegionID' RefType='ID' . Notice that the List= attribute points to the alias of the Child reference. Put another way, there is a reference list called Customers that contains a column called RegionID that is a reftype of ID. That needs to be equal to our new aliased table called Regions based on the root table Region on the ID attribute.

We are almost home now. At this point we need to create Projected Fields to spit out the Region.Title and Customer.Title attributes.

Putting it all together then, your CAML query would be:

That should get you all the way there. Also - you can query (Where clause for instance) on any element you've joined to provided the field type is supported (text, refid, number, etc). Just remember that if you are going to query on say, the ID of the Region you will need to add the ID to your ProjectedFields.

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

I've checked articles on here and Googled till I'm blue in the finger tips. I've read and read and read and just can't seem to wrap my head around CAML Joins in Sharepoint2010.

Caml Query Builder

Question: Can someone please show me a full example of how exactly CAML Join Query compares to SQL Join Query?

So, again, my question is, can I get an exact example of this same operation is inacted upon in Sharepoint 2010 using the CAML Joins Query string?

2 Answers

Create your query from one of your lists.

To do the join, set query.Joins to

and query.ProjectedFields to

Caml Query Orderby

To choose the fields to display set query.ViewFields to

Then

Or something like that (it's from memory!)

Sharepoint Caml Query

My frustration mirrors yours, here's some more tips:

1. Start the query based on the child table in the relationship. (I cannot discern from your example which would be the parent and which the child.)

2. I agree with Rob Windsor that it must be based on a Lookup fields, but from my testing, it must be a lookup to a ListItemID type field. In SharePoint, this is the internal ID field. (I know this, because I have a lookup to a text field, and it just doesn't work. Hours of life wasted.) I currently have a post on the Microsoft Forums asking if the RefType parameter can be anything other than 'ID', so maybe keep an eye on that. Finally, if in the projected fields, the Type parameter must always be 'lookup', then why is it needed?

3. None of the CAML Query Builders(YACQB and U2U) support joins, so don't bother downloading and trying.

Sharepoint List Caml Query

Not the answer you're looking for? Browse other questions tagged c#.netsharepoint-2010caml or ask your own question.