Querying Metadata with FetchXML

I’ve looked at different ways of accessing CDS metadata recently, but there’s another way I’d overlooked – using FetchXML.

CDS exposes a limited amount of metadata as special virtual entities that lets you query it using standard FetchXML syntax:

<fetch top="1" >
  <entity name="entity" >
    <attribute name="entitysetname" />
    <filter>
      <condition attribute="logicalname" operator="eq" value="account" />
    </filter>
  </entity>
</fetch>

This will help you convert from the logical name of an entity to the collection name that you need to use in Power Automate and anywhere else you use OData / Web API queries.

Running the query

If you build this query in FetchXML Builder and run it, you’ll get the error:

I guess these metadata entities were implemented in Dynamics CRM some time before the modern virtual entity mechanism was in place, and they are only queryable using the older ExecuteFetch method. Hit OK on this error and you do get the results, but as a raw XML document instead of a formatted table.

Because SQL 4 CDS will always use the RetrieveMultiple method you can’t query these entities there. But keep your eyes open for a future release with more metadata goodness!

What metadata is available?

It looks to me that the metadata exposed in this way is oriented towards report building. It’s much more limited than what you can get in the other methods I looked at, but it does include a few extras too.

Entity

The entity entity contains the name used for the entity in different areas, including:

• SQL table
• Filtered view for reporting
• Logical entity & collection names
• Display name

Attribute

The attribute entity doesn’t appear to be documented, but again contains the various names for each attribute:

• SQL column
• Logical name

Strangely it doesn’t appear to contain anything to link it to the containing entity, so you can’t run a query to get the list of attributes in an entity.

Relationship

The relationship entity again doesn’t appear to be documented. It contains the name of the relationship, but no information on what entities it links or the attributes it uses. Largely this seems pretty useless.

Summary

In general, the only useful metadata to be exposed through FetchXML appears to be in the entity type. It exposes some information that’s only relevant to on-premise uses (reportviewname for example, which returns “FilteredAccount” for use in SQL-based SSRS reports – the online TDS endpoint uses the logical name instead) and some that shouldn’t be used at all (basetablename – no-one’s querying the SQL tables directly, right?)

Because you can only query it using the deprecated ExecuteFetchRequest I have to wonder if this is either going to vanish at some point, or going to get overhauled with a modern virtual entity view. Time will tell.

In the meantime though, I’m exploring better ways of querying metadata using SQL 4 CDS which will hopefully be available in the next major update.