ODATA: Unable to expand Attachments from SharepointOnline

Question

My objective is to use SSIS with an ODATA source to connect to a Sharepoint Online list. My aim is to extract the ServerRelativeUrl for the attachments of all items within the list.

I'm as yet unable to get the SSIS ODATA Connector to return the attachments, so as a preliminary step in my SSIS development, I'm first trying to access the attachment from within my browser. I've read that I should be expanding "AttachmentFiles" from my list, however when I try to do so I get the error that my list "does not have a property named AttachmentFiles". When checking my list itself, I see that my field is simply named Attachments, so I then use the following URL in my browser:

 https://<sitename>.sharepoint.com/sites/FieldManagement/_vti_bin/listdata.svc/MerchandiserInspection?/_api/lists/getbytitle(%27Merchandiser%20Inspection%27)/items?$select=AttachmentsItem/ServerRequestUrl&$expand=Attachments

Atthis point it's noteworthy that no matter what values I enter into the $select parameter (even if I make obvious errors), it seems to have no bearing on that which is returned. Also, I don't see ant fields/parameters called ServerRequestURL, so I don't believe that it is having any impact.

An excerpt of one of the items (ID 41) returned, is as follows:

<entry m:etag="W/&quot;12&quot;">
    <id>https://<sitename>.sharepoint.com/sites/FieldManagement/_vti_bin/listdata.svc/MerchandiserInspection(41)</id>
    <title type="text"></title>
    <updated>2019-04-03T20:24:34-07:00</updated>
    <author>
        <name />
    </author>
    <link rel="edit" title="MerchandiserInspectionItem" href="MerchandiserInspection(41)" />
    <link rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/Representative" type="application/atom+xml;type=entry" title="Representative" href="MerchandiserInspection(41)/Representative" />
    <link rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/CreatedBy" type="application/atom+xml;type=entry" title="CreatedBy" href="MerchandiserInspection(41)/CreatedBy" />
    <link rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/ModifiedBy" type="application/atom+xml;type=entry" title="ModifiedBy" href="MerchandiserInspection(41)/ModifiedBy" />
    <link rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/Attachments" type="application/atom+xml;type=feed" title="Attachments" href="MerchandiserInspection(41)/Attachments">
        <m:inline>
            <feed>
                <title type="text">Attachments</title>
                <id>https://<sitename>.sharepoint.com/sites/FieldManagement/_vti_bin/listdata.svc/MerchandiserInspection(41)/Attachments</id>
                <updated>2019-04-10T20:41:42Z</updated>
                <link rel="self" title="Attachments" href="MerchandiserInspection(41)/Attachments" />
                <entry>
                    <id>https://<sitename>.sharepoint.com/sites/FieldManagement/_vti_bin/listdata.svc/Attachments(EntitySet='MerchandiserInspection',ItemId=41,Name='D2DDAC5A-304C-4594-9E3F-FF270DEFD5C0.jpg')</id>
                    <title type="text">D2DDAC5A-304C-4594-9E3F-FF270DEFD5C0.jpg</title>
                    <updated>2019-04-10T20:41:42Z</updated>
                    <author>
                        <name />
                    </author>
                    <link m:etag="&quot;{B8A3E4EF-5B96-4AE4-B240-08C462661F4E},1&quot;" rel="edit-media" title="AttachmentsItem" href="Attachments(EntitySet='MerchandiserInspection',ItemId=41,Name='D2DDAC5A-304C-4594-9E3F-FF270DEFD5C0.jpg')/$value" />
                    <link rel="edit" title="AttachmentsItem" href="Attachments(EntitySet='MerchandiserInspection',ItemId=41,Name='D2DDAC5A-304C-4594-9E3F-FF270DEFD5C0.jpg')" />
                    <category term="Microsoft.SharePoint.DataService.AttachmentsItem" scheme="http://schemas.microsoft.com/ado/2007/08/dataservices/scheme" />
                    <content type="image/jpeg" src="https://<sitename>.sharepoint.com/sites/FieldManagement/Lists/Merchandiser%20Inspection/Attachments/41/D2DDAC5A-304C-4594-9E3F-FF270DEFD5C0.jpg" />
                    <m:properties xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata" xmlns:d="http://schemas.microsoft.com/ado/2007/08/dataservices">
                        <d:EntitySet>MerchandiserInspection</d:EntitySet>
                        <d:ItemId m:type="Edm.Int32">41</d:ItemId>
                        <d:Name>D2DDAC5A-304C-4594-9E3F-FF270DEFD5C0.jpg</d:Name>
                    </m:properties>
                </entry>
            </feed>
        </m:inline>
    </link>
    <category term="Microsoft.SharePoint.DataService.MerchandiserInspectionItem" scheme="http://schemas.microsoft.com/ado/2007/08/dataservices/scheme" />
    <content type="application/xml">
        <m:properties>
            <d:StoreVisitDate m:type="Edm.DateTime">2018-11-15T10:21:00</d:StoreVisitDate>
            <d:RepresentativeId m:type="Edm.Int32">16</d:RepresentativeId>
            <d:Title m:null="true" />
            <d:ContentTypeID>0x010007EFE22AD1BAFC4994BA443D963003FB</d:ContentTypeID>
            <d:ComplianceAssetId m:null="true" />
            <d:Id m:type="Edm.Int32">41</d:Id>
            <d:ContentType>Item</d:ContentType>
            <d:Modified m:type="Edm.DateTime">2019-04-03T20:24:34</d:Modified>
            <d:Created m:type="Edm.DateTime">2018-11-15T10:36:10</d:Created>
            <d:CreatedById m:type="Edm.Int32">16</d:CreatedById>
            <d:ModifiedById m:type="Edm.Int32">6</d:ModifiedById>
            <d:Owshiddenversion m:type="Edm.Int32">12</d:Owshiddenversion>
            <d:Version>12.0</d:Version>
            <d:Path>/sites/FieldManagement/Lists/Merchandiser Inspection</d:Path>
        </m:properties>
    </content>
</entry>

From the above extract, the value that I am looking to retrieve is the

<content type="image/jpeg" src="https://<sitename>.sharepoint.com/sites/FieldManagement/Lists/Merchandiser%20Inspection/Attachments/41/D2DDAC5A-304C-4594-9E3F-FF270DEFD5C0.jpg"

where you can see that the full URL of the image for Item with ID 41.

However, when using this URL within my SSIS ODATA connector, the Attachment URL is not included in my result set and so I'm assuming that I'm just not expanding the correct object or selecting the correct fields.

Answer 1

First of all, I don't think you need to use the old _vti_bin/listdata.svc endpoint in conjunction with the newer REST API, so your URL should be simply:

https://<sitename>.sharepoint.com/sites/FieldManagement/_api/lists/getbytitle(%27Merchandiser%20Inspection%27)/items?$select=AttachmentsItem/ServerRequestUrl&$expand=Attachments

Second, to get the information about the attachments, I'm not sure if there is a way to expand the attachment info for all the items at the same time. I just tried a bunch of tests in Postman, and none of the queries where I tried to $select and $expand on the AttachmentFiles property worked, they mostly returned the error

Value does not fall within the expected range.

You can get the attachment information for a single item like this:

https://<sitename>.sharepoint.com/sites/FieldManagement/_api/lists/getbytitle(%27Merchandiser%20Inspection%27)/items/getbyid(41)/AttachmentFiles

To get the information for all the items might be a two-step process, because you can do

https://<sitename>.sharepoint.com/sites/FieldManagement/_api/lists/getbytitle(%27Merchandiser%20Inspection%27)/items?$select=AttachmentFiles

(without the $expand) which, if you set your accept header to application/json;odata=verbose to get back JSON instead of XML, will return an array of simple JSON objects with the AttachmentFiles property for each of the list items, and within that AttachmentFiles property you will find

AttachmentFiles.__deferred.uri

which is a URL pretty much exactly like the one I mentioned above to get the attachment info for a single item, with the getbyid(x) already set to the correct list item ID.

I'm not used to working with the SP REST services returning XML, so I don't know exactly how that would be formatted if you need it back in XML.

But still, that two step process could work, because you don't need to know any list item IDs to make the first query to get the AttachmentFiles property for all the list items, and then once you get that, you don't need to know any list item IDs because it's already encoded in the AttachmentFiles.__deferrred.uri value.

---

Just did a quick test without the accept header, and that secondary URL does get returned, just in a slightly different format:

When getting a JSON response, the __deferred.uri value is an absolute URL you can just turn around and plug right into another query.