Javascript Querying SharePoint

May 28th, 2007 • Related • Filed Under

The code in this post has now been replaced by a cross browser javascript API which includes proxies and utility methods for almost all of the SharePoint and Office Live web services. For more information read this article and also take a look at the examples, which demonstrate a number of common tasks using the SharePoint and Office Live web services.

When working with Office Live applications I frequently have the need to execute queries against lists stored in my applications. There are a number of ways to query lists in WSS 3.0, however the method I find most useful for Office Live applications is to utilise the List Data Retrieval web service of the WSS 3.0 SDK.

Office Live is secured using Windows Live ID. Client side code contained (or included) in pages on an Office Live site executes under the context of the current user. So as long as the user viewing a page has sufficient access rights it should be possible to use javascript to call _vti_bin/DspSts.aspx and query a list. This is ideal for a number of common tasks in Office Live development so it makes sense to have a utility function to hand which makes it easy- QueryListEx does just that.

function QueryListEx(listGuid, fields, where, orderBy, rowLimit, extractRows)
{
	var a = new ActiveXObject("Microsoft.XMLHTTP");

	if(a == null) return null;

	a.Open("POST", GetRootUrl() + "_vti_bin/DspSts.asmx", false);
	a.setRequestHeader("Content-Type", "text/xml; charset=utf-8");
	a.setRequestHeader("SOAPAction", "http://schemas.microsoft.com/sharepoint/dsp/queryRequest");

	var d = '<?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:Header xmlns:soap=\"http://schemas.xmlsoap.org/soap/envelope\/\">"
	+" <dsp:versions xmlns:dsp=\"http://schemas.microsoft.com/sharepoint/dsp\">"
	+" <dsp:version>1.0</dsp:version>"
	+" </dsp:versions>"
	+" <dsp:request xmlns:dsp=\"http://schemas.microsoft.com/sharepoint/dsp\""
	+" service=\"DspSts\" document=\"content\" method=\"query\">"
	+" </dsp:request>"
	+" </soap:Header>"
	+ "<soap:Body>"
	+ "<queryRequest "
	+" xmlns=\"http://schemas.microsoft.com/sharepoint/dsp\">"
	+" <dsQuery select=\"/list[@id='" + listGuid + "']\""
	+" resultContent=\"dataOnly\""
	+" columnMapping=\"attribute\" resultRoot=\"Rows\" resultRow=\"Row\">"
	+" <query RowLimit=\"" + rowLimit + "\">"
	+" <fields>" + fields + "</fields>"
	+" <where>" + where + "</where>"
	+" <orderBy>" + orderBy + "</orderBy>"
	+" </query>"
	+" </dsQuery>"
	+" </queryRequest>"
	+ "</soap:Body>"
	+ "</soap:Envelope>";

	a.Send(d);

	if (a.status != 200)
		return null;
	else
	{
		if (extractRows)
		return a.responseXML.selectNodes('//Row');
		else
		return a.responseXML;
	}
}

QueryListEx relies on a simple method GetRootUrl which decodes the request URL to determine the correct root path to the current web site.

function GetRootUrl()
{
	var pathparts = document.location.pathname.split('/');
	var url = 'https://' + document.location.hostname + '/' + pathparts[1] + '/';

	return url;
}

Calling QueryListEx to obtain results is a pretty easy process. A number of parameters are required:

listGuid	The internal guid of the list to be queried (see below).
fields	An XML string containing a list of fields to be returned by the query. The resultant fields will be represented in the output XML. <field Name=’ID’></field><field Name=’Forenames’></field>
where	A query expression used to filter the rows from the list and create the result set. The following expression returns all records in the list where the field with the internal name of ID is equal to 1. <Eq><FieldRef Name=’ID’ /><Value Type=’Counter’>1</Value></Eq>
orderBy	An XML fragement representing one or more fields to order the results by. Pass in an empty string to use the default sort order. The following example sorts by the Surname and Forename fields. <OrderField Name=’Surname’ Type=’xsd:string’ Direction=’ASC’/><OrderField Name=’Forename’ Type=’xsd:string’ Direction=’ASC’/>
rowLimit	Set to a positive integer to limit the size of the result set. It is always wise to restrict row sets to prevent potential performance problems or timeouts.
extractRows	Set this to true to automatically extract the results into an array of XML nodes. If set to false the entire XML DOM of the result will be returned.

