Sign in to follow this  
Followers 0
Aphotic

XML Sharepoint CAML Query - Not filtering based on query

6 posts in this topic

#1 ·  Posted (edited)

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.

#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 by Aphotic

Share this post


Link to post
Share on other sites



#2 ·  Posted (edited)

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 by Aphotic

Share this post


Link to post
Share on other sites

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?


Get Scite to add a popup when you use a 3rd party UDF -> http://www.autoitscript.com/autoit3/scite/docs/SciTE4AutoIt3/user-calltip-manager.html

Share this post


Link to post
Share on other sites

Maybe remove this line? 

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

IEbyXPATH-Grab IE DOM objects by XPATH IEscriptRecord-Makings of an IE script recorder ExcelFromXML-Create Excel docs without excel installed GetAllWindowControls-Output all control data on a given window.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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!
 

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

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0