Re: XML ouput for psql

Thanks, Merlin, for your thoughtful comments. There is no question that the
marriage of XML and SQL is wrought with semantic difficulties. I'm not sure
the win is compelling enough to justify re-writing the PostgreSQL back end;
on the other hand, it is a juicy technical challenge!

I like PostgreSQL just as it is, and truth be told, I'd like to see some
additional features in PL/pgSQL that are completely unrelated to the whole
XML issue. But I see some interesting possibilities for PostgreSQL to make
inroads in enterprise development if it were the first open source database
to do something truly useful with XQuery concepts.

So I had this idea. If I can find a few good co-conspirators, I'm prepared
to get involved seriously with developing this add-on capability, whatever
it takes. Mostly, I suspect, time...

Sincerely,

Bob Calco

%% -----Original Message-----
%% From: pgsql-hackers-owner(at)postgresql(dot)org
%% [mailto:pgsql-hackers-owner(at)postgresql(dot)org]On Behalf Of Merlin Moncure
%% Sent: Wednesday, March 05, 2003 10:19 AM
%% To: robert(dot)calco(at)verizon(dot)net
%% Cc: pgsql-hackers(at)postgresql(dot)org
%% Subject: Re: [HACKERS] XML ouput for psql
%%
%%
%% This is just about a total conversion of the backend to an xml document
%% server. The marriage of xml and sql is awkward and not easily
%% retrofitted to existing databases.
%%
%% Its pretty much proven that hierarchal storage techniques (xml included)
%% are more difficult to manage and use than traditional sql databases.
%% However, xml does have some very powerful supplemental technologies for
%% document generation on the client end, especially xslt. Unless there
%% is a compelling reason to use those tools, you are 99% likely better off
%% not using xml at all. XML has also found a niche in the edi world, but
%% in this case you can get away with using the blob technique below.
%%
%% Before thinking about xquery you have to think about the role tuples and
%% relations play in storing xml data. The simplest route is to store your
%% xml data in a blob object and use little tricks like xslt to extract
%% elements out of the document into separate fields for index purposes and
%% use vanilla sql queries to get the data. This is a very loose coupling
%% of sql and xml and leads to very brittle designs because at the very
%% least you have to store two separate definitions of your data as well as
%% your marshalling xslt scripts.
%%
%% I wrote a little blob based xml server which you can see here if you are
%% interested:
%% http://www.radiosoft.com/freetools.php?Location=Directional+Patterns
%% The real magic was in the xslt which you can't see, though.
%%
%%
%% A tighter coupling would involve decomposing your xml structure into
%% discrete objects and building xml power into the backend. I think it is
%% yet to be proven if this is even reasonably possible. The big problem
%% with xml is that there is too many ways to do things, for example the
%% choice of dtd or schemas. I think, the problem with this approach is
%% recomposing your document involves complex or inefficient queries. If
%% you think this is good idea, good luck, many companies have tried and
%% I've personally not seen one that seems to work very well. The next
%% major version of MS SQL server is rumored to be something of an xml
%% document server, and that's been several years in development.
%%
%% Merlin
%%
%% > -----Original Message-----
%% > From: Bob Calco [mailto:robert(dot)calco(at)verizon(dot)net]
%% > Sent: Wednesday, March 05, 2003 9:10 AM
%% > To: Merlin Moncure; Alan Gutierrez
%% > Cc: pgsql-hackers(at)postgresql(dot)org
%% > Subject: RE: [HACKERS] XML ouput for psql
%% >
%% > I would like to see PostgreSQL eventually support XQuery:
%% >
%% > http://www.w3.org/TR/xquery/
%% > http://www.w3.org/TR/query-datamodel/
%% >
%% > I see potentially an alternative front end called xsql, providing
%% > substantially the same functionality as psql, only using XQuery syntax
%% and
%% > optionally returning recordsets as XML.
%% >
%% > Anybody want to put together a team to explore this seriously? There
%% are
%% > probably several non-trivial semantic issues on the back end, but I
%% only
%% > dimly grasp them at this point.
%% >
%% > - Bob Calco
%% >
%% > %% -----Original Message-----
%% > %% From: pgsql-hackers-owner(at)postgresql(dot)org
%% > %% [mailto:pgsql-hackers-owner(at)postgresql(dot)org]On Behalf Of Merlin
%% Moncure
%% > %% Sent: Wednesday, March 05, 2003 8:16 AM
%% > %% To: Alan Gutierrez
%% > %% Cc: pgsql-hackers(at)postgresql(dot)org
%% > %% Subject: Re: [HACKERS] XML ouput for psql
%% > %%
%% > %%
%% > %% XSLT could be used to convert virtually any xml table format
%% directly
%% > %% into an insert statement. For me, this is better than using a
%% > %% programming language plus a parser. XSLT is quite powerful and
%% fast
%% > and
%% > %% is build on top of xpath, and is a closer fit to the declarative
%% > %% programming model of sql. Validation could be done at the xslt
%% stage
%% > or
%% > %% with schemas, which I prefer.
%% > %%
%% > %%
%% > %% > 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.
%% > %% >
%% > %%
%% > %% ---------------------------(end of
%% broadcast)--------------------------
%% > -
%% > %% TIP 3: if posting/reading through Usenet, please send an
%% appropriate
%% > %% subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
%% > %% message can get through to the mailing list cleanly
%% >
%%
%%
%% ---------------------------(end of broadcast)---------------------------
%% TIP 4: Don't 'kill -9' the postmaster
%%