Since I've gotten into web programming, I've been stuck (happily) in a LAMP and RoR mindset: model data, normalize, create schema, lean almost entirely on SQL statements wrapped in whatever scripting language I'm using.
When have you forgone the relational db in your web apps with good results?
Look at duels.com (a simple player-vs-player game). On each page it needs to know your stats/equipment and that of the person you are dueling. In an SQL database you might store the person/stats in one table, the inventory in another, and the fight record in yet another. In SimpleDB the stats and inventory could be in one domain with fight record in another.
SimpleDB or even dumb file-per-player based systems don't pay the overhead for allowing arbitrary queries so they scale better. Your SQL store will be slowed down by the weight of inactive players and their inventories and all the indexes and data they have lying around. Using in-memory caching (like memcached) is a popular way to pay the SQL cost less often while keeping the arbitrary-query ability on the back end.
And of course don't forget that SimpleDB is hosted by a farm of machines tweaked and maintained by someone else. Your MySQL setup will never be that cool.
Edit: and as for XML,if you add that to SQL, well now you have two problems.
In essence, RDBMS are highly optimised for the sort of things people do with databases, but you need to know what you're doing with them.
An anecdote on bad linear search: in the 90s I worked on a system in C++ that made heavy use of inheritance and other C-plus-plus-ery. There was a bunch of file opener classes each that found files in a different way (regexp, straight path, glob). They differed only in a method named match. The base class called match on every file name in the directory until it returned true. That was fine for regexps but even if you opened a file _by name_ it took N/2 syscalls to readdir to find it! This worked OK until someone created a directory with 10k entries on a production box. Ouch.