The easiest way to use QueryListEx is to set extractRows to true. This will return an array of XML nodes containing the results of the query. The list columns requested in the fields parameter will be accessible through node attributes (using the getAttribute DOM method). The attributes are named after the internal name of the column.

Sometimes I find it a pain to manage the guids of the lists I want to query. For this reason I sometimes use a utility function (GetListGuid) to convert the name of a list to the corresponding guid. Remember though that this will have a performance penalty and may become ineffective if the list name changes.

function GetList(listName)
{
	var a = new ActiveXObject("Microsoft.XMLHTTP");

	if(a == null) return null;

	a.Open("POST", GetRootUrl() + "_vti_bin/Lists.asmx", false);
	a.setRequestHeader("Content-Type", "text/xml; charset=utf-8");
	a.setRequestHeader("SOAPAction", "http://schemas.microsoft.com/sharepoint/soap/GetList");
	var d = "<?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>"
	+ "<getList xmlns=\"http://schemas.microsoft.com/sharepoint/soap/\">"
	+ "<listName>" + listName + "</listName>"
	+ "</getList>"
	+ "</soap:Body>"
	+ "</soap:Envelope>";

	a.Send(d);

	if (a.status != 200)
		return null;
	else
		return a.responseXML;

}

function GetListGuid(fullName)
{
	var res = GetList(fullName);
	if (res != null)
	return res.selectSingleNode("//List").getAttribute("ID");
	else
	return null;
}

There Are 41 Responses So Far. »

Comment by anuj handa on 19 July 2007:

I am trying to update a lookup column in the sharepoint list using the Web service.

var xmldoc1;
var batch;//The batch variable builds up the XML that we are going to submit to the SharePoint list via the SharePoint web service
batch = “” + issueID + “”;
xmldoc1=”
xmldoc1=xmldoc1+”+ ListName +”;
xmldoc1=xmldoc1+” + batch +”;

FeedbackHTTP1=new ActiveXObject(”MSXML2.XMLHTTP.3.0″);

FeedbackHTTP1.Open(”POST”, WSSSiteURL + “/_vti_bin/Lists.asmx?op=UpdateListItems”, true);
FeedbackHTTP1.setRequestHeader(”Content-Type”,”text/xml; charset=utf-8″);
FeedbackHTTP1.setRequestHeader(”SOAPAction”,”http://schemas.microsoft.com/sharepoint/soap/UpdateListItems”);

FeedbackHTTP1.onreadystatechange=LookUpReady;
FeedbackHTTP1.Send(xmldoc1);
return false;
}

function LookUpReady()
{
if (FeedbackHTTP1.readyState==4){
var xmlResponse=new ActiveXObject(”MSXML2.DOMDocument.3.0″);
xmlResponse.async=false;
xmlResponse.loadXML(FeedbackHTTP1.responseXML.xml);
}
}

But this is not throwing error and is neither updating the column ……Any Help?
Comment by Vivek on 24 June 2008:

i wanna access my share point list! i have used your getlist method!
its giving “Undefined” as a result for this the status values is 500;
i could not justify where the problem exactly lies!
so could you please share your code?
Comment by darren on 26 June 2008:

Vivek, You have all of the code already- there is only the code sample given. The status code 500 is an internal server error. This likely means that the CAML query is poorly formed. Check the fields, where, and orderBy parameters. Check that each of the fields you reference is in the list. You can also find further examples of this kind of stuff in the “Drawing charts in SharePoint and Office Live” post on this site.
Darren
Comment by Vivek on 27 June 2008:

Darren,
Could you please Explain me, how to consume the Response XML? let me include what i have done!
I have created a custom list; the only way i can access that list is i think. i have built that soap request string, and sent via an HTTPXML object. that status now what i got was 200; now i’m struggling for consuming the response. how can i consume the Response XML? when i try to put in a alert its showing [Object]. here i attach my sample code! please tell me if i have done anything wrong!
function GetListCollection()
{

var a = new ActiveXObject(”Microsoft.XMLHTTP”);
if(a == null) return null;
a.Open(”POST”, “http://ServerName/_vti_bin/Lists.asmx”, false);
a.setRequestHeader(”Content-Type”, “text/xml; charset=utf-8″);
a.setRequestHeader(”SOAPAction”, “http://schemas.microsoft.com/sharepoint/soap/GetListCollection”);
var d = ““;
alert(d);
a.Send(d);
alert(a.status);
if (a.status != 200)
{
alert(”no data”);
return null;

}
else
{
return a.responseXML;
}
}

its like “select * from list” i want all the items in the list. i can consume that code as XML String which is ready to parse.

-Vivek
Comment by darren on 28 June 2008:

Hi Vivek,

Once the XMLHttp response returns you have access to the responseText and responseXML properties. responseText is a string representing the XML returned whilst responseXML is an XML DOM object. The [object] you see is the XML DOM. You can navigate through this using the selectNodes and selectSingleNode methods which accept XPath statements. You’ll get back a single object representing an XML element or a collection depeding on which you use. Hope this helps.
Comment by Vivek on 30 June 2008:

Darren,
i solved my problem,
I have used GetListItems; and I didn’t mention any parameter, string schema or xml;

+”"
+”"
+”Test”
+”"
+”"

i have got the responseXML; then i’ve parsed it like this

for(i=0;i<xmlDoc.documentElement.childNodes[0].childNodes[0].childNodes[0].childNodes[0].childNodes[0].childNodes.length;i++)
{
document.write(xmlDoc.documentElement.childNodes[0].childNodes[0].childNodes[0].childNodes[0].childNodes[0].childNodes[i].attributes.getNamedItem(”ows_lat”).nodeValue);
document.write(”");
}

is it a correct way or do we have any good approach to parse the responseXML?
Comment by darren on 30 June 2008:

Vivek,

Glad you got the problem solved. The method you show for getting to the child nodes will work, but it’s probably not the best way to do it. You should be using the selectSingleNode method of the DOM and using an XPath statement to select the node you are interested in. This is fairly easy, you just need to understand the XML structure. You could also use xmlDoc.documentElement.selectSingleNode(”//ows_lat”) if you are sure that there is only one element called this in the document. Good luck.

Cheers,
Darren
Comment by Vivek on 1 July 2008:

Thanks Darren,
the response structure of soap object is difficult to get, i struggle a lot to get the fourth sub child node.
let me see your suggestion of going with xpath. i didn’t work on that; if you have any sample, could you share it?
Comment by Vivek on 1 July 2008:

Thanks Darren,
the response structure of soap object is difficult to get, i struggle a lot to get the fourth sub child node.
let me see your suggestion of going with xpath. i didn’t work on that; if you have any sample, could you share it?
xmlDoc.documentElement.selectSingleNode(”//ows_lat”) no! i have so many elements like this! each and every element i have to parse through!
the idea behind this concept is, i’m gonna integrate maps in sharepoint, the data for latitude and longitude has to come from sharepoint list. based on that we have some calculations on the metrics. and the project goes on. the major role would be given for javascript, since we are dealing with client based subscription for maps. i’ll come with some more mind breaking doubts, please be there for me!
Comment by darren on 1 July 2008:

Vivek,

If you have more than one ows_lat node then you can use documentElement.selectNodes(”//ows_lat”) this will return you an array of XML node objects. You can then use getAttribute to examine the attribute values.

Darren
Comment by ppsp on 8 July 2008:

Hi Darren,

I wanna upload and download doc from sharepoint site through my website. For eg. User can attach the doc at my site and i want these doc to be saved in sharepoint location. Also they should be able to retieve it if they want it throught the link.
Can you please suggest how can we achieve it through java script?
I am completely new to sharepoint any help will do.
Thanks!
Comment by James Callaghan on 8 July 2008:

Darren,

Nice post, just what I needed. Is it posbile to use this code and perform a top query on a SharePoint list. I’m trying to get the top 3 items in a list based on a number column, or is there another way this can be done via JavaScript?

Thanks for your help.
James Callaghan

Cambridge, UK
Comment by Vivek on 9 July 2008:

Darren,
I tried fetching values in an array, (just assigning to array);
and accessing ar[0]; its returning null?
is that indented way of assigning the list of xml nodes or do we need to specifically declare and consume the nodes?
Comment by darren on 9 July 2008:

Hi Vivek,

I’m not sure exactly what you mean. Send me a code sample and I’ll take a look at it for you.

Darren
Comment by darren on 9 July 2008:

Hi James,

You can use the method to get a list of top items. What you need to do is set the order clause to descending by your rank (i.e. whatever list field you want to define the “top” items). Then you need to set the maximum count of records that will be returned.

So, in the function set orderBy to something like this:

<OrderField Name="Score" Type="xsd:int" Direction="DESC"/>

and then set the rowCount parameter to the number of top items you want. This would get you the top items ordered by rank.

Hope this helps,

Darren
Comment by darren on 9 July 2008:

Hi ppsp,

Uploading and downloading files to/from SharePoint can be accomplished using WebDAV. SharePoint is a WebDAV server which is what allows you to map Windows Explorer drives to it and things like that. You need to use the WebDAV PUT and GET methods.

A free javascript WebDAV library is available here:

http://debris.demon.nl/projects/davclient.js/doc/README.html

The big problem you’ll have though is how to get the file data in the required byte array in javascript. You might also want to consider making a custom IHTTPHandler implementation and place it in your _layouts folder. In this way you could send the file to the service via a normal HTTP post and use file inputs to get the upload file.

Hope this helps,
Darren
Comment by Powell on 10 July 2008:

Great post.
Question, I am not getting very far, I always get a 500 error, even if I limit my script to the following:
var a = new ActiveXObject("Microsoft.XMLHTTP"); a.Open("POST", "http://hostname/sites/mysite/_vti_bin/DspSts.asmx", false); a.setRequestHeader("Content-Type", "text/xml; charset=utf-8"); a.setRequestHeader("SOAPAction", "http://schemas.microsoft.com/sharepoint/soap/GetList");
.......
a.Send(d); alert(a.status);
I must be missing something fundamental, is there any server side, or IE security settings that need to be configured to allow this?

Thanks,
Powell
Comment by Vivek on 11 July 2008:

Hi Darren,
here i attach the sample code to consume in array!
var ra=new Array(5); ra=xmlDoc.documentElement.selectSingleNode(”//ows_lat”); alert(ra[0]); //output:null alert(xmlDoc.documentElement.selectSingleNode(”//ows_lat”))// output: [Object]
how can i parse the resultant object?
Comment by Vivek on 11 July 2008:

One More Question Darren,
how can we access BDC List Data in javascript?
Comment by darren on 11 July 2008:

Hi Powell,

The 500 (internal server) error is most likely caused by an error in the XML being sent to the server. This will be the fields, where, or orderBy parameters. Your xml was stripped out of the comment you posted. Please can you send the query you are using and I will check it for you. You can send it to me at darren@darrenjohnstone.net.

One other thing to check is that all of the fields are correctly installed and that you are using the correct internal names. Note that if they are long they might be truncated and spaces will be replaced with _x0020_.

Cheers,
Darren
Comment by darren on 11 July 2008:

Hi Vivek,

selectSingleNode doesn’t return an array. It is designed to pull out a single XML element. To get an array of nodes you would use selectNodes.

Assuming it’s a single element you’re after selectSingleNode will return an object giving you access to the value and attributes of the element (as well as it’s children and parent).

To parse the attibutes you use the getAttribute method. This takes a single parameter which is the name of the attribute you want.

It looks to me though that you’re looking to get an array of the objects. To do this you should change your code as follows to use the selectNodes method:

var ra; ra=xmlDoc.documentElement.selectNodes("//ows_lat"); alert(ra[0]); // output will be an XML element object
// Return the string value of the attribute called name alert(ra[0].getAttribute["name"]);
// Return the value of the element alert(ra[0].getAttribute["name"].value);

Hope this helps.

Cheers,
Darren
Comment by PPSP on 11 July 2008:

Hi Darren,

Thanks for your help!

What I am trying to achieve is to use sharepoint as depositroy for the documents uploaded by user on my website.

My site is designed in Java/JSP and I am looking for the way to setup a link between sharepoint and my website so that when user click on the attachment it should be saved on the sharepoint site. Something like that.
Could you please help me with some sample example how to achieve this?
Thank you,
ppsp
Comment by darren on 12 July 2008:

PPSP,

Ok, that’s slightly different. You probably don’t want to use javascript for that. What I’d suggest is that you upload the file to your JSP site using a normal file upload and then use the WebDAV PUT method to upload the document from there to SharePoint. To download the file you’d use the GET method. I don’t have any examples of this to hand but Rohit has one over at http://blogs.msdn.com/rohitpuri/archive/2007/04/10/upload-download-file-to-from-wss-document-library-using-dav.aspx.

Hope this helps.

Cheers,
Darren
Comment by darren on 12 July 2008:

Vivek,

To access DBC data from script I’d probably create a web service in the _layouts folder using a custom feature. Then the ApplicationRegistry classes could be used to pull the data out of the BDC and return it as XML which could be consumed in a similar way to the lists.asmx sample given here. I don’t have any specific examples but start here anyway http://msdn.microsoft.com/en-us/library/microsoft.office.server.applicationregistry.metadatamodel.applicationregistry.aspx.

Darren
Comment by Anil on 13 July 2008:

Hi Darren,

Thanks for the article…I tried using the Getlist method. I get the status 200. But when i apply SelectSingleNode(”//List”) function on the ResponseXML object i get null and when i try to alert ResponseText i get the xml displaying all the web methods present in lists.asmx rather than displaying the actual contents of the list. Here’s the code i used:

function GetListGuid(fullName)
{
var res = GetList(fullName);
if (res != null)
return res.selectSingleNode(”//List”).getAttribute(”ID”);
else
return null;
}

i get “res.selectSingleNode is Null” exception. If you observe i used GET instead of POST. When i use POST i get statuscode as 500. So i used GET where i get 200. Am i missing anything here. Please suggest.

Thanks in advance
Anil
Comment by darren on 13 July 2008:

Hi Anil,

This sounds like you’re either getting the WSDL file returned or you are for some reason being redirected to the web service test harness. Check you’re using the correct URL.

If you send me the javascript by email along with the XML that was returned I’ll see if I can help you.

UPDATE: I tested this in my environment. First I missed that you said you were using GET. You can’t do this as it will result in the web service discovery page being returned- hence the 200 status code. The 500 code is an internal service error. There’s something wrong with the XML you’re sending most likely.

Darren
Comment by Vivek on 14 July 2008:

There is one option for consuming BDC data as a column in a list. making Business Data as a column type. if the data is in the list, we can easily get it like how we have done. but the assigned columns are not appearing in the list view. its because of Title* Primary key constraint in the list. how can we avoid this constraint, or creating default values for this Title column?
Comment by Vivek on 14 July 2008:

Darren,
Thanks! that approach is working fine! (SelectNodes)

Cheers
-Vivek
Comment by darren on 14 July 2008:

Hi Vivek,

Glad you got it working.

If I get a chance I’ll try out the web service with a BDC column. If I get it I’ll email you.

Cheers

Darren
Comment by vivek on 12 August 2008:

hi Darren,
i got an issue with our approach!
DSQuery method is working fine up to some extent. if we are giving big conditions with 30 OR’s & 2 AND’s its giving null as the response!
the same query is working for 10 to 15 WHERE conditions. why we have this limitations? since we are using POST method, it should allow us to send huge size of request right?

let me explain my clear scenario!

i have 3 lists. i should take plant codes from one list, based on the plant codes, i should select list of cost centers from second list, based on that cost centers, i should select different accounts and their actual values from the third list.
with the selected list items, i’m building or conditions dynamically. this or conditions works only for limited conditions. if the number of conditions exceeds, it fails, our QueryListEx method returns NULL
since it fails, i’m in position to look after some other approach for fetching this huge data! any idea about my issue?

-Vivek
Comment by darren on 12 August 2008:

Hi Vivek,

What is the HTTP status code that you get?

It seems like you are building up pretty big queries on the client. I’m not sure this is the best approach for you. It’ll be slow and very error prone.

I think you should create custom web services and call those instead. This will make your life a lot easier and still allow you to have an AJAX style interface which pulls data from lists.

Cheers,
Darren
Comment by vivek on 14 August 2008:

i didnt see that status code, its a big application. yup i’m building a huge CAML query; my situation is like that!
i would have created a webservice, but our final deployment would be in sharepoint. hence no database, no web services allowed, all that we can use that is, we can use lists as our data sources and javascript to bring the business logic!

disgusting …… what can i do?
Comment by Vivek on 19 August 2008:

hi Daren,
how can we delete the entire list content using CAML query?
deletelist method will delete the list from the site i think,
i wanna delete only the list contents and the structure of list remains same! how can i do that?
again the constraint is javascript! i cant write any server side scripting for this deletion!
Comment by darren on 19 August 2008:

Hi Vivek,

You’ll need to query the list to get all of the ID numbers and then delete them one at a time or using a batch. You can use the quickDeleteListItem method from my other javascript library. There is a demo here http://darrenjohnstone.net/2008/07/22/examples-for-the-sharepoint-and-office-live-javascript-api/#topic-11.

Cheers,
Darren
Comment by Vivek on 20 August 2008:

Hi Daren!
I’ll look into that!
i need some higher priority issue; i need to get user id (domain\userid) of the logged user!
how can i get that in javascript?
Comment by darren on 21 August 2008:

Hi Vivek,

I’ve posted an article about your login name question: http://darrenjohnstone.net/2008/08/21/wss-30-getting-the-current-user-login-name-via-javascript/

Cheers,
Darren
Comment by Jose Morris on 24 August 2008:

Hi,

Had to make some changes on code using GetList function:

1. Change “https” to “http” on function GetRootURL if no https is configured on IIS (as my development env)
2. Change XML tag “getlist” to “GetList”. List webservice seems to be case sensitive.

Happy coding,
J. Morris
Comment by darren on 25 August 2008:

Hi Jose,

Thanks for the info. For you info there are better (cross browser) functions than this here: http://darrenjohnstone.net/2008/07/22/a-cross-browser-javascript-api-for-the-sharepoint-and-office-live-web-services/.

Examples here: http://darrenjohnstone.net/2008/07/22/examples-for-the-sharepoint-and-office-live-javascript-api/.

Cheers,
Darren
Comment by sharavathi on 22 September 2008:

Hi,

i have a list called TestSite and one of column called “Title” has few items as element1, element2….element12.
I want to retrieve all the items(element1…element12) and store it in array.I want to do it using javascript code.
I have some code will will access all the items of default page and if i go to next page its not accessing the items.

Please suggest me.
Comment by darren on 23 September 2008:

Hi Sharavathi,

I’m not really sure what you mean. There are more examples of querying lists with js here: http://darrenjohnstone.net/2008/07/22/examples-for-the-sharepoint-and-office-live-javascript-api/.

Other than that can you provide a code sample of more information if you need further help.

Cheers,
Darren
Comment by vivek on 14 November 2008:

hi Darren,
i’ve an issue with AddAttachment webservice
Task: Upload a local file to “Shared Document” list of my sharepoint site.
i’ve used Lists.asmx web service, and try uploading the file by using AddAttachment method.
it has given the following error
<!–
soap:ServerException of type ‘Microsoft.SharePoint.SoapServer.SoapServerException’ was thrown.Value does not fall within the expected range.
–>

i’m converting a string into base64 format and sending as an attachment. i suspect the compatibility? what could be the issue, can you help me out in this regard?

-Vivek

Darren’s SharePoint and .Net blog

Javascript Querying SharePoint

There Are 41 Responses So Far. »

Post a Response

Popular

Most Comments

Search

Tags

Archives

Recent Comments