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,Ci ty_Value
from
Table_Locations
ORDER BY CITY_VALUE,SUBU RB_VALUE

.... and you then did something like this (only a lot tidier) ...

do while no_more_records
if Suburb_Value <> CurrentSuburb
output Closing_City_Ta g
output Opening_City_Ta g(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]

Re: SQL recordset -&gt; XML

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

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


Best,
Miklós

--
PRISZNYÁK Miklós
---
Jegenye 2001 Bt. ( mailto:jegenye2 001@parkhosting .com )
Egyedi szoftverkészíté s, tanácsadás
Custom software development, consulting

http://jegenye2001.parkhosting.com

Richard Shea <richardshea@fa stmail.fm> wrote in message
news:282f826a.0 310271500.62fe7 a5a@posting.goo gle.com...[color=blue]
> 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,Ci ty_Value
> from
> Table_Locations
> ORDER BY CITY_VALUE,SUBU RB_VALUE
>
> ... and you then did something like this (only a lot tidier) ...
>
> do while no_more_records
> if Suburb_Value <> CurrentSuburb
> output Closing_City_Ta g
> output Opening_City_Ta g(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.[/color]

[Hung Jung Lu]

Re: SQL recordset -&gt; XML

richardshea@fas tmail.fm (Richard Shea) wrote in message news:<282f826a. 0310271500.62fe 7a5a@posting.go ogle.com>...[color=blue]
>
> I want to take data from a SQL generated recordset and represent it as
> XML.[/color]

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]

Re: SQL recordset -&gt; XML

"Richard Shea" <richardshea@fa stmail.fm> wrote in message
news:282f826a.0 310271500.62fe7 a5a@posting.goo gle.com...
....[color=blue]
> 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.[/color]
....[color=blue]
> richard shea.[/color]


I used xml.sax.saxutil s.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.
--
Francis Avila

[Andy Todd]

Re: SQL recordset -&gt; XML

Richard Shea wrote:[color=blue]
> 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) ...
>[/color]
[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
--
--------------------------------------------------------------------------------
From the desk of Andrew J Todd esq - http://www.halfcooked.com/