Recently I needed to create XML straight from a relational database.
The use case was to create an import file for Wordpress (WRX)
without writing any line of Python or Ruby code.
The legacy blogging application was using a PostgreSQL database
that holds: users/authors, articles, comments and some other relations.
It's not the intention to describe the whole process here.
Much more, it attempts to make it adaptable to other situations.
Importing users/authors (no aggregation)
The easiest case was the users table without any relations and no need for
aggregation. The goal was to create XML elements like this:
This can be done with this select statement:
The xmlelement expression produces an XML element with the name wp:author.
Then instead of nesting six other xmlelement statements (for id, username,
...), it was easier (and even more readable) to make use of the xmlforest
expression and create a sequence of the given elements.
Articles and comments (covering XML aggregation)
The goal was to create XML elements like this. An item can have none or
It is necessary to use a LEFT JOIN on comments and
GROUP BY p.id (ID of the article) to also include articles without comments.
To make it clear - the second column (count) of the following select statement can be zero:
Basically, the first part of the final statement is identical to the first
example. The xmlelement expression creates the item node and the
xmlforest expression creates it's children.
The xmlagg expression expects an XML element at the first argument.
It just concatenates the elements created by the xmlelement expression
and passes it to the aggregation call.
I had some unexpected results when using constants like '1' or '0' within
the xmlagg functions. The following node was generated, even if an
article does not have any comments.
The solution was to express '0' and '1' by using the comment's ID: