I am wondering is it possible to convert a SharePoint list into a SQL query to be used on a different platform. Or even a way to query the data that exists in the list?
-
No, this is not advisable. Use one of the supported API methods to get the data you need, like REST or CSOM. There are plenty of examples here detailing that.– Eric AlexanderAug 9, 2016 at 14:25
-
There are list webservices which you can use ..– Amal HashimAug 9, 2016 at 14:25
-
2This should still be relevant sharepoint.stackexchange.com/questions/27626/…– Ransher SinghAug 9, 2016 at 14:26
-
I have added my suggestions to the linked question suggested by Ransher Singh. Short answer is yes it can be done. Easily if using a 3rd party tool. Completely not supported or advised and best to find a different way to get at the SharePoint data using SSIS with SharePoint SSIS Adapters or the REST interfaces. Good luck !– SatyricalAug 9, 2016 at 14:57
Add a comment
|
2 Answers
If you need to use the data contained in the SharePoint list as a source for some other subsystem, I would first of all question if the data should be kept inside SharePoint in the first place.
Some time ago I was discussing some "transaction/operation atomicity" issues regarding a business process implemented on SharePoint, and one guy told me that "SharePoint isn't a database". While he probably didn't understand the problem at all (we were discussing about a way to ensure a batch of operations on a list didn't error half way in the process) he still had a point: SharePoint data infrastructure isn't really built up to support elaborate processing of business critical information.
Returning to your question, first of all I would advise against any attempt to directly query the SharePoint content databases. Not only this is unsupported, but you will drag yourself in an undocumented mess where anything you find out can be changed without warning by any future patch/upgrade.
That leave you with just two options: use SharePoint high-level api to query the data from an outside source or move the data outside SharePoint in the first place.
If the data has to stay inside SharePoint, then you best options are either using the CSOM or the REST web services. Notice that if you need to access the data from a "not .Net" source like Java, Python or such - which may be the case if you are trying to interface with some third parties accounting programs - probably the REST api is the best option if not the only one. Anyway, remember that if all else fails, building a custom SharePoint hosted REST service that provides the data you need in the format you need is a viable solution too.
On the other hand, if the data can be moved outside SharePoint, then probably the real problem will be shifted on how to make the data "available" inside SharePoint too. Here all depends on the level of interaction you need to give to the users. Most of the times probably you don't want to attempt the external data source option unless the data model is very simple and you just want to offer basic options (which probably is the case if you were using simple lists to store the data before). Otherwise, custom interfaces may be a better choice - a single page Angularjs app, made available either via a web part or an actual SharePoint App has provided to be a good solution for me most times recently.
As a last notice, about how to decide if the data can be moved outside of SharePoint. Sadly, this isn't a question I can answer: only you can fully know your environment and make the correct choice. As a suggestion, I would ask myself what actual benefits does keeping the data in SharePoint give out: are this info used as metadata to empower search? Do some workflow rely on them? If this is the case, probably you should go for the REST services options. But if you discover that the data is there just because it was simpler to create a SharePoint list than a database table and someone though that you would also get a CRUD interface for free... perhaps the moment has come to question this design choice and ask if the data weren't mean to go in a database in the first place. Remember: you will lose the free CRUD but you get referential integrity, transactions and so on... and probably will be able to have all the data in one single place...
Only sort of. You can import it into SQL and query it there. There are multiple ways to do it. Personally, I used the SSIS components for exporting data, but was never able to get it to work for importing.
An uglier, but more reliable way to move data between SP lists and SQL Server is to use Excel as an intermediary. Simply make a data-grid view of the data, copy what you need to Excel, clean up the data as necessary, and then import into SQL.
The obvious caviat here is that it only works on snapshots (may or may not meet your requirements), or you would have to set up Sharepoint to use the Sql server table as an external data source (quite painful, IME).
Otherwise, using the web services or CSOM is the way to do it.
