XML Sharepoint CAML Query - Not filtering based on query

[Aphotic]

Any assistance in this would be greatly appreciated.
When running this snippet of code, it returns the first 20 items in the list (this is not the issue), basically ignoring the query I built. (It should return 20 different items than it is, note the <Value Type="Text">Closure</Value> from the query)
Although, when running this query in a Query builder tool, it runs as it should and returns the results needed. This leads me to believe that the issue is not with the query but how I'm handling it with the XML request.
Thanks in advance.

expandcollapse popup

#include-once
Global $BasicAuthentication = _Base64Encode("")
    ;------------------------------------------------------------------------
    ;Example:
    ; dim $x
    ; $x=_sp_getxml("http://<yoursite>/<your subsite>","<yourlist","")
    ; _SP_DeleteListItems("http://<yoursite>/<your subsite>","<yourlist>",$x)
    ;------------------------------------------------------------------------

Dim $x
$x=_sp_getxml("*SITE_URL_REMOVED*","*LIST_NAME_REMOVED*",'<Query><OrderBy><FieldRef Name="ows_Modified" Ascending="TRUE"></FieldRef></OrderBy><Where><And><BeginsWith><FieldRef Name="ows_Status"></FieldRef><Value Type="Text">Closure</Value></BeginsWith><IsNotNull><FieldRef Name="Title"></FieldRef></IsNotNull></And></Where></Query>')
ConsoleWrite($x)

Func _SP_GetXML($url,$list,$CAML)
    ;------------------------------------------------------------------------
    ;Make sure your default view is "AllItems"
    ; $url= URL to your site/subsite
    ; $list= Name of the Sharepointlist to query
    ; $CAMLquery= CAML based query string. Leave empty for the whole list.
    ;Return: XML string from Sharepoint list containing the filtered items
    ;------------------------------------------------------------------------
    Dim $request
    $url = $url & "/_vti_bin/Lists.asmx"
    $request =  "<?xml version='1.0' encoding='utf-8'?>"
    $request =  $request & "<soap:Envelope xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance' xmlns:xsd='http://www.w3.org/2001/XMLSchema' xmlns:soap='http://schemas.xmlsoap.org/soap/envelope/'>"
    $request =  $request & "<soap:Body>"
    $request =  $request & "<GetListItems xmlns='http://schemas.microsoft.com/sharepoint/soap/'>"
    $request =  $request & "              <listName>" & $list & "</listName>"
    $request =  $request & "              <ViewName></ViewName>"
    $request =  $request & '              ' & $CAML
    $request =  $request & "              <ViewFields></ViewFields>"
    $request =  $request & "              <rowLimit>20</rowLimit>"
    ;$request =  $request & "             <queryOptions></queryOptions>"
    $request =  $request & "              <webID></webID>"
    $request =  $request & "</GetListItems>"
    $request =  $request & "</soap:Body>"
    $request =  $request & "</soap:Envelope>"
    ;post it up and look at the response
    $XmlHttp=ObjCreate("Microsoft.XMLHTTP")
    $XmlHttp.open ("Get", $url, False)
    ;Pass username:password as a base64 string ($BasicAuthentication)
    $XmlHttp.SetRequestHeader ("Authorization", "Basic " & $BasicAuthentication)
    $XmlHttp.setRequestHeader("Content-Type", "text/xml; charset=utf-8")
    $XmlHttp.SetRequestHeader ("Accept", "text/xml; charset=UTF-8")
    $XmlHttp.send ($request)
    return ($XmlHttp.responsetext)
EndFunc

Func _Base64Encode($Data, $LineBreak = 76)
    Local $Opcode = "0x5589E5FF7514535657E8410000004142434445464748494A4B4C4D4E4F505152535455565758595A6162636465666768696A6B6C6D6E6F707172737475767778797A303132333435363738392B2F005A8B5D088B7D108B4D0CE98F0000000FB633C1EE0201D68A06880731C083F901760C0FB6430125F0000000C1E8040FB63383E603C1E60409C601D68A0688470183F90176210FB6430225C0000000C1E8060FB6730183E60FC1E60209C601D68A06884702EB04C647023D83F90276100FB6730283E63F01D68A06884703EB04C647033D8D5B038D7F0483E903836DFC04750C8B45148945FC66B80D0A66AB85C90F8F69FFFFFFC607005F5E5BC9C21000"
    Local $CodeBuffer = DllStructCreate("byte[" & BinaryLen($Opcode) & "]")
    DllStructSetData($CodeBuffer, 1, $Opcode)
    $Data = Binary($Data)
    Local $Input = DllStructCreate("byte[" & BinaryLen($Data) & "]")
    DllStructSetData($Input, 1, $Data)
    $LineBreak = Floor($LineBreak / 4) * 4
    Local $OputputSize = Ceiling(BinaryLen($Data) * 4 / 3)
    $OputputSize = $OputputSize + Ceiling($OputputSize / $LineBreak) * 2 + 4
    Local $Ouput = DllStructCreate("char[" & $OputputSize & "]")
    DllCall("user32.dll", "none", "CallWindowProc", "ptr", DllStructGetPtr($CodeBuffer), _
                                                    "ptr", DllStructGetPtr($Input), _
                                                    "int", BinaryLen($Data), _
                                                    "ptr", DllStructGetPtr($Ouput), _
                                                    "uint", $LineBreak)
    Return DllStructGetData($Ouput, 1)
EndFunc

 

To further elaborate, please view these screenshots in order to understand.
 

This is the unsorted sharepoint list: '>

Sorted Showing 'Informational' Entries: '>

Sorted Showing 'Closure' Entries: '>

Query Builder Settings: '>

Query Builder Results: '>

SciTe Results: '>

 

So I used the same CAML query in SciTe as I did in the query builder.

Unfortunately, as you may notice, the results from the query builder match the Closure entries from the sharepoint, though SciTe returns from the first entry which are the Informational entries.

Edited August 5, 2014 by Aphotic

[Aphotic]

Another bump, again I apologize but would greatly appreciate the help. If anyone who sees this needs or could user more info, please ask.

Edited August 5, 2014 by Aphotic

[computergroove]

Can you provide a pseudo list that has more than 20 entries and modify the above snippet of code so it will run on its own so we can see the problem and provide a solution?

[jdelaney]

Maybe remove this line? 

$request =  $request & "              <rowLimit>20</rowLimit>"

[Aphotic]

Can you provide a pseudo list that has more than 20 entries and modify the above snippet of code so it will run on its own so we can see the problem and provide a solution?

I will look into re-hosting the lists onto another sharepoint server to allow public troubleshooting, unfortunately I wouldn't really know how else to test this, and the sharepoint server it's on is privately hosted and authenticated using AD credentials.

 

Maybe remove this line? 

$request =  $request & "              <rowLimit>20</rowLimit>"

The issue is not the limit to 20 lines, it's that the 20 lines being returned are not the correct 20.

 

 

I've added some screenshots to further elaborate and assist in understanding the issue I'm facing to the original post.

Edit: Bump

Edit: Bump 2

[Aphotic]

I resolved this myself, after a lucky search I found an article of a guy writing a CAML query using Visual BASIC. I ported it into AutoIT, found out I didn't even need the Authentication Function I had, and ultimately, the query I had built now works. It looks as if it relates to having two <query> brackets in the query. I'm not sure why but hey it works!
 

expandcollapse popup

#include-once
dim $request
dim $oHttp
dim $URL
Dim $x, $pos

$oHttp = ObjCreate("Microsoft.XmlHttp")

$URL     = "*URL REMOVED*"
$request = "<?xml version='1.0' encoding='utf-8'?>" _
        & "<soap:Envelope xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance' xmlns:xsd='http://www.w3.org/2001/XMLSchema' xmlns:soap='http://schemas.xmlsoap.org/soap/envelope/'>" _
        &    "<soap:Body>" _
        &       "<GetListItems xmlns='http://schemas.microsoft.com/sharepoint/soap/'>" _
        &          "<listName>Reported Incidents 2</listName>" _
        &          "<query>" _
        &             "<Query>" _
        &               "<OrderBy>" _
        &                   "<FieldRef Name='Modified' Ascending='FALSE'></FieldRef>" _
        &               "</OrderBy>" _
        &                "<Where>" _
        &                   "<Eq>" _
        &                      "<FieldRef Name='Status' />" _
        &                      "<Value Type='Text'>Closure</Value>" _
        &                    "</Eq>" _
        &                "</Where>" _
        &             "</Query>" _
        &          "</query>" _
        &          "<ViewFields>" _
        &             "<ViewFields xmlns='http://schemas.microsoft.com/sharepoint/soap/'>" _
        &                "<FieldRef Name='Status' />" _
        &                "<FieldRef Name='ServDesk' />" _
        &             "</ViewFields>" _
        &         "</ViewFields>" _
        &       "</GetListItems>" _
        &    "</soap:Body>" _
        & "</soap:Envelope>"

$oHttp.Open("POST", $URL, False)
$oHttp.setRequestHeader("Content-Type", "text/xml; charset=utf-8")
$oHttp.send($request)
$x = $oHttp.responseText
ConsoleWrite($x)

Thanks to the 2 that responded.