Extracting entity name from FetchXml columns in Dynamics 365 Database

Question

I'm trying to write a SQL query that shows all Advanced Find views owned by a specific user in Dynamics 365 b8.2 (on-prem).

This is pretty straight-forward in terms of getting the view name, and User from the UserQuery view, but the entity related to that view is only stored as part of the FetchXML, so I need to extract that.

Can I use XPath on Fetch XML? Any help would be awesome! Two examples of the FetchXML are as follows (the entity is Account):

<fetch mapping="logical" version="1.0" distinct="false" output-format="xml-platform">
  <entity name="account">
    <attribute name="name" />
    <attribute name="primarycontactid" />
    <attribute name="telephone1" />
    <attribute name="address2_city" />
    <attribute name="parentaccountid" />
    <attribute name="new_navnumber" />
    <attribute name="emailaddress1" />
    <attribute name="new_accountmanager" />
    <attribute name="accountclassificationcode" />
    <attribute name="accountid" />
    <order descending="false" attribute="name" />
    <filter type="and">
      <condition value="0" attribute="statecode" operator="eq" />
      <condition value="1" attribute="new_isshareholder" operator="eq" />
      <condition attribute="accountclassificationcode" operator="in">
        <value>200001</value>
        <value>200003</value>
        <value>200005</value>
        <value>200007</value>
        <value>200009</value>
        <value>200011</value>
        <value>100000000</value>
        <value>200012</value>
      </condition>
    </filter>
  </entity>
</fetch>

jasonscript jasonscript · Accepted Answer · 2018-08-23T03:47:51

FetchXml is valid XML so yes you can use xpath

Something like /fetch/entity/@name should work. Views owned by Users should be ok, but be aware that there are some non-standard system views hidden away in the system

Extracting entity name from FetchXml columns in Dynamics 365 Database

2 Answers