This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
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
Hi @Joost Verhoog
As tag "office-excel-itpro" focuses on general issue of Excel clients, at present, I have not found the basis for Excel to determine the version of Odata.
I suggest you go to File > Options > Data > Tick the box of "From OData Data Feed (Legacy)" under Show legacy data import wizards section. Then please try this legacy wizard to connect your ODada.
Thanks @Emily Hua-MSFT , this gives some more insights. The legacy wizard does understand the contract, and allows me to connect to it. The subsequent data retrieval fails because it requires Atom XML and the service only provides JSON, but that's beside the point of this issue.
My original question remains: how does the original wizard determine the OData version to use? I am the author of the service, and I'm happy to change it to meet this wizard's needs.
Hi @Joost Verhoog
Thanks for your reply.
About this issue I would suggest you contact Microsoft for a confirm, as this question involves the description of the working mechanism of Power Query connecting to OData.
Thanks for your understanding.
Hi @Emily Hua-MSFT , the link that you shared is for global customer service phone numbers. Are you suggesting I contact them by phone?
Sign in to comment