为Excel创建OData提供程序 - Power查询显示$ metadata xml

Question

I'm creating an OData provider to be used with Excel with a PHP library: https://github.com/Algo-Web/POData

I've managed to deliver a Sample data set in the browser on my local network with:

http://dss.example.com/proto/odata.svc/Samples?$format=json

{
  "value":[
    {
      "id":"1","firstName":"Rebbecca","lastName":"Didio","companyName":"Brandt, Jonathan F Esq","address":"171 E 24th St","city":"Leith","state":"TA","post":"7315","phone1":"03-8174-9123","phone2":"0458-665-290","email":"[e-mail]","web":"http://www.brandtjonathanfesq.com.au"
    },{
      "id":"2","firstName":"Stevie","lastName":"Hallo","companyName":"Landrum Temporary Services","address":"22222 Acoma St","city":"Proston","state":"QL","post":"4613","phone1":"07-9997-3366","phone2":"0497-622-620","email":"[e-mail]","web":"http://www.landrumtemporaryservices.com.au"
    },{
      "id":"3","firstName":"Mariko","lastName":"Stayer","companyName":"Inabinet, Macre Esq","address":"534 Schoenborn St #51","city":"Hamel","state":"WA","post":"6215","phone1":"08-5558-9019","phone2":"0427-885-282","email":"[e-mail]","web":"http://www.inabinetmacreesq.com.au"
    },{
      "id":"4","firstName":"Gerardo","lastName":"Woodka","companyName":"Morris Downing & Sherred","address":"69206 Jackson Ave","city":"Talmalmo","state":"NS","post":"2640","phone1":"02-6044-4682","phone2":"0443-795-912","email":"[e-mail]","web":"http://www.morrisdowningsherred.com.au"
    },{
      "id":"5","firstName":"Mayra","lastName":"Bena","companyName":"Buelt, David L Esq","address":"808 Glen Cove Ave","city":"Lane Cove","state":"NS","post":"1595","phone1":"02-1455-6085","phone2":"0453-666-885","email":"[e-mail]","web":"http://www.bueltdavidlesq.com.au"
    },{
      "id":"6","firstName":"Idella","lastName":"Scotland","companyName":"Artesian Ice & Cold Storage Co","address":"373 Lafayette St","city":"Cartmeticup","state":"WA","post":"6316","phone1":"08-7868-1355","phone2":"0451-966-921","email":"[e-mail]","web":"http://www.artesianicecoldstorageco.com.au"
    },
    ...
}

and corresponding metadata on http://dss.example.com/proto/odata.svc/$metadata

<?xml version="1.0" encoding="UTF-8"?>
<edmx:Edmx xmlns:edmx="http://schemas.microsoft.com/ado/2007/06/edmx" xmlns="http://schemas.microsoft.com/ado/2009/11/edm" xmlns:cg="http://schemas.microsoft.com/ado/2006/04/codegeneration" xmlns:annotation="http://schemas.microsoft.com/ado/2009/02/edm/annotation" xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata" Version="3.0">
    <edmx:DataServices m:MaxDataServiceVersion="3.0" m:DataServiceVersion="3.0">
        <Schema Namespace="Proto">
            <EntityType OpenType="false" Abstract="false" Name="Sample">
                <Key>
                    <PropertyRef Name="id"/>
                </Key>
                <Property Name="id" Type="Edm.Int64" Nullable="false"/>
                <Property Name="firstName" Type="Edm.String" Nullable="false"/>
                <Property Name="lastName" Type="Edm.String" Nullable="false"/>
                <Property Name="companyName" Type="Edm.String" Nullable="false"/>
                <Property Name="address" Type="Edm.String" Nullable="false"/>
                <Property Name="city" Type="Edm.String" Nullable="false"/>
                <Property Name="state" Type="Edm.String" Nullable="false"/>
                <Property Name="post" Type="Edm.String" Nullable="false"/>
                <Property Name="phone1" Type="Edm.String" Nullable="false"/>
                <Property Name="phone2" Type="Edm.String" Nullable="false"/>
                <Property Name="email" Type="Edm.String" Nullable="false"/>
                <Property Name="web" Type="Edm.String" Nullable="false"/>
            </EntityType>
            <EntityContainer Name="Proto" m:IsDefaultEntityContainer="true">
                <EntitySet Name="Proto" EntityType="Proto.Sample" cg:GetterAccess="Public"/>
            </EntityContainer>
        </Schema>
    </edmx:DataServices>
</edmx:Edmx>

Through recording of headers I have confirmed that Excel makes the following requests:

Request 1 for what appears to be all data. The library responds with JSON to this

User-Agent: Microsoft.Data.Mashup (https://go.microsoft.com/fwlink/?LinkID=304225)
MaxDataServiceVersion: 3.0
OData-MaxVersion: 4.0
Accept: application/json;odata.metadata=minimal;q=1.0,application/json;odata=minimalmetadata;q=0.9,application/atomsvc+xml;q=0.8,application/atom+xml;q=0.8,application/xml;q=0.7,text/plain;q=0.7
Accept-Encoding: gzip, deflate
Connection: Keep-Alive

Request 2 just for JSON

User-Agent: Microsoft.Data.Mashup (https://go.microsoft.com/fwlink/?LinkID=304225)
OData-MaxVersion: 4.0
Accept: application/json;odata.metadata=minimal
Accept-Encoding: gzip, deflate
Host: ds1.example.com

The library responds with JSON to this

Request 3 for atom/xml

Through trial and error I have determined to return the metadata here

User-Agent: Microsoft.Data.Mashup (https://go.microsoft.com/fwlink/?LinkID=304225)
MaxDataServiceVersion: 3.0
Accept: application/json;odata=minimalmetadata;q=1.0,application/atomsvc+xml;q=0.8,application/atom+xml;q=0.8,application/xml;q=0.7,text/plain;q=0.7
Accept-Encoding: gzip, deflate
Host: ds1.example.com
---Request Start: /proto/odata.svc/Samples---
User-Agent: Microsoft.Data.Mashup (https://go.microsoft.com/fwlink/?LinkID=304225)
MaxDataServiceVersion: 3.0
Accept: application/atomsvc+xml;q=0.8,application/atom+xml;q=0.8,application/xml;q=0.7,text/plain;q=0.7
Accept-Encoding: gzip, deflate

However this displays the XML metadata in PowerQuery as the results of the query.

I have also modified my OData backend to return xml as it would when requesting through the browser: http:/dss.example.com/proto/odata.svc/Samples

For this variant Excel shows an error:

"Odata: The given URL neither points to an OData service or a feed: "

I'm new to OData, the purpose here is to deliver Table data over a web portal without requiring to install anything on the machine running Excel, otherwise I would use ODBC

Answer 1

By documentation :

OData supports two formats for representing resources, the XML-based Atom format and the JSON format. As described in the HTTP specification [RFC2616], clients can indicate their preference of resource representation by including an accept request header with a list of MIME types it can handle.

It means you can use only two formats ATOM and JSON. However, by default metadatareturn xml format. It means you can change format and for ResourcePath by default.

Algo-Web/POData has BaseService.php. There are TargetKind enum. TargetKind has RESOURCE. RESOURCE is Entity (like Samples in your project http://dss.example.com/proto/odata.svc/Samples).

I think you need change BaseService.php in the case:

case TargetKind::RESOURCE():
                return HttpProcessUtility::selectMimeType(
                    $requestAcceptText,
                    array_merge(
                        [MimeTypes::MIME_APPLICATION_ATOM],
                        $baseMimeTypes
                    )
                );

and change MimeTypes::MIME_APPLICATION_ATOM to MimeTypes.MIME_APPLICATION_XML.

for example

return HttpProcessUtility::selectMimeType(
                    $requestAcceptText,
                    array_merge(
                        [MimeTypes::MIME_APPLICATION_XML],
                        $baseMimeTypes
                    )
  );