CAML error when querying SharePoint List for more than one input

Question

I have a CAML query which retrieves data from a SharePoint list. It passes for 1 input, but fails when there are 2 or more.

Error:

Microsoft.SharePoint.SPException: One or more field types are not installed properly. Go to the list settings page to delete these fields. ---> System.Runtime.InteropServices.COMException: One or more field types are not installed properly. Go to the list settings page to delete these fields.

---

After researching, probable cause may be due to mismatch SharePoint column InternalName . However, query managed to execute for 1 input, but not 2 or more.

Base on the query examples below, am I having a wrong format for the FAIL cases?

---

1. PASS:

<Where>
    <And>
        <Eq>
            <FieldRef Name="Header1Ref"/><Value Type="Text">H1</Value>
        </Eq>
        <Neq>
            <FieldRef Name ="ContentType"/><Value Type="Text">Document</Value>
        </Neq>
    </And>
</Where>

---

2. FAIL:

<Where>
    <And>
        <Eq>
            <And>
                <Or>
                    <Eq>
                        <FieldRef Name="Header1Ref"/><Value Type="Text">H1</Value>
                    </Eq>
                    <Eq>
                        <FieldRef Name="Header1Ref"/><Value Type="Text">H2</Value>
                    </Eq>
                </Or>
                    <Neq>
                        <FieldRef Name ="ContentType"/><Value Type="Text">Document</Value>
                    </Neq>
            </And>
        </Eq>
    </And>
</Where>

3. FAIL:

<Where>
    <And>
        <Eq>
            <And>
                <Or>
                    <FieldRef Name="Header1Ref"/><Value Type="Text">H1</Value>
                    <FieldRef Name="Header1Ref"/><Value Type="Text">H2</Value>
                </Or>
                <Neq>
                    <FieldRef Name ="ContentType"/><Value Type="Text">Document</Value>
                </Neq>
            </And>
        </Eq>
        <Neq>
            <FieldRef Name ="ContentType"/><Value Type="Text">Document</Value>
        </Neq>
    </And>
</Where>

Answer 1

First of all check the internal names and data types of your fields you are using in your CAML query, Accordingly your queries will change.

If all your internal names are correct then try using below queries:

<Where>
    <And>
        <Or>
            <Eq>
                <FieldRef Name="Header1Ref"/><Value Type="Text">H1</Value>
            </Eq>
            <Eq>
                <FieldRef Name="Header1Ref"/><Value Type="Text">H2</Value>
            </Eq>
        </Or>
        <Neq>
            <FieldRef Name ="ContentType"/><Value Type="Text">Document</Value>
        </Neq>
    </And>
</Where>

This will return the results when:

Header1Ref is equal to "H1" or "H2" AND ContentType is not be equal to "Document".

official documentation: Query Schema.

Update:

If you are using OOTB ContentType field then you need to query on it like below:

1. Using Content Type Name:

<Neq>
    <FieldRef Name="ContentType"/><Value Type="Computed">Document</Value>
</Neq>

2. Using Content Type ID:

<BeginsWith>
   <FieldRef Name="ContentTypeId"/><Value Type="ContentTypeId">YourContentTypeIdHere</Value>
</BeginsWith>

Answer 2

2. Try:

<Where>
    <And>
        <And>
            <Or>
                <Eq>
                    <FieldRef Name="Header1Ref"/><Value Type="Text">H1</Value>
                </Eq>
                <Eq>
                    <FieldRef Name="Header1Ref"/><Value Type="Text">H2</Value>
                </Eq>
            </Or>
            <Neq>
                <FieldRef Name ="ContentType"/><Value Type="Text">Document</Value>
            </Neq>
        </And>
    </And>
</Where>

3. Try:

<Where>
    <And>
        <And>
            <Or>
                <Eq>
                    <FieldRef Name="Header1Ref"/><Value Type="Text">H1</Value>
                </Eq>
                <Eq>
                    <FieldRef Name="Header1Ref"/><Value Type="Text">H2</Value>
                </Eq>
            </Or>
            <Neq>
                <FieldRef Name ="ContentType"/><Value Type="Text">Document</Value>
            </Neq>
        </And> 
        <Neq>
            <FieldRef Name ="ContentType"/><Value Type="Text">Document</Value>
        </Neq>
    </And>
</Where>

Answer 3

In both number 2 and 3 you have And operators that only contain one test. It can be helpful to replace your tests with boolean values to better see how the queries will behave. Example for number 2:

Where
  And
    And         
      (True or True)
      False

The second And says that Header1Ref should be equal to either "H1" or "H2", and ContentType should not be equal to "Document".

The first And compares the result of the second And and nothing else. This is why it fails.

In number 3 you are also missing comparison operators inside your Or statement.

I think the following query will achieve what you are trying to do:

<Where>
    <And>
        <Or>
            <Eq>
                <FieldRef Name="Header1Ref"/><Value Type="Text">H1</Value>
            </Eq>
            <Eq>
                <FieldRef Name="Header1Ref"/><Value Type="Text">H2</Value>
            </Eq>
        </Or>
        <Neq>
            <FieldRef Name ="ContentType"/><Value Type="Text">Document</Value>
        </Neq>
    </And>
</Where>