XML from SQL result

[Marc 'BlackJack' Rintsch]

In <f5t3cs$ebq$1 at news.task.gda.pl>, Marcin Stępnicki wrote:

> Anyway: I've got the SQL query which returns:
> 
> col1 | col2 | col3
> -----+------+-----
>   a  | a10  | b20
>   a  | a10  | b30
>   a  | a20  | b30
> 
> I need to generate the following:
> 
> <tag1 "col1"="a">
> 	<tag2 "col2"="a10">
> 		<tag3 "col3"="b20" />
> 		<tag3 "col3"="b30" />
> 	</tag2>
> 
> 	<tag2 "col2"="a20">
> 		<tag3 "col3"="b30" />
> 	</tag2>
> </tag1>  
> 
> Using Elementree it's not a problem when returned data is "flat", not
> hierarchical. However, that's not the case.
> 
> Questions:
> 
> 1) (I'll ask that on PostgreSQL group too) I use PostgreSQL. I understand
> that in order to achieve what I want directly from RDBMS I need SQL/XML
> language support which will be included in 8.3 which is not yet available.

That question sounds more like a statement.  :-)

> 3) I can generate series of select queries based on previous queries, but
> I can't imagine there isn't a better solution.

If the data can be queried sorted like shown above, should be possible to
iterate over it and use `itertools.groupby` and
`elementtree.SimpleXMLWriter.XMLWriter` to generate the XML on the fly:

import sys
from itertools import groupby
from operator import itemgetter
from elementtree.SimpleXMLWriter import XMLWriter

first, second, third = map(itemgetter, xrange(3))

def main():
    data = (('a', 'a10', 'b20'),
            ('a', 'a10', 'b30'),
            ('a', 'a20', 'b30'))
    
    writer = XMLWriter(sys.stdout)
    writer.start('document')
    for value, rows in groupby(data, first):
        writer.start('tag1', col1=value)
        for value, rows in groupby(rows, second):
            writer.start('tag2', col2=value)
            for value in imap(third, rows):
                writer.element('tag3', col3=value)
            writer.end('tag2')
        writer.end('tag1')
    writer.end('document')

Ciao,
	Marc 'BlackJack' Rintsch