Reference

Endpoint Read List Items

Name

get_list_items

Description

No description available

Related Tables

[Dynamic Table] , ListItems

Parameters

Parameter

Required

Options

Name: ListId

Label: ListId

Specify a list id to search items for

YES

Name: SiteId

Label: SiteId (Re-Select ListId after you change this)

Specify a site

Name: Filter

Label: Filter

To filter data on a field, you will first need to index it: https://support.microsoft.com/en-us/office/add-an-index-to-a-list-or-library-column-f3f00554-b7dc-44d1-a2ed-d477eac463b0

Option	Value
None
Equal Search	fields/SomeColumn eq 'SomeValue'
Substring Search	substringof(fields/SomeField,'abc')
Starts With	startswith(fields/SomeField, 'abc')

Name: expand

Label: Expand (Output Fields)

By default lookup fields are returned as (null). Select fields which you want to process (to speedup processing check only those fields which are (null) in the output)

Output Columns

Label	Data Type (SSIS)	Data Type (SQL)	Length	Description
Id	`DT_WSTR`	`nvarchar(200)`	200
CreatedOn	`DT_DBTIMESTAMP`	`datetime`
CreatedById	`DT_GUID`	`uniqueidentifier`
CreatedByDisplayName	`DT_WSTR`	`nvarchar(200)`	200
ModifiedOn	`DT_DBTIMESTAMP`	`datetime`
LastModifiedId	`DT_GUID`	`uniqueidentifier`
LastModifiedDisplayName	`DT_WSTR`	`nvarchar(200)`	200
[Dynamic Column]	`DT_WSTR`	`nvarchar(2000)`	2000	[Dynamic Column]
[Dynamic Column]LookupId	`DT_I4`	`int`		[Dynamic Column] Id
[Dynamic Column]Label	`DT_WSTR`	`nvarchar(500)`	500	[Dynamic Column] Id
[Dynamic Column]TermGuid	`DT_WSTR`	`nvarchar(100)`	100	Managed Meta [Dynamic Column] TermGuid
[Dynamic Column]WssId	`DT_I4`	`int`		Managed Meta [Dynamic Column] WssId

If the column you are looking for is missing, consider customizing SharePoint Online Connector.

Input Columns

Label	Data Type (SSIS)	Data Type (SQL)	Length	Description
There are no Static columns defined for this endpoint. This endpoint detects columns dynamically at runtime.

Examples

SSIS

Use SharePoint Online Connector in API Source or in API Destination SSIS Data Flow components to read or write data.

API Source

This Endpoint belongs to the [Dynamic Table] table, therefore it is better to use it, instead of accessing the endpoint directly:

API Source - SharePoint Online

SharePoint Connector can be used to read, write data in SharePoint Online List / Document Library, perform file operations such as upload, download, create, move, delete, rename in a few clicks!

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

API Destination

This Endpoint belongs to the [Dynamic Table] table, therefore it is better to use it, instead of accessing the endpoint directly. Use this table and table-operation pair to read list items:

API Destination - SharePoint Online

SharePoint Connector can be used to read, write data in SharePoint Online List / Document Library, perform file operations such as upload, download, create, move, delete, rename in a few clicks!

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 (SharePoint Document Library)

SELECT * FROM get_list_items
WITH (ListId='14bdfd1d-1090-4cfe-adc1-XXXXXXXXXXXXXX')
--You can get ListId by selecting from 'list_lists' endpoint.

Select data from a named list

SELECT * FROM MyList 
--WHERE Id = 1234

Get ListItems

SELECT * FROM ListItems

get_list_items endpoint belongs to [Dynamic Table] , ListItems table(s), and can therefore be used via those table(s).

SQL Server

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

Get list items (SharePoint Document Library)

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM get_list_items
WITH (ListId=''14bdfd1d-1090-4cfe-adc1-XXXXXXXXXXXXXX'')
--You can get ListId by selecting from ''list_lists'' endpoint.';

EXEC (@MyQuery) AT [LS_TO_SHAREPOINT_ONLINE_IN_GATEWAY];

Select data from a named list

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

EXEC (@MyQuery) AT [LS_TO_SHAREPOINT_ONLINE_IN_GATEWAY];

Get ListItems

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM ListItems';

EXEC (@MyQuery) AT [LS_TO_SHAREPOINT_ONLINE_IN_GATEWAY];

get_list_items endpoint belongs to [Dynamic Table] , ListItems table(s), and can therefore be used via those table(s).