SQL recordset -> XML

[Richard Shea]

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.

I just wondered if anyone had written anything to at least help this
process a bit ?

I'm in the process of reading the O'Reilly "XML and Python" book and
there's any amount of support for 'input XML' but nothing really to
assist with output.

Is there a smart way to do this ?

thanks

richard shea.

 

[Jegenye 2001 Bt]

Once I saw something like this...
Sorry can't remember where and when..

They advocated a very simple shallow representation like this:
<row><field1>data</field1>etc..</row>
and there was even an acronym for that.


Best,
Miklós

 

[Hung Jung Lu]

I want to take data from a SQL generated recordset and represent it as
XML.

Since you mention recordset and XML, you may already be a Microsoft
convert, so why not just use ADO.NET?

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dndive/html/data12132001.asp

Hung Jung

 

[Francis Avila]

....

I'm in the process of reading the O'Reilly "XML and Python" book and
there's any amount of support for 'input XML' but nothing really to
assist with output. ....
richard shea.

I used xml.sax.saxutils.XMLGenerator for a very simple text->xml conversion.
XMLGenerator is like an inverse SAX: give it sax events (python method
calls), output xml. It is staggeringly, shockingly poorly documented (even
worse than the rest of the xml.sax package, it seems), which means it's
probably a bad idea to depend on it for anything serious.

It's still not much more sophisticated than the pseudocode you outlined
above, but it takes care of grimy xml character-level details. It won't
verify against a DTD or even make sure you don't overlap your elements.
(Although the latter could probably be added fairly easily.)

Basic outline of using XMLGenerator here:
http://www.xml.com/pub/a/2003/03/12/py-xml.html

Much more recent and varied coverage:
http://www.xml.com/pub/a/2003/10/15/py-xml.html

There's a little series of "Python and XML" articles over at xml.com, as
you'll see.

 

[Andy Todd]

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) ...

[snip]

You may also want to check out the Effbot's ElementTree as well
(http://effbot.org/zone/element-index.htm). It has a component called
the 'Simple XML Writer' which may fit your needs.

Regards,
Andy