Example |
Query |
Select lists from Lists table |
SELECT * FROM Lists
|
Select data from a named list |
SELECT * FROM [Type here your list name] --Where Id=1234
|
Insert data into a named list |
INSERT INTO [Type here your list name](Name, Surname)
VALUES ('John', 'Doe') WITH (Output=1)
|
Update data in a named list |
UPDATE [Type here your list name]
SET Name = 'Elizabeth', Surname = 'Johnson'
WITH (ListItemId='887',Output=1)
--ListItemId can be retrieved by selecting from named list table, e.g. 'MyEmployeesList'.
|
Delete item from a named list |
DELETE FROM [Type here your list name] WHERE Id=888
--OR--
DELETE FROM [Type here your list name] WITH (ListItemId='888')
--ListItemId can be retrieved by selecting from named list table, e.g. 'MyEmployeesList'.
|
Main site |
SELECT * FROM get_main_site
|
List sites |
SELECT * FROM Sites
|
List drives |
SELECT * FROM Drives
|
Get drive |
SELECT * FROM get_drive
WITH (DriveId='b!GtLQTMU726LE0eY5F2BBNi14-XXXXXXXXXXX-GuQ4DORpHy-XXXXXXXXXXXXXX')
--You can get DriveId by selecting from 'Drives' table.
|
List folders |
SELECT * FROM list_folders
WITH (DriveId='b!GtLN726LE0eY5F2BBNi14wMKmwdpCDFMn1d71ra11GuQ4DORpHy-XXXXXXXXXXXXX')
--You can get DriveId by selecting from 'Drives' table.
|
List files |
SELECT * FROM list_files
WITH (DriveId='b!GtLN726LE0eY5F2BBNi14wMKmwdpCDFMn1d71ra11GuQ4DORpHy-XXXXXXXXXXXXX')
--You can get DriveId by selecting from 'Drives' table.
|
List Excel files |
SELECT * FROM list_excel_files
WITH (DriveId='b!GtLN726LE0eY5F2BBNi14wMKmwdpCDFMn1d71ra11GuQ4DORpHy-XXXXXXXXXXXX')
--You can get DriveId by selecting from 'Drives' table.
|
Get list fields |
SELECT * FROM get_list_fields
WITH (ListId='2e1d58e4-eced-4d1c-9279-XXXXXXXXXXXXX')
--You can get ListId by selecting from 'list_lists' endpoint.
|
Get list items |
SELECT * FROM get_list_items
WITH (ListId='14bdfd1d-1090-4cfe-adc1-XXXXXXXXXXXXXX')
--You can get ListId by selecting from 'list_lists' endpoint.
|
Get list items (dynamic metadata) |
SELECT * FROM get_list_items_dynamic
WITH (ListId='14bdfd1d-1090-4cfe-adc1-XXXXXXXXXXXXXX')
--You can get ListId by selecting from 'list_lists' endpoint.
|
Create list item |
INSERT INTO create_list_item(Title, OrderId)
VALUES ('My super title', 12345)
WITH (ListId='14bdfd1d-1090-4cfe-adc1-XXXXXXXXXXXXXX'
,Output=1)
--You can get ListId by selecting from 'list_lists' endpoint.
|
Update list item |
UPDATE update_list_item
SET Title = 'My super title', OrderId = 12345
WITH (ListId='14bdfd1d-1090-4cfe-adc1-XXXXXXXXXXXXX'
,ListItemId='775'
,Output=1)
--You can get ListId by selecting from 'list_lists' endpoint.
--ListItemId can be retrieved by selecting from 'get_list_items' endpoint.
|
Delete list item |
DELETE FROM delete_list_item
WITH (ListId='14bdfd1d-1090-4cfe-adc1-XXXXXXXXXXXXXX'
,ListItemId='775')
--You can get ListId by selecting from 'list_lists' endpoint.
--ListItemId can be retrieved by selecting from 'get_list_items' endpoint.
|
List Excel file worksheets |
SELECT * FROM list_excel_worksheets
WITH (DriveId='b!GtLN726LE0eY5F2BBNi14wMKmwdpCDFMn1d71ra11GuQ4DORpHy-XXXXXXXXXXXXXX'
,FileId='01SUOJPKECYDDVFZWXXXXXXXXXXXXXXXXX')
--DriveId can be retrieved by selecting from 'Drives' table.
--FileId can be retrieved by selecting from 'list_files' endpoint.
|
Read Excel file |
SELECT * FROM get_excel_worksheet
WITH (DriveId='b!GtLN726LE0eY5F2BBNi14wMKmwdpCDFMn1d71ra11GuQ4DORpHy-XXXXXXXXXXX'
,FileId='01SUOJPKECYDDVFXXXXXXXXXXXXXXXXXX'
,SheetId='in'
,Range='A1:K10000')
--DriveId can be retrieved by selecting from 'Drives' table.
--FileId can be retrieved by selecting from 'list_files' endpoint.
--SheetId can be retrieved by downloading Excel file and discovering what sheets exist or by selecting from 'list_excel_worksheets' endpoint.
--Range should be set to Excel-type of range where data is located.
|
Upload a file to a SharePoint site |
SELECT * FROM upload_file
WITH (DriveId='b!GtLN726LE0eY5F2BBNi14wMKmwdpCDFMn1d71ra11GuQ4DORpHy-XXXXXXXXXXX'
,DiskFilePath='C:\My files\Employees\List.xls'
,SharePointFilePath='Uploads/Employees/List.xls')
--DriveId can be retrieved by selecting from 'Drives' table.
|
Download a file to a local disk from a SharePoint site |
SELECT * FROM download_file_to_disk
WITH(
DriveId='b!GtLN726LE0eY5F2BBNi14wMKmwdpCDFMxxxxxxxxxxxxx'
, FileId='01SUOJPKDXTQL2XI2EIFDYZxxxxxxxxxxxxx'
, TargetFilePath='C:\temp\List.xlsx'
--, FileOverwriteMode='FailIfExists' //change default overwrite mode from AlwaysOverwrite to FailIfExists
)
--DriveId can be retrieved by selecting from 'Drives' table.
--FileId can be retrieved by selecting from 'list_files' endpoint.
|
Read a file content as BASE64 (Binary to string) |
SELECT Data as BYTES_AS_BASE64_STRING, BASE64_TO_FILE('C:\My files\Employees\List.xls', Data) FILE_SAVED_TO_DISK_FROM_BASE64
FROM download_file
WITH (DriveId='b!GtLN726LE0eY5F2BBNi14wMKmwdpCDFMn1d71ra11GuQ4DORpHy-XXXXXXXXXXXXX'
,FileId='01SUOJPKHXMPKD2UXXXXXXXXXXXXXXXXXX')
--DriveId can be retrieved by selecting from 'Drives' table.
--FileId can be retrieved by selecting from 'list_files' endpoint.
|
Get full image path from Image type field |
SELECT *,(JSON_VALUE(MyImageField,'$.serverUrl') || JSON_VALUE(MyImageField,'$.serverRelativeUrl')) as DevicePhotoUrl,
FROM "My SharePoint List Name"
|