EndPoint Read List Items (Dynamic Columns)
Name
get_list_items_dynamic
Description
Use this endpoint if you face issue with NULl values in some columns. This endpoint scans metadata from few sample rows and decides datatypes. Use this if other endpoing showing null values for Complex datatypes (e.g. Lookup, Location, Person)
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 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| There are no Static columns defined for this endpoint. This endpoint detects columns dynamically at runtime. | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
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 (Dynamic Columns) using API Source
Read List Items (Dynamic Columns) using API Destination
ODBC application
Use these SQL queries in your ODBC application data source:
Get list items - dynamic metadata (SharePoint Document Library)
Gets items of a specific list using dynamic metadata approach. Use this if other endpoint is showing null values for complex datatypes (e.g. Lookup, Location, Person)
SELECT * FROM get_list_items_dynamic
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 - dynamic metadata (SharePoint Document Library)
Gets items of a specific list using dynamic metadata approach. Use this if other endpoint is showing null values for complex datatypes (e.g. Lookup, Location, Person)
DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM get_list_items_dynamic
WITH (ListId=''14bdfd1d-1090-4cfe-adc1-XXXXXXXXXXXXXX'')
--You can get ListId by selecting from ''list_lists'' endpoint.';
EXEC (@MyQuery) AT [LINKED_SERVER_TO_SHAREPOINT_ONLINE_IN_DATA_GATEWAY];