Reference

Table [Dynamic Table]

Description

No description available

Supported Operations

Below section contains supported CRUD operations. Each operation is executed by some EndPoint behind the scene.
Method Supported Reference EndPoint
SELECT get_list_items
INSERT create_list_item
UPDATE update_list_item
UPSERT
DELETE delete_list_item
LOOKUP get_list_item

Examples

SSIS

Use SharePoint Online Connector in API Source component to read data or in API Destination component to read/write data:

Read from [Dynamic Table] table using API Source

API Source - SharePoint Online
Read and write SharePoint Online data effortlessly. Integrate, manage, and automate sites, lists, document libraries, and files — almost no coding required.
SharePoint Online
[Dynamic Table]
Required Parameters
ListId Fill-in the parameter...
Optional Parameters
SiteId (Re-Select ListId after you change this)
Filter
SSIS API Source - Read from table or endpoint

Read/write to [Dynamic Table] table using API Destination

API Destination - SharePoint Online
Read and write SharePoint Online data effortlessly. Integrate, manage, and automate sites, lists, document libraries, and files — almost no coding required.
SharePoint Online
[Dynamic Table]
Select
Required Parameters
ListId Fill-in the parameter...
Optional Parameters
SiteId (Re-Select ListId after you change this)
Filter
SSIS API Destination - Access table operation

ODBC application

Use these SQL queries in your ODBC application data source:

Get list items

<p>Gets items from a specific list. Replace <code>MyList</code> with the actual name of your SharePoint list.</p> 

SELECT * FROM MyList 
--WHERE Id = 1234

Get list items with filter

<p>Gets items from a list using a filter. This mimics a SharePoint view. Use <code>HonorNonIndexedQueriesWarningMayFailRandomly</code> in <code>ExtraHeaders</code> if filtering on non-indexed fields.</p> 

SELECT * FROM MyList 
WITH(Filter='MyField eq ''Something'' ')
--In order to search on non-indexed fields set following value for ExtraHeaders property on connection UI
--Prefer: HonorNonIndexedQueriesWarningMayFailRandomly

Create a list item

<p>Creates a new item in a list. Replace <code>MyList</code> with your list name and specify the columns to populate.</p> 

INSERT INTO MyList(Name, Surname)
VALUES ('John', 'Doe') WITH (Output=1)

Create a list item with multi-select lookup or person fields

<p>Creates a list item with multi-select Lookup or Person/Group fields. Requires passing values as a JSON array and specifying the OData type (e.g. <code>Collection(Edm.Int32)</code>).</p> 

--new version --				
INSERT INTO MyList(Title, ManyPersonsLookupId, ManyPersonsLookupIdOdataType)
VALUES ('John Doe', '[11,22,33]', 'Collection(Edm.Int32)')
WITH (Output=1)

--old version --				
INSERT INTO MyList(Title, ManyPersonsLookupId, "fields.ManyPersonsLookupId@odata\u002Etype")
VALUES ('John Doe', '[11,22,33]', 'Collection(Edm.Int32)')
WITH (Output=1)
--Considering "ManyPersons" is the name of the field in SharePoint List

Create a list item with multi-select choice fields

<p>Creates a list item with multi-select Choice fields. Requires passing values as a JSON array and specifying the OData type (e.g. <code>Collection(Edm.String)</code>).</p> 

--new version --				
INSERT INTO MyList(Title, MultiChoiceField, MultiChoiceFieldODataType)
VALUES ('John Doe', '["Choice1","Choice5"]', 'Collection(Edm.String)')
WITH (Output=1)

--old version --				

INSERT INTO MyList(Title, MultiChoiceField, "fields.MultiChoiceField@odata\u002Etype")
VALUES ('John Doe', '["Choice1","Choice5"]', 'Collection(Edm.String)')
WITH (Output=1)

--Considering "MultiChoiceField" is the name of the field in SharePoint List

Update a list item

<p>Updates an existing list item by ID.</p> 

UPDATE MyList
SET Name = 'Elizabeth', Surname = 'Johnson'
WHERE Id = '123'

Update a list item with multi-select fields

<p>Updates multi-select Lookup or Person/Group fields on an existing item.</p> 

UPDATE MyList
SET Title = 'Elizabeth Johnson',
	--Method#1 (Pass type info in the same value)
    ManyPersonsLookupId = '[11,22,33]', "fields.ManyPersonsLookupId@odata\u002Etype" = 'Collection(Edm.Int32)'
    --Method#2 (Pass type info via ODataField)
	--OR--
	--ManyPersonsLookupId = '[11,22,33]',
    --ManyPersonsLookupIdODataType" = 'Collection(Edm.Int32)
	
