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.calco@verizon.net]
> Sent: Wednesday, March 05, 2003 9:10 AM
> To: Merlin Moncure; Alan Gutierrez
> Cc: pgsql-hackers@postgresql.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@postgresql.org
> %% [mailto:pgsql-hackers-owner@postgresql.org]On Behalf Of Merlin
Moncure
> %% Sent: Wednesday, March 05, 2003 8:16 AM
> %% To: Alan Gutierrez
> %% Cc: pgsql-hackers@postgresql.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@postgresql.org so that your
> %% message can get through to the mailing list cleanly
> 


Wed, 5 Mar 2003 10:18:32 -0500
"Merlin Moncure" <merlin.moncure@rcsonline.com>
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@postgresql.org
%% [mailto:pgsql-hackers-owner@postgresql.org]On Behalf Of Merlin Moncure
%% Sent: Wednesday, March 05, 2003 10:19 AM
%% To: robert.calco@verizon.net
%% Cc: pgsql-hackers@postgresql.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.calco@verizon.net]
%% > Sent: Wednesday, March 05, 2003 9:10 AM
%% > To: Merlin Moncure; Alan Gutierrez
%% > Cc: pgsql-hackers@postgresql.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@postgresql.org
%% > %% [mailto:pgsql-hackers-owner@postgresql.org]On Behalf Of Merlin
%% Moncure
%% > %% Sent: Wednesday, March 05, 2003 8:16 AM
%% > %% To: Alan Gutierrez
%% > %% Cc: pgsql-hackers@postgresql.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@postgresql.org so that your
%% > %% message can get through to the mailing list cleanly
%% >
%%
%%
%% ---------------------------(end of broadcast)---------------------------
%% TIP 4: Don't 'kill -9' the postmaster
%%



Wed, 5 Mar 2003 19:05:37 -0500
"Bob Calco" <robert.calco@verizon.net>
Re: XML ouput for psql
> 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.

Um, why change the backend at all?  Why not have libpq do the
interference mapping between the front end and backend so that we can
leave the backend alone?  Seems like a simple application of a good
SAX parser to me.  -sc

-- 
Sean Chittenden

Wed, 5 Mar 2003 16:50:49 -0800
Sean Chittenden <sean@chittenden.org>
Re: XML ouput for psql
If it turns out to be that simple, great - my first goal would be not to
touch the backend at all. But I suspect there are some significant semantic
issues lurking in the spec that may make that goal unattainable. In any
event, I agree with the impulse to change nothing in the backend unless
absolutely necessary to do it right - and only then if we determine upon
careful consideration that it really is worth doing. I *think* it *might* be
well worth it - but only real effort will yield a definitive answer.

- Bob Calco

%% -----Original Message-----
%% From: pgsql-hackers-owner@postgresql.org
%% [mailto:pgsql-hackers-owner@postgresql.org]On Behalf Of Sean Chittenden
%% Sent: Wednesday, March 05, 2003 7:51 PM
%% To: Bob Calco
%% Cc: Merlin Moncure; pgsql-hackers@postgresql.org
%% Subject: Re: [HACKERS] XML ouput for psql
%%
%%
%% > 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.
%%
%% Um, why change the backend at all?  Why not have libpq do the
%% interference mapping between the front end and backend so that we can
%% leave the backend alone?  Seems like a simple application of a good
%% SAX parser to me.  -sc
%%
%% --
%% Sean Chittenden
%%
%% ---------------------------(end of broadcast)---------------------------
%% TIP 4: Don't 'kill -9' the postmaster
%%



Wed, 5 Mar 2003 20:58:35 -0500
"Bob Calco" <robert.calco@verizon.net>
Re: XML ouput for psql
* Merlin Moncure <merlin.moncure@rcsonline.com> [2003-03-05 10:03]:
> 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.


I suppose this was in response to my comments, hard to tell with all
the top posting...

I am not advocating adding XML storage to the PostgreSQL backend.
XML is just a stupid little tag language. PostgreSQL is so much
more.

I adopted XML in my application to generated HTML via XSLT in
Internet Explorer. XSLT is very useful when the interface is
expressed in HTML. It has been as good a format as any for
transmitting documents and storing them on file system. 

Yes, mapping XML to SQL requires developer intervention. The little
program I wrote makes it very simple to express the mapping from and
XML element to db table.

It seems like it might be a useful add on. Maybe it could be kept
with the back end, and written in C.

Getting the information out PostgreSQL has been simple, I've used
Perl. Getting information back in has been simple with my utility
which will check if the element is already stored as a row, and
update, otherwise it will insert. It can also delete rows based on
the document and keep track of placeholder values used by the client
program to represent SERIAL values in database.

What I've got is a system where I post an XML document to a URL and
it gets mapped out to my normalized PostgreSQL schema.

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

Wed, 5 Mar 2003 23:53:47 -0600
Alan Gutierrez <ajglist@izzy.net>
Re: XML ouput for psql