Microsoft have recently released a new in-depth set of documentation for FetchXML, and it includes some extra features that haven’t been supported before.
The new documentation contains examples and comparisons to SQL for each of the elements of the language, similar to my previous Inside FetchXML series.
Along with the previously well-understood features, the documentation also includes some information for the first time about previously undocumented features which could help unlock more powerful and efficient queries.
I’m not going to repeat the documentation here, please use the links to refer to all the latest content on Microsoft Learn. Instead I’ll just highlight some of the interesting new features.
Additional Join Types
I covered the in, exists and matchfirstrowusingcrossapply join types in a blog article a few years ago, but now they are officially supported complete with examples and equivalent SQL
in and exists let you filter rows based on another table (something like “show me contacts that have had an email in the past month”) without having to deal with the multiple rows that would be introduced by an inner join.
matchfirstrowusingcrossapply does a similar job, but allows you to actually get the data from a single row in the related table as well.
More Ordering Flexibility
When you add sort orders to a query that involves multiple tables, the results are sorted on the main table first, then by the linked tables. So if you have a query like:
<fetch>
<entity name=”account”>
<attribute name=”name” />
<link-entity name=”contact” from=”parentcustomerid” to=”accountid”>
<attribute name=”fullname” />
<order attribute=”fullname” />
</link-entity>
<order attribute=”name” />
</entity>
</fetch>
the results will always be ordered by the account name first, then by the contact name. To order by the contact name first you’d have to rearrange your query.
With the addition of the newly-documented entityname attribute on the <order> element we can now override this:
<fetch>
<entity name=”account”>
<attribute name=”name” />
<link-entity name=”contact” from=”parentcustomerid” to=”accountid”>
<attribute name=”fullname” />
</link-entity>
<order entityname=”contact” attribute=”fullname” />
<order attribute=”name” />
</entity>
</fetch>
Cross-Table Column Comparisons
When the ability to filter by comparing two columns against each other was released (rather than a column against a fixed value), it was limited to columns within the same table. That restriction is now officially lifted, so we could add a filter to the earlier example query to find accounts & contacts that have the same email address as each other by adding:
<filter>
<condition attribute=”emailaddress1″ operator=”eq” valueof=”contact.emailaddress1″ />
</filter>
Subquery Filters
The in and exists join types mentioned earlier are helpful, but you can’t combine them with other filters very flexibly. The new option to include subqueries within a filter means that this is now possible for the following scenarios:
| FetchXML link-type | SQL equivalent | Description |
|---|---|---|
any | exists | Finds records that have at least one matching related record |
not any | not exists | Finds records that do not have any matching related records |
not all | exists | Same as the any link-type |
all | exists and not exists | Same as the not any link-type but with an extra check that there are some related records that do not match the filters |
The not all and all link-types take a bit of getting used to with their back-to-front naming. Erik Donker shared a good walkthrough of these on his blog a couple of months ago as well.
Wrapping Up
I’ve tried to highlight some of the FetchXML features which are now documented officially for the first time, but it’s well worth browsing through the rest of the documentation as well. It’s presented in a nice step-by-step format and there’s also a complete reference guide as well.
I’ve started building some of these features into the next version of SQL 4 CDS as well so it can use them to generate more efficient queries, keep an eye out for that coming soon!CATEGORY: FETCHXML
Post navigation
Previous post:SQL 4 CDS v8 Released
LEAVE A REPLY
Your email address will not be published. Required fields are marked *
COMMENT
NAME *
EMAIL *
WEBSITE
SAVE MY NAME, EMAIL, AND WEBSITE IN THIS BROWSER FOR THE NEXT TIME I COMMENT.
This site uses Akismet to reduce spam. Learn how your comment data is processed.
I usually do not create a lot of responses, but i did a few searching and
wound up here NEW FETCHXML FEATURES AND DOCUMENTATION
| Powser Apps Ninja. And I actually do have 2 questions foor you if you don’t mind.
Could it be only me or does it give the impression like some of these responses look as if they
are coming from brain dead visitors? 😛 And, if you are writing onn other online social sites,
I would like to follow anything fresh you have to post. Woild you
make a list of every one of yoir public sites
like your twitter feed, Faceboo pagve or linkedin profile? https://Odessaforum.biz.ua/
I usually do not create a lot of responses, but i did a few searching and wund upp here NEW FETCHXML FEATURES AND DOCUMENTATION | Powewr Apps Ninja.
And I actually do have 2 questions for you if you don’t mind.
Could it bbe nly me or does it give the impression like some of these responses look as if they arre
coming from brain dead visitors? 😛 And, if youu are writing on other online social sites,
I would like to follow anything fresh you have to post.
Wuld you make a list of every one of your public sites lile your
twitter feed, Facebook page or linkedin profile?
my web sitre … https://Odessaforum.biz.ua/
You are not right. Let’s discuss it. Write to me in PM, we will communicate.