I am astonished of FOR XML feature in MSSQL. Oracle, despite claiming they had XML support first, has nothing like that; and XML support in PgSQL and MySQL is just ridiculuos:
The key feature of FOR XML is that is allows fetching multiple tables in just one query.
For example, below SELECT fetches Customer-Order-Shipper-OrderDetail-Product-Supplier-Category relationship:
SELECT *, ( SELECT *, ( SELECT *, ( SELECT *, ( SELECT Suppliers.SupplierID, Suppliers.CompanyName FROM Suppliers WHERE Suppliers.SupplierID = Products.SupplierID FOR XML PATH('Supplier'), TYPE ), ( SELECT Categories.CategoryID, Categories.CategoryName, Categories.Description FROM Categories WHERE Categories.CategoryID = Products.CategoryID FOR XML PATH('Category'), TYPE ) FROM Products WHERE Products.ProductID = OrderDetails.ProductID FOR XML PATH('Product'), TYPE ) FROM "Order Details" OrderDetails WHERE OrderDetails.OrderID = Orders.OrderID FOR XML PATH('OrderDetail'), TYPE ), ( SELECT Shippers.ShipperID, Shippers.CompanyName FROM Shippers WHERE Shippers.ShipperID = Orders.ShipVia FOR XML PATH('Shipper'), TYPE ) FROM Orders WHERE Orders.CustomerID = Customers.CustomerID FOR XML PATH('Order'), TYPE ) FROM Customers FOR XML PATH('Customer'), ROOT('xml'), TYPE
The question is not about receiving tables in XML, but about receiving all tables in just one call, w/ proper master-detail grouping (see attachment for result of the query). It is hard to imagine code doing the same w/ JOINs or cursors or on client-side. This makes FOR XML a perfect use-case in report generation services.
XSLT application servers would benefit greatly from FOR XML support: bespoke multiple tables can be fetched in one call, FOR XML produces results in XML format — just perfect for subsequent XSLT processing.