Re: XML ouput for psql

* greg(at)turnstep(dot)com <greg(at)turnstep(dot)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.

Acually, the difficult part has been getting the information back
into the database. Getting it out is a very simple query. I imagine
that every language/environment has an SQL->XML library somewhere,
but I wasn't able to find something that would go from XML to SQL.

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.

It has some constraints, but it is a very useful utility. I've been
able to abosorb XML documents into my PostgreSQL db just by tweeking
the configuration file.

Currently, I am porting it to C++ from Perl.

--
Alan Gutierrez - ajglist(at)izzy(dot)net
http://khtml-win32.sourceforge.net/ - KHTML on Windows