EndPoint Read List Items
Parameters
| Parameter | Label | Required | Options | Description | ||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| ListId | ListId | YES | Specify a list id to search items for | |||||||||||
| SiteId | SiteId (Re-Select ListId after you change this) | NO | Specify a site | |||||||||||
| Filter | Filter | NO |
|
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 [API reference] | ||||||||||
| expand | Expand (Output Fields) | NO | 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 | Raw | Description |
|---|---|---|---|---|---|
| Id |
DT_WSTR
|
nvarchar(200)
|
200 | False |
|
| CreatedOn |
DT_DBTIMESTAMP
|
datetime
|
False |
||
| CreatedById |
DT_GUID
|
uniqueidentifier
|
False |
||
| CreatedByDisplayName |
DT_WSTR
|
nvarchar(200)
|
200 | False |
|
| ModifiedOn |
DT_DBTIMESTAMP
|
datetime
|
False |
||
| LastModifiedId |
DT_GUID
|
uniqueidentifier
|
False |
||
| LastModifiedDisplayName |
DT_WSTR
|
nvarchar(200)
|
200 | False |
|
| [Dynamic Column] |
DT_WSTR
|
nvarchar(2000)
|
2000 | False |
[Dynamic Column] |
| [Dynamic Column]LookupId |
DT_I4
|
int
|
False |
[Dynamic Column] Id | |
| [Dynamic Column]Label |
DT_WSTR
|
nvarchar(500)
|
500 | False |
[Dynamic Column] Id |
| [Dynamic Column]TermGuid |
DT_WSTR
|
nvarchar(100)
|
100 | False |
Managed Meta [Dynamic Column] TermGuid |
| [Dynamic Column]WssId |
DT_I4
|
int
|
False |
Managed Meta [Dynamic Column] WssId |
Input Columns
| Label | Data Type (SSIS) | Data Type (SQL) | Length | Raw | 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 component to read data or in API Destination component to read/write data:
Read List Items using API Source
SharePoint Online
Read List Items
Read List Items using API Destination
SharePoint Online
Read List Items
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.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];