Re: XML ouput for psql

* greg@turnstep.com <greg@turnstep.com> [2003-03-04 14:21]:
> 
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1

> > The XML standard does not call for any table format.  But a
> > number of table formats have been established within the XML
> > framework.  Some of them are formatting-oriented (e.g., the HTML
> > model, or CALS which is used in DocBook) and some of them are
> > processing-oriented (e.g., SQL/XML).  Which do we need?  And
> > which do we need from psql in particular (keeping in mind that
> > psql is primarily for interactive use and shell-scripting)?  In
> > any case, it should most likely be a standard table model and
> > not a hand-crafted one.
>  
> I think all psql needs is a simple output, similar to the ones used by 
> Oracle, Sybase, and MySQL; the calling application should then process 
> it in some way as needed (obviously this is not for interactive use).
> Where can one find a "standard table model?"
> 
> All of the DBs I mentioned (and the perl module DBIx:XML_RDB) all share 
> a similar theme, with subtle differences (i.e. some use <row>, some 
> <row num="x">, some have <rowset>). I'd be happy to write whatever 
> format we can find or develop. My personal vote is the DBIx::XML_RDB 
> format, perhaps with the row number that Oracle uses, producing this:
> 
> <?xml version="1.0"?>
> <RESULTSET statement="select * from xmltest">
> <ROW num="1">
>  <scoops>3</scoops>
>  <flavor>chocolate</flavor>
> </ROW>
> <ROW num="2">
>  <scoops>2</scoops>
>  <flavor>vanilla</flavor>
> </ROW>
> </RESULTSET>
> 
> 
> > (If, for whatever reason, we go the "processing-oriented" route, then I
> > claim that there should not be a different output with and without \x
> > mode.)
> 
> I agree with this.

I'm interested in creating XML documents that have heirarcy.
I can produce the above with Perl.

I wrote a utility that takes an xml document, and xml configuration
file, and writes the document to a PostgerSQL data base using the
configuration file to figure out what goes where. The configuration
file makes some use of XPath to pluck the correct values out of the
xml doucment.

I suppose the same code could generate a document, but it is so easy
to do using Perl and cgi, I've not bothered.

This util has been very helpful to me in developing a document
mangement application. Rather than writing insert/update logic every
time the db or xml schema changes, I just tweak the config file and
it will generated the inserts, updates, and deletes by comparing the
XML document with the tables to which the XML elements are mapped.

I've been able to handle tree structures tolerably well.

I am currently rewriting the code in C++ from Perl.

-- 
Alan Gutierrez - ajglist@izzy.net
http://khtml-win32.sourceforge.net/ - KHTML on Windows