SQL to XML

So, I tried to investigate how one can get XML from relational data on web/data server side. Here are ways:

• you can use Oracle, but you should write constructions like this:

SELECT XMLELEMENT( "Star", XMLFOREST(s.name, s.x, s.y, s.z, s.type), XMLAGG(XMLELEMENT("planet",XMLFOREST(p.id, p.size, p.type))))

AS result

FROM (star s INNER JOIN (planet_owner po INNER JOIN planet p ON po.planet_id = p.id) ON s.name = p.star) INNER JOIN star_explored se ON s.name = se.name

WHERE se.empire="empire"

GROUP BY s.name

• you can use MS SQL and you should write constructions like this:

SELECT s.name, s.x, s.y, s.z, s.type, p.id, p.size, p.type

FROM star s, planet p, star_explored se

WHERE s.name = p.star

AND s.name = se.name

AND se.empire="empire"

FOR XML AUTO

So, if your page code can render data in XML format, which it can get by HTTP, as Backbase can - that examples suits you pretty well. But... the problem is that these solutions are proprietary. So ...

• you CAN use MySQL, but here problems start:

MySQL itself has no native support for XML - so all you can do - use scripts/programs to transform SQL query to several queries, then get results, parse it and write XML, send it to browser. It seems that problem of "SQL-XML transformation" is quite repeatable and there should be several solutions ou there in Internet - NO, there is no suitable solutions. There is some libs that seems to fit the problem, but there function differs a bit from our task. So, our choise is:

Perl module - DBIx-DBStag-0.07 - as you see an early, very early alpha.