[XML-SIG] How to convert my data in database to XML

[Dave Kuhlman]

On Tue, Nov 02, 2004 at 09:21:41AM -0800, kumar s wrote:
> Dear group, 
>  I apologize for asking a very basic question here and
> also I am afraid if this is not the correct place to
> ask. 
> 
> I have a database and I am plannig to pump my data in
> the tables into XML files. For the kind of data in my
> database table, there is already a proposed DTD out
> there.  
> 
> What I should be do in order to convert my existing
> data in tables into XML files. 

Your task sounds very straight-forward.  You need to write a Python
application that reads your table, and writes data formatted as XML
to a file.  In Python, that is easy to do.  I have included a
simple example at the end of this email.  This example reads a
table in a PostgreSQL database using psycopg - Python-PostgreSQL
Database Adapter (http://initd.org/Software/).  If you do not
already have the database adapter you need for your database, you
can learn about others at: http://www.python.org/sigs/db-sig/.


> 
> I might not have all the data fields supported in the
> DTD in my database. Is it okay if I leave some fields
> as blank. 
> 

Maybe.  You might (1) include the tags with no data or (2) include
the tags with default values that you want to initialize or (3)
omit the tags altogether.  This depends on your needs and on the
needs of the application that will consume the XML file you
produce.

Hope the helps.

Dave

Here is the trivial example:

    import psycopg

    CONNECT_ARGS = 'host=localhost user=postgres1 password=mypassword dbname=test'

    def exportPlants(outfileName):
        outfile = file(outfileName, 'w')
        connection = psycopg.connect(CONNECT_ARGS)
        cursor = connection.cursor()
        cursor.execute("select * from Plant_DB order by p_name")
        rows = cursor.fetchall()
        outfile.write('<?xml version="1.0" ?>\n')
        outfile.write('<mydata>\n')
        for row in rows:
            outfile.write('  <row>\n')
            outfile.write('    <name>%s</name>\n' % row[0])
            outfile.write('    <desc>%s</desc>\n' % row[1])
            outfile.write('    <rating>%s</rating>\n' % row[2])
            outfile.write('  </row>\n')
        outfile.write('</mydata>\n')
        outfile.close()

And, here is sample output:

    <?xml version="1.0" ?>
    <mydata>
      <row>
        <name>almonds</name>
        <desc>good nuts</desc>
        <rating>4</rating>
      </row>
      <row>
        <name>apricot</name>
        <desc>sweet fruit</desc>
        <rating>4</rating>
      </row>
      <row>
        <name>arugula</name>
        <desc>heavy taste</desc>
        <rating>4</rating>
      </row>
      <row>
        <name>chard</name>
        <desc>leafy green</desc>
        <rating>4</rating>
      </row>
    </mydata>


-- 
Dave Kuhlman
http://www.rexx.com/~dkuhlman