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 also 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.