WHERE Id = '123'

Update / Insert Sharepoint List Data (Mixed Data Types - e.g. Multi Choice, Lookup, Person, DateTime, Number) 

UPDATE  "Travel Requests" --list name
SET   
	 Title='MyTitle @ <<FUN_NOW>>'  
	 --Method#1 Pass Value and Type in 2 fields
	, CustomText = 'This is text'
	, CustomTextLarge = 'This is really long text ....'	                     
	, CustomNumber = 10000.12345
	, CustomDateTime ='2012-12-31T23:59:59.123Z' --End with Z to treat as UTC time (avoid local-UTC conversion)
	, CustomChoice = 'BB'
	
	--Choice Multi-Select
	, CustomChoiceMulti = '["AA", "BB"]'
	, CustomChoiceMultiODataType='Collection(Edm.String)' --for multi select must supply OData Type 
	--OR-- append like below in one field
	--, CustomChoiceMulti = '["AA", "BB"], "CustomChoiceMulti@odata.type" : "Collection(Edm.String)"'
	
	--Lookup
	--field with Lookup type must be set to [<field-name>LookupId] field (suffix with LookupId)
	, CustomLookupSingleLookupId = 1 
	
	--Lookup Multi-Select
	, CustomLookupMultiLookupId = '[1,3]' --for multi select must supply OData Type 
	, CustomLookupMultiLookupIdODataType='Collection(Edm.Int32)'
	--OR-- append like below in one field
	--, CustomLookupMultiLookupId = '[1,3], "CustomLookupMultiLookupId@odata.type" : "Collection(Edm.String)"'
	
	--Person 
	,CustomPersonLookupId = 11  
	
	--Person Multi-Select
	, CustomPersonMultiLookupId = '[11, 22]' 
	, CustomPersonMultiLookupIdODataType='Collection(Edm.Int32)'
	--OR-- append like below in one field
	--, CustomPersonMultiLookupIdODataType = '[11,22], "CustomPersonMultiLookupIdODataType@odata.type" : "Collection(Edm.Int32)"'
	
Where Id=1 --List Item Id

--- Insert Example ----

INSERT INTO "Travel Requests" --list name
(
	 Title
	, CustomText 
	, CustomTextLarge 
	, CustomNumber 
	, CustomDateTime 
	, CustomChoice 
	
	, CustomChoiceMulti 
	, CustomChoiceMultiODataType

	, CustomLookupSingleLookupId 
	
	--Lookup Multi-Select
	, CustomLookupMultiLookupId 
	, CustomLookupMultiLookupIdODataType
	
	--Person 
	,CustomPersonLookupId 
	
	, CustomPersonMultiLookupId 
	, CustomPersonMultiLookupIdODataType
)
VALUES(
	  'MyTitle @ <<FUN_NOW>>'  
	, 'This is text'
	, 'This is really long text .. '	                     
	, 10000.12345
	, '2012-12-31T23:59:59.123Z' --End with Z to treat as UTC time (avoid local-UTC conversion)
	, 'BB'

	--Choice Multi-Select
	, '["AA", "BB"]'
	, 'Collection(Edm.String)' --for multi select must supply OData Type 
	
	--Lookup
	,  1 
	
	--Lookup Multi-Select
	, '[1,3]' --for multi select must supply OData Type 
	, 'Collection(Edm.Int32)'
	
	--Person 
	,11  
	
	--Person Multi-Select
	, '[11, 22]' 
	, 'Collection(Edm.Int32)'
)

Delete item from a named list 

DELETE FROM MyList WHERE Id=888
--OR--        
DELETE FROM MyList WITH (ListItemId='123')

--ListItemId can be retrieved by selecting from named list table, e.g. 'MyList'.

Read full image path

<p>Gets the full path of an image file from an Image type field. By default, the Image field returns a JSON document; this example extracts and concatenates fields to get the full URL.</p> 

SELECT *,(JSON_VALUE(MyImageField,'$.serverUrl') || JSON_VALUE(MyImageField,'$.serverRelativeUrl')) as DevicePhotoUrl,
    FROM "My SharePoint List Name"

SQL Server

Use these SQL queries in SQL Server after you create a data source in Data Gateway:

Get list items

<p>Gets items from a specific list. Replace <code>MyList</code> with the actual name of your SharePoint list.</p> 

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM MyList 
--WHERE Id = 1234';

