Dataverse internals: how to get the Sitemap of a given Model Driven App 🤔

If you're struggling to retrieve the sitemap used by a given Model Driven App, maybe it's because relationship between the two is not 1-N (as one may expect, given the fact that each MDA has one and only one sitemap), but it's mediated by the appmodulecomponent table, as per the schema below:

Given this statement, if you need to retrieve the sitemap you can use the following query:

SELECT TOP 1
    am.appmoduleid,
    am.name,
    am.uniquename,
    sm.sitemapid,
    sm.sitemapname,
    sm.sitemapxml
FROM 
    appmodule am
    LEFT OUTER JOIN appmodulecomponent amc ON am.appmoduleidunique = amc.appmoduleidunique
    LEFT OUTER JOIN sitemap sm ON amc.objectid = sm.sitemapid
WHERE 
    amc.componenttype = 62  -- 62: sitemap
    AND am.uniquename = 'YOUR APP UNIQUE NAME'

or, if you prefer FetchXML syntax:

<fetch top="1">
  <entity name="appmodule">
    <attribute name="appmoduleid" />
    <attribute name="name" />
    <attribute name="uniquename" />
    <link-entity name="appmodulecomponent" to="appmoduleidunique" from="appmoduleidunique" alias="amc" link-type="outer">
      <link-entity name="sitemap" to="objectid" from="sitemapid" alias="sm" link-type="outer">
        <attribute name="sitemapid" />
        <attribute name="sitemapname" />
        <attribute name="sitemapxml" />
      </link-entity>
      <filter>
        <condition attribute="componenttype" operator="eq" value="62" />
      </filter>
    </link-entity>
    <filter>
      <condition attribute="uniquename" operator="eq" value="YOUR APP UNIQUE NAME" />
    </filter>
  </entity>
</fetch>

same, in the QueryExpression syntax:

var query = new QueryExpression("appmodule");
query.ColumnSet.AddColumns("appmoduleid", "name", "uniquename");
var amc = query.AddLink("appmodulecomponent", "appmoduleidunique", "appmoduleidunique", JoinOperator.LeftOuter);
var sm = amc.AddLink("sitemap", "objectid", "sitemapid", JoinOperator.LeftOuter);
sm.Columns.AddColumns("sitemapid", "sitemapxml", "sitemapname");

amc.EntityAlias = "amc";
sm.EntityAlias = "sm";

amc.LinkCriteria.AddCondition("componenttype", ConditionOperator.Equal, 62); // 62 = Sitemap
query.Criteria.AddCondition("uniquename", ConditionOperator.Equal, appModuleName);
query.TopCount = 1;

Hope this helps!