GerixSoft

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.