Endpoint Read List Items

Name

get_list_items

Description

No description available

Related Tables

[Dynamic Table] , ListItems

Parameters

Output Columns

Input Columns

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

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

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

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

ODBC application

Use these SQL queries in your ODBC application data source:

Read items from a list

<p>Reads list items from any SharePoint list when you supply <code>ListId</code>. For a document library, each item corresponds to a file or folder row. Turn on metadata detection below if you need extra or hidden fields (e.g. Link field in Lists, Content Type Id, etc. which are not included by default in the standard endpoint).</p>

SELECT * FROM get_list_items
WITH (
	  ListId='14bdfd1d-1090-4cfe-adc1-XXXXXXXXXXXXXX'
	 -- Turn on Metadata Detection to get additional fields (e.g. Link to item, Content Type Id, etc.) that are not included by default in the standard endpoint. This is especially useful for document libraries.
	 --,MetaDetectionOrder='MergeStaticDynamic' 
	)
--Obtain ListId from the Lists table or list_lists (e.g. SELECT id, name FROM Lists).

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

Get ListItems

SELECT * FROM ListItems

SQL Server

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

Read items from a list

<p>Reads list items from any SharePoint list when you supply <code>ListId</code>. For a document library, each item corresponds to a file or folder row. Turn on metadata detection below if you need extra or hidden fields (e.g. Link field in Lists, Content Type Id, etc. which are not included by default in the standard endpoint).</p>

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM get_list_items
WITH (
	  ListId=''14bdfd1d-1090-4cfe-adc1-XXXXXXXXXXXXXX''
	 -- Turn on Metadata Detection to get additional fields (e.g. Link to item, Content Type Id, etc.) that are not included by default in the standard endpoint. This is especially useful for document libraries.
	 --,MetaDetectionOrder=''MergeStaticDynamic'' 
	)
--Obtain ListId from the Lists table or list_lists (e.g. SELECT id, name FROM Lists).';

EXEC (@MyQuery) AT [LS_TO_SHAREPOINT_ONLINE_IN_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];

Get ListItems

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

EXEC (@MyQuery) AT [LS_TO_SHAREPOINT_ONLINE_IN_GATEWAY];