There is an out of the box api that we can use to generate SQL from FetchXml. It’s called ‘FetchXMLToSQL’. You can call it using C# like this:

var request = new OrganizationRequest("FetchXMLToSQL");
request["FetchXml"] = "<fetch><entity name='account'><attribute name='accountid'/><attribute name='name'/></entity></fetch>";
var response = client.Execute(request);
var sql = (string)response["Response"];

The response will contain an item called ‘Response’. For this example, the outputted SQL will look like this:

"account0".accountid as "accountid"
, "account0".name as "name"
Account as "account0"

You can use this query against the Dataverse Tabular Data Stream (TDS) endpoint:

It seems that we cannot call this message using the OData web api, or Power Automate, so you have to use the OrganizationService to perform this action.

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *