Convert OData to FetchXML

[rappen]

OData / Flow ➡ FetchXML.

Get query:

1. Paste full OData query
2. Paste one/several parts from Flow Dataverse List rows parameter items

Create FetchXML from this.

Like the same as we have for FetchXML ➡ OData and Flow parameters.

[MarkMpn]

It should be possible, but... are people writing queries in OData format first that need to be converted back? Just trying to understand the use-case.

Or is it more like:

1. Write the query in FetchXML Builder
2. Convert it to OData and copy it into your Flow
3. Close FetchXML Builder & don't save the query
4. Realise you need to edit the query in the Flow and don't want to touch the OData

[rappen]

Or easier: From anywhere you find OData (or the Flow List Rows with parameters, not a "full" OData) and start from that, and want to investigate the query in FXB, which is easier than in Flow - or wherever. And who knows why people would need it... 😉

[rafek1241]

In older projects we had xrm.sdk nuget with fetch xml queries and used fetch xml builder + sql 4 cds to verify if something went wrong.

Currently we're using Simple.OData.Client + our custom wrapper on that package to use that type of processing in communication with dynamics. So in our current flow we have only a OData URIs. We use additionally postman/IDE debugging/logs to review our requests that we sent. In my opinion OData-way is the most clean approach (the most minimized queries).

[BenediktBergmann]

One use-case I often have is that I create the OData query in FXB to be used in a Cloud flow. Sometimes I get a changed requirement or have to change the request for some other reason. In that case, I would have to adjust the OData manually (merge old with new generated) or rebuild it in FXB to export a new version. If we could automatically convert it to FetchXML, do our changes and convert it back to OData it would save a lot of time.

[BenBitDesign]

I would also enjoy this feature for the same reasons

[roblom]

I would use it to easier inspect the query and make changes to it

[JimiXPAC]

When inspecting Power Automate Flows written by others, trying to interpret existing complex OData queries (e.g. multiple levels of expands and nested expands) can be difficult. Being able to convert them back to the FetchXML they were most likely generated from originally would make understanding and editing them much simpler.

[ralphcorrigan]

Just adding another bump on here. I could do with this feature, right now (for most of the reasons outlined above). Many thanks for the tool though!

[MarkMpn]

I've been looking into this some more. I think the problem in doing this generically is that even slightly complex OData queries cannot be represented in a single FetchXML query. For example, the query:

/accounts?$select=name&$expand=contact_customer_accounts($select=fullname;$filter=firstname eq 'Mark')

will be executed by the server as this composite query:

<fetch distinct="false" useraworderby="false" no-lock="false" mapping="logical">
    <entity name="contact">
        <attribute name="fullname" />
        <attribute name="parentcustomerid" />
        <filter type="and">
            <condition attribute="firstname" operator="eq" value="Mark" />
        </filter>
        <link-entity name="account" to="parentcustomerid" from="accountid" link-type="inner"
            alias="_LinkEntityAliasPrefix_contact_customer_accounts" />
        <fetch distinct="false" useraworderby="false" no-lock="false" mapping="logical" page="1"
            count="5000">
            <entity name="account">
                <attribute name="name" />
                <attribute name="accountid" />
                <attribute name="owningbusinessunit" />
                <attribute name="ownerid" />
            </entity>
        </fetch>
    </entity>
</fetch>

It seems the inner query is executed to get the list of account records, and the results of this are combined with the results of the outer query to populate the child collection of contacts.

While this FetchXML can most easily be generated by intercepting the query as it is executed on the server, the resulting composite query cannot practically be converted back to OData after making any required changes.

Alternatively a more lax manual conversion could potentially convert this into a more idiomatic FetchXML:

<fetch>
  <entity name="account">
    <attribute name="name" />
    <link-entity name="contact" from="customerid" to="accountid" link-type="outer">
      <attribute name="fullname" />
      <filter>
        <condition attribute="firstname" operator="eq" value="Mark" />
      </filter>
    </link-entity>
  </entity>
</fetch>

This would probably be my preferred option as the FetchXML is much more understandable and would allow it to be converted back to OData again after making any changes. I don't have time to work on this, but if anyone is able to take this on there are several OData query parsers available on NuGet which could be a good starting point.

[rappen]

Maybe making a two-step...? OData -> SQL -> FetchXml FXB->DynamicODataToSQL->SQL4CDS->FXB

[MarkMpn]

I hadn't seen that project before. Unfortunately it doesn't support expand or lambda operators so it still wouldn't work for anything beyond a simple query. Doing the conversion without supporting these would be easy enough, not sure if that gives any value or does it really need to support expand to be useful?

[rappen]

Hm, I agree... simple queries I can create the code for it easily, but I guess most of the users don't need to convert simple queries...