3

Unable to select expanded properties of an expanded type, when using a query with $expand operator.

Sharepoint 2016 list contains a 'Person or Group' field (column=Client), when querying the list:

SSIS 2017 Odata query:

.../_vti_bin/listdata.svc/mylist?$select=Client,Client/UserName&$expand=Client

Only ClientId is returned in the preview.

When using the browser with above url, the XML scheme contains desired properties - nested.

Any suggestions? Also, the odata source is using V3. Is it possible to force it to use V4? If so, how?

Hadi
  • 36,233
  • 13
  • 65
  • 124
Strig
  • 43
  • 5
  • Did you ever figure this out? Trying to get the same thing over here. – Ethan Aug 20 '20 at 17:30
  • I know this is old, but for SharePoint 2016 you'll probably have better luck using the newer REST endpoints. Instead of `/_vti_bin/listdata.svc/mylist` you can use `/_api/web/lists/getByTitle('mylist')/items` – Thriggle Feb 10 '21 at 14:34

1 Answers1

0

ODATA with Sharepoint $expand

Try modifying the Select part as following:

.../_vti_bin/listdata.svc/mylist?$select=Client/Name,ClientId&$expand=Client

Similar issue posts

Additional Information

Sharepointlist adapter

I really don't like using ODATA Source to read from Sharepoint since it is a generic component for Open Data. Instead, in order to read from a Sharepoint list, you can use the SharepointListAdapter which is a component created by Microsoft community:

The project was published on Codeplex, later after Codeplex is turned into archive it is published on GitHub

  • Official documentation Download the white paper, it contains a step by step guide on how to use this component
Community
  • 1
  • 1
Hadi
  • 36,233
  • 13
  • 65
  • 124
  • thank you. I just wonder how to fetch so called "expanded" columns with the list adapter. Say I have a column of a "Person or Group" type, that has many other "hidden" properties I would like to cast to columns. – Strig Apr 07 '19 at 19:05
  • I don't really have much experience in reading from Sharepoint using ODATA components. But i think there are not developed for Sharepoint so you may not have all functionalities supported. I will try to search more for this issue – Hadi Apr 07 '19 at 19:29
  • Are you able to fetch 'Person or Group' extended properties as columns with SharepointListAdapter? – Strig Apr 07 '19 at 19:46
  • @Strig i am not sure if SharepointList can solve the issue, you can give a try if ou didn't find a solution using ODATA – Hadi Apr 07 '19 at 20:26