Querying sharepoint lists and libraries

Gene Klamerus
Gene Klamerus used Ask the Experts™
on
Is there not some web service or worst case library based API to query sharepoint lists and libraries similar to SQL?

I’ve only been able to find fairly obtuse ways of doing this and in reality the basis for these are sql server relational dB tables.

And I do mean querying to sharepoint not the tables behind it.  I have SP Online sites and I only have acces to SP not the DB behind it.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Walter CurtisSharePoint AED
Distinguished Expert 2018

Commented:
SPServices may be something you can use. It is very good...

http://sympmarc.github.io/SPServices/

Hope that helps...

btw... since providing just links is frowned upon, I will say that I have been aware of SPServices and using the library for over seven years with very good results. I don't see the need to fill space in this box with information that is not as good as what you will get from the web site that I referenced above. Good luck...
Cloud Expert
Most Valuable Expert 2013
Top Expert 2014
Commented:
First thing to say is that SharePoint is not a relational database even though it might be built on top of some. There's no access to those databases, and everything you get through the API is not database-like.

That said there are many ways to query List and Library data via web services, Client Object Model and more recently through REST. The former two methods support a query language called CAML which looks a lot like SQL serialized into XML. However there are limitations to how many items you can retrieve and it's not quite the same as SQL despite cosmetic similarities.

Those same APIs also allow you to query the Search service which is a very powerful way of surfacing content from individual lists or right across the sites.

Walters suggestion of SPServices is a wrapper over the web services which can be a nice way to do it.

This might seem like links overkill but covers the spectrum of how to query lists. The latter links are more modern. Web services are deprecated and REST (and something called Microsoft Graph not relevent here) is likely to be where most future investment occurs.

https://williamwmy.wordpress.com/2013/05/13/using-spservices-with-camlquery-to-get-listitems/

https://msdn.microsoft.com/en-us/library/lists.lists.getlistitems%28v=office.12%29.aspx

https://maxmorrow.wordpress.com/2012/06/17/jquery-caml-query-lists-asmx-web-service/

https://msdn.microsoft.com/en-us/library/office/ee534956%28v=office.14%29.aspx

https://chuvash.eu/2014/03/25/using-caml-with-sharepoint-rest-api/

https://docs.microsoft.com/en-us/sharepoint/dev/sp-add-ins/working-with-lists-and-list-items-with-rest
Gene KlamerusTechnical Architect

Author

Commented:
Would these approaches support the development of something I can upload into the site and expose to end-users as an alternative to the LTV interface?
PMI ACP® Project Management

Prepare for the PMI Agile Certified Practitioner (PMI-ACP)® exam, which formally recognizes your knowledge of agile principles and your skill with agile techniques.

Jamie McAllisterCloud Expert
Most Valuable Expert 2013
Top Expert 2014

Commented:
Yes. Particularly the JSOM examples I.e. Javascript calling the client object model. Lot's of cut and paste examples online and you can put these on a page and surface data based on that users permissions.  Works great for small scale solutions.
Gene KlamerusTechnical Architect

Author

Commented:
Frustrating to get answers when they're disappointing answers, but they are the real answers nonetheless.
Gene KlamerusTechnical Architect

Author

Commented:
hard to believe how MS can deliver solutions that capacity limitations lower than applications I wrote 30 years ago due to some internal design/architecture decisions.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial