How does Excel determine the OData version?

Joost Verhoog 1 Reputation point
2022-01-25T19:14:46.27+00:00

I have an OData 4 service running on my local machine.

In an Excel workbook, I go to Data > Get Data > From Other Sources > From OData Feed, type the service's URL and click OK.

Excel shows an error: Unable to connect. Details: "OData: The feed's metadata document appears to be invalid. Error: The metadata document could not be read from the message content.UnexpectedXmlElement : The element 'edmx:Edmx' was unexpected for the root element. The root element should be Edmx. : (0, 0)
"

It looks like Excel tries to parse the metadata as OData 3, but it's OData 4. I can see that t's OData 4 when I look at the start of the metadata file:

<edmx:Edmx xmlns:edmx="http://docs.oasis-open.org/odata/ns/edmx" Version="4.0">

I think there is something wrong with the OData service, because Excel is able to connect to

https://services.odata.org/V4/Northwind/Northwind.svc/, so clearly it supports OData 4.

So my questions is: How does it determine which OData version to use? Or: how should my OData service change so that Excel understands it's OData 4?

I'm using Microsoft® Excel® for Microsoft 365 MSO (Version 2112 Build 16.0.14729.20254) 64-bit

Excel Management
Excel Management
Excel: A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.Management: The act or process of organizing, handling, directing or controlling something.
1,639 questions
{count} votes