Also the "ability to write custom sql" is more interesting in terms of what use case it which it will be involved than how to do it in a specific tool.

We have had to use custom SQL queries in our domain model/DAL (used via our O/R Mapper) in certain cases where our database does silly things that cannot be changed due to other applications using the database. One such situation with XPO was to get a query of people who are subscribed to a mailing list. The problem is that the mailing list subscriptions are stored as bits in a long within the database so you have to do bitwise operations. (At present) XPO does not have a query mechanism to generate bitwise compares in SQL so we had to write this sql and then execute it using XPO. It actually worked out fine - though I do agree it is a bit of a hack... However, as you mention this should definitely be the exception rather than the rule for needing custom sql...