SQL recordset -> XML

[Ian Bicking]

On Monday, October 27, 2003, at 05:00 PM, Richard Shea wrote:

> Hi - Not sure if this is a really stupid question or not ... so I
> thought I'd ask it anyway ;-)
>
> I want to take data from a SQL generated recordset and represent it as
> XML.
>
> I can think of *very* straightforward ways (as I'm sure all you can)
> to do that (please excuse rough/weird pseudo code) ...
>
> if you had a recordset resulting from a query something like :
>
> select
> Suburb_Value,City_Value
> from
> Table_Locations
> ORDER BY CITY_VALUE,SUBURB_VALUE
>
> ... and you then did something like this (only a lot tidier) ...
>
> do while no_more_records
>     if Suburb_Value <> CurrentSuburb
>         output Closing_City_Tag
>         output Opening_City_Tag(City_Value)
>     end if
>     output Opening_Suburb_Tag
>     output Suburb_Value
>     output Closing_Suburb_Tag
> end do
>
> ... you'd get what I'm after but it's all a bit clunky and of course
> very specific the current recordset.

There are several libraries for outputting XML more easily -- but I 
haven't tried them, so I can't really even reference one, not to 
mention recommend one.

But for being more general with the result set you can use the 
description attribute of cursors.  So something like (untested, refer 
to the DB-API spec on the python.org site for more information):

# get a cursor from a DB-API connection, then:
cursor.execute("SELECT suburb_value, city_value from table_locations 
order by city_value, suburb_value")
desc = cursor.description
xml = StringIO()
xml.write('<rowset>')
for row in cursor.fetchall():
     for i in range(len(desc)):
         name = desc[i]
         col = row[i]
         xml.write('<%s>%s</%s>' % (name, col, name))
xml.write('</rowset>')
xml = xml.getvalue()


There's all sorts of quoting problems that will come up with this naive 
implementation, so I'd recommend using a real XML-producing library.

--
Ian Bicking | ianb at colorstudy.com | http://blog.ianbicking.org