XML from SQL result

[=?iso-8859-2?q?Marcin_St=EApnicki?=]

Hello.

I've skimmed through many Python&XML related books/articles but I am
unable to find anything that is similar to my problem - but it seems to
me that it should be common.

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.

2) Provided that I can't generate it from 1) should I take a deeper look
into Gnosis Utils? http://www.gnosis.cx/download/Gnosis_Utils.More/ ?

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

Could you please clarify these for me? I'm a bit puzzled and frankly
don't know where to start. Even a single URL which explains it in detail is
greatly anticipated .

Thank you for your time,
Marcin

 

[Marc 'BlackJack' Rintsch]

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