EXEC (@MyQuery) AT [LS_TO_SHAREPOINT_ONLINE_IN_GATEWAY];

Get list items with filter

<p>Gets items from a list using a filter. This mimics a SharePoint view. Use <code>HonorNonIndexedQueriesWarningMayFailRandomly</code> in <code>ExtraHeaders</code> if filtering on non-indexed fields.</p> 

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM MyList 
WITH(Filter=''MyField eq ''''Something'''' '')
--In order to search on non-indexed fields set following value for ExtraHeaders property on connection UI
--Prefer: HonorNonIndexedQueriesWarningMayFailRandomly';

EXEC (@MyQuery) AT [LS_TO_SHAREPOINT_ONLINE_IN_GATEWAY];

Create a list item

<p>Creates a new item in a list. Replace <code>MyList</code> with your list name and specify the columns to populate.</p> 

DECLARE @MyQuery NVARCHAR(MAX) = 'INSERT INTO MyList(Name, Surname)
VALUES (''John'', ''Doe'') WITH (Output=1)';

EXEC (@MyQuery) AT [LS_TO_SHAREPOINT_ONLINE_IN_GATEWAY];

Create a list item with multi-select lookup or person fields

<p>Creates a list item with multi-select Lookup or Person/Group fields. Requires passing values as a JSON array and specifying the OData type (e.g. <code>Collection(Edm.Int32)</code>).</p> 

DECLARE @MyQuery NVARCHAR(MAX) = '--new version --				
INSERT INTO MyList(Title, ManyPersonsLookupId, ManyPersonsLookupIdOdataType)
VALUES (''John Doe'', ''[11,22,33]'', ''Collection(Edm.Int32)'')
WITH (Output=1)

--old version --				
INSERT INTO MyList(Title, ManyPersonsLookupId, "fields.ManyPersonsLookupId@odata\u002Etype")
VALUES (''John Doe'', ''[11,22,33]'', ''Collection(Edm.Int32)'')
WITH (Output=1)
--Considering "ManyPersons" is the name of the field in SharePoint List';

EXEC (@MyQuery) AT [LS_TO_SHAREPOINT_ONLINE_IN_GATEWAY];

Create a list item with multi-select choice fields

<p>Creates a list item with multi-select Choice fields. Requires passing values as a JSON array and specifying the OData type (e.g. <code>Collection(Edm.String)</code>).</p> 

DECLARE @MyQuery NVARCHAR(MAX) = '--new version --				
INSERT INTO MyList(Title, MultiChoiceField, MultiChoiceFieldODataType)
VALUES (''John Doe'', ''["Choice1","Choice5"]'', ''Collection(Edm.String)'')
WITH (Output=1)

--old version --				

INSERT INTO MyList(Title, MultiChoiceField, "fields.MultiChoiceField@odata\u002Etype")
VALUES (''John Doe'', ''["Choice1","Choice5"]'', ''Collection(Edm.String)'')
WITH (Output=1)

--Considering "MultiChoiceField" is the name of the field in SharePoint List';

EXEC (@MyQuery) AT [LS_TO_SHAREPOINT_ONLINE_IN_GATEWAY];

Update a list item

<p>Updates an existing list item by ID.</p> 

DECLARE @MyQuery NVARCHAR(MAX) = 'UPDATE MyList
SET Name = ''Elizabeth'', Surname = ''Johnson''
WHERE Id = ''123''';

EXEC (@MyQuery) AT [LS_TO_SHAREPOINT_ONLINE_IN_GATEWAY];

Update a list item with multi-select fields

<p>Updates multi-select Lookup or Person/Group fields on an existing item.</p> 

DECLARE @MyQuery NVARCHAR(MAX) = 'UPDATE MyList
SET Title = ''Elizabeth Johnson'',
	--Method#1 (Pass type info in the same value)
    ManyPersonsLookupId = ''[11,22,33]'', "fields.ManyPersonsLookupId@odata\u002Etype" = ''Collection(Edm.Int32)''
    --Method#2 (Pass type info via ODataField)
	--OR--
	--ManyPersonsLookupId = ''[11,22,33]'',
    --ManyPersonsLookupIdODataType" = ''Collection(Edm.Int32)
	
WHERE Id = ''123''';

EXEC (@MyQuery) AT [LS_TO_SHAREPOINT_ONLINE_IN_GATEWAY];

Update / Insert Sharepoint List Data (Mixed Data Types - e.g. Multi Choice, Lookup, Person, DateTime, Number) 

DECLARE @MyQuery NVARCHAR(MAX) = 'UPDATE  "Travel Requests" --list name
SET   
	 Title=''MyTitle @ <<FUN_NOW>>''  
	 --Method#1 Pass Value and Type in 2 fields
	, CustomText = ''This is text''
	, CustomTextLarge = ''This is really long text ....''	                     
	, CustomNumber = 10000.12345
	, CustomDateTime =''2012-12-31T23:59:59.123Z'' --End with Z to treat as UTC time (avoid local-UTC conversion)
	, CustomChoice = ''BB''
	
	--Choice Multi-Select
	, CustomChoiceMulti = ''["AA", "BB"]''
	, CustomChoiceMultiODataType=''Collection(Edm.String)'' --for multi select must supply OData Type 
	--OR-- append like below in one field
	--, CustomChoiceMulti = ''["AA", "BB"], "CustomChoiceMulti@odata.type" : "Collection(Edm.String)"''
	
	--Lookup
	--field with Lookup type must be set to [<field-name>LookupId] field (suffix with LookupId)
	, CustomLookupSingleLookupId = 1 
	
	--Lookup Multi-Select
	, CustomLookupMultiLookupId = ''[1,3]'' --for multi select must supply OData Type 
	, CustomLookupMultiLookupIdODataType=''Collection(Edm.Int32)''
	--OR-- append like below in one field
	--, CustomLookupMultiLookupId = ''[1,3], "CustomLookupMultiLookupId@odata.type" : "Collection(Edm.String)"''
	
	--Person 
	,CustomPersonLookupId = 11  
	
	--Person Multi-Select
	, CustomPersonMultiLookupId = ''[11, 22]'' 
	, CustomPersonMultiLookupIdODataType=''Collection(Edm.Int32)''
	--OR-- append like below in one field
	--, CustomPersonMultiLookupIdODataType = ''[11,22], "CustomPersonMultiLookupIdODataType@odata.type" : "Collection(Edm.Int32)"''
	
Where Id=1 --List Item Id

--- Insert Example ----

INSERT INTO "Travel Requests" --list name
(
	 Title
	, CustomText 
	, CustomTextLarge 
	, CustomNumber 
	, CustomDateTime 
	, CustomChoice 
	
	, CustomChoiceMulti 
	, CustomChoiceMultiODataType

	, CustomLookupSingleLookupId 
	
	--Lookup Multi-Select
	, CustomLookupMultiLookupId 
	, CustomLookupMultiLookupIdODataType
	
	--Person 
	,CustomPersonLookupId 
	
	, CustomPersonMultiLookupId 
	, CustomPersonMultiLookupIdODataType
)
VALUES(
	  ''MyTitle @ <<FUN_NOW>>''  
	, ''This is text''
	, ''This is really long text .. ''	                     
	, 10000.12345
	, ''2012-12-31T23:59:59.123Z'' --End with Z to treat as UTC time (avoid local-UTC conversion)
	, ''BB''

	--Choice Multi-Select
	, ''["AA", "BB"]''
	, ''Collection(Edm.String)'' --for multi select must supply OData Type 
	
	--Lookup
	,  1 
	
	--Lookup Multi-Select
	, ''[1,3]'' --for multi select must supply OData Type 
	, ''Collection(Edm.Int32)''
	
	--Person 
	,11  
	
	--Person Multi-Select
	, ''[11, 22]'' 
	, ''Collection(Edm.Int32)''
)';

EXEC (@MyQuery) AT [LS_TO_SHAREPOINT_ONLINE_IN_GATEWAY];

Delete item from a named list 

DECLARE @MyQuery NVARCHAR(MAX) = 'DELETE FROM MyList WHERE Id=888
--OR--        
DELETE FROM MyList WITH (ListItemId=''123'')

--ListItemId can be retrieved by selecting from named list table, e.g. ''MyList''.';

EXEC (@MyQuery) AT [LS_TO_SHAREPOINT_ONLINE_IN_GATEWAY];

Read full image path

<p>Gets the full path of an image file from an Image type field. By default, the Image field returns a JSON document; this example extracts and concatenates fields to get the full URL.</p> 

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT *,(JSON_VALUE(MyImageField,''$.serverUrl'') || JSON_VALUE(MyImageField,''$.serverRelativeUrl'')) as DevicePhotoUrl,
    FROM "My SharePoint List Name"';

EXEC (@MyQuery) AT [LS_TO_SHAREPOINT_ONLINE_IN_GATEWAY];