The XML is generated manually through statements such as:

    SELECT
        xmlroot(
          xmlelement( name root,
          xmlelement( name description,
            xmlelement( name title, table.title ),
            xmlelement( name diet, table.diet ) ), etc.

If the format of the XML was out of the developer's control, XSL is a relatively easy way to convert XML from one format to another. If XSL won't handle it, then any number of ETL tools would be more than sufficient. Unless you mean something else by "flavour of XML"?

To get the same document using a language external to the database requires the following steps:

  1. Write the SQL statement (a stored procedure, view, or string).
  2. Instantiate an XML document library (e.g., PHP's DOM).
  3. Iterate over the result set(s).
  4. Build the XML document from the results.

Note that the first step is always required in both situations (whether the query is internal or external to the database).

Steps 2 to 4 effectively echo the first step: they tightly couple the XML format to the expected result set(s) from the database query. There is no abstraction to the application, there is no visible gain.

Also, with XML you can add meta information to the element: <date format="dd-MMM-yyyy">02-FEB-2012</date>. In PostgreSQL, you would use the xmlattribute function.