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 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 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 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.
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.
2 thoughts on “Querying Metadata with FetchXML”
Regarding ‘Attribute’ metadata above you mention – “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.” Is that still the case. I’ve been reading through more recent blogs posts / release notes and I can’t find anything that explains how I can get list of Attributes (Columns) for a specific Entity (Table).
I don’t believe this is possible with FetchXML. You do have a few options to choose from however. In XrmToolBox the standard tool for this would be Metadata Browser, but you can also use SQL 4 CDS (
SELECT * FROM metadata.attribute WHERE entitylogicalname = 'account'). If you’re using C# you can use the
RetrieveEntityRequestto get all the attribute metadata, or
RetrieveMetadataChangesRequestto get a more selective set. Finally you can also use Web API to query the metadata.