XML ouput for psql

greg@turnstep.com kirjutas K, 26.02.2003 kell 22:46:

and with the \x option:

<?xml version="1.0" encoding="SQL_ASCII"?>
<resultset psql_version="7.4devel" query="select * from foo;">

<columns>
<col num="1">a</col>
<col num="2">b</col>
<col num="3">c</col>
<col num="4">mucho nacho </col>
</columns>
<row num="1">
<cell name="a">1</cell>
<cell name="b">pizza</cell>
<cell name="c">2003-02-25 15:19:22.169797</cell>
<cell name="mucho nacho "></cell>
</row>
<row num="2">
<cell name="a">2</cell>
<cell name="b">mushroom</cell>
<cell name="c">2003-02-25 15:19:26.969415</cell>
<cell name="mucho nacho "></cell>
</row>
</resultset>

The default encoding "SQL-ASCII" is not valid for XML.
Should it be automatically changed to something else?

I think you should force conversion to something standard, try using
automatic conversion to some known client encoding.

btw, "UNICODE" is also not any known encoding in XML, but PostgreSQL
uses it to mean utf-8

The flag "-X" is already taken, unfortunately, although \X is not.
I used "-L" and "\L" but they are not as memorable as "X". Anyone
see a way around this? Can we still use \X inside of psql?

It would be nice to include the string representation of the column
types in the xml output:
<col type="int8">foo</col>
....but I could not find an easy way to do this: PQftype returns the
OID only (which is close but not quite there). Is there an
existing way to get the name of the type of a column from a
PQresult item?

Run "select oid,typname from pg_type;" first if run in xml mode and
store the oid/columnname pairs.

you could also store the result in ~/.psql for faster access later on
and manually clear it if new types are defined

----------------
Hannu

greg@turnstep.com writes:

Patch to add XML output to psql:

This would get me more excited if you do one or both of the following:

1. Look into the SQL/XML standard draft (ftp.sqlstandards.org) to find out
whether the standard addresses this sort of thing.

2. Use an established/standardized XML (or SGML) table model rather than
rolling your own.

Incidentally, the HTML table model is such an established and standardized
XML and SGML table model, so the easiest way to get the task "add XML
output to psql" done is to update the HTML output to conform to XHTML.
That way you get both the strict XML and you can look at the formatted
result with any old (er, new) browser.

--
Peter Eisentraut peter_e@gmx.net

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hannu Krosing wrote:

I think you should force conversion to something standard, try using
automatic conversion to some known client encoding.

I've thought about this some more, and the only thing I can think
about doing without being too heavy-handed is to change the encoding
to "US-ASCII" whenever someone enters "XML" mode if the encoding is set
to "SQL-ASCII". Perhaps with a warning.

"The character set most commonly use in the Internet and used especially in
protocol standards is US-ASCII, this is strongly encouraged."
http://www.iana.org/assignments/character-sets

On the other hand, SQLX seems to lean toward a strict unicode encoding
(see my reply to Peter Eisentraut for more on that).

Run "select oid,typname from pg_type;" first if run in xml mode and
store the oid/columnname pairs.

I realize that I could run a SQL query against pg_type to grab the info,
but I was hoping there was an internal function similar to PQtype which
would return the information.

you could also store the result in ~/.psql for faster access
later on and manually clear it if new types are defined

Not only does pg_type has literally hundreds of entries, but there is no
way to guarantee that these are correct at the time when the query is
run, so I don't think this is viable.

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200302280938
-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html

iD8DBQE+X3hCvJuQZxSWSsgRArMTAKChouxnFF1ugI1mutXYJf14p1ICGwCfUDG9
yISxrIvqxnYWHfvD0lOWZAQ=
=M6nd
-----END PGP SIGNATURE-----

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Peter Eisentraut wrote:

1. Look into the SQL/XML standard draft (ftp.sqlstandards.org) to find out
whether the standard addresses this sort of thing.

The URL you gave leads to a site curiously content-free and full of dead links.
I've looked around a bit, but found nothing definitive. One good resource I
did find was this:

http://www.wiscorp.com/sql/SQLX_Bringing_SQL_and_XML_Together.pdf

The article mentions a lot of links on the sqlstandards.org and iso.org sites, none
of which work or are restricted. If anyone knows of some good links, please
let me know. (especially ISO 9075). From what I've read of the SQLX stuff, the
format in my patch should be mostly standard:

<row>
<name>Joe Sixpack</name>
<age>35</age>
<state>Alabama</state>
</row>

One problem is that the recommended way to handle non-standard characters
(including spaces) is to escape them like this:

foobar baz => <foobar_x0020_baz>

This also includes escaping things like "_x*" and "xml*". We don't have
anything like that in the code yet (?), but we should probably think about
heading that way. I think escaping whitespace in quotes is good enough
for now for:

foobar baz => <"foobar baz">

The xsd and xsi standards are also interesting, but needlessly complicated
for psql output, IMO.

Incidentally, the HTML table model is such an established and standardized
XML and SGML table model, so the easiest way to get the task "add XML
output to psql" done is to update the HTML output to conform to XHTML.
That way you get both the strict XML and you can look at the formatted
result with any old (er, new) browser.

I don't agree with this: XML and XHTML are two different things. We could
certainly upgrade the HTML portion, but I am pretty sure that the XML
standard calls for this format:

<columnname>data here</columnname>

..which is not valid XHTML and won't be viewable by any browser. The other
suggested XML formats are even further from XHTML than the above. The HTML
format should be "html table/layout" specific and the XML should be
"schema/data" specific.

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200302280938

-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html

iD8DBQE+X3k5vJuQZxSWSsgRAuXFAKDGO1IsjB9Lwtkcws1xJy47PibcLQCg3dx5
fsy27qguZv841lPvCjzdUic=
=4f9B
-----END PGP SIGNATURE-----

greg@turnstep.com writes:

I don't agree with this: XML and XHTML are two different things.

No one claimed anything to the contrary.

We could certainly upgrade the HTML portion, but I am pretty sure that
the XML standard calls for this format:

<columnname>data here</columnname>

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.

(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.)

--
Peter Eisentraut peter_e@gmx.net

greg@turnstep.com writes:

I don't agree with this: XML and XHTML are two different things.

No one claimed anything to the contrary.

We could certainly upgrade the HTML portion, but I am pretty sure that
the XML standard calls for this format:

<columnname>data here</columnname>

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 would expect XML output to be based on whatever the tree of data
contained.

If the tree is to be rewritten, then this would mean having some sort of
transformation engine in PostgreSQL that you would have to program.

If I want a CALS table, then I'll push CALS table data into the
database.

If I'm storing a GnuCash chart of accounts in PostgreSQL, I am
ludicrously uninterested in seeing it rewritten for some sort of
physical layout. Spit out the tags that are stored in the database, not
some rewriting of it.
--
(reverse (concatenate 'string "moc.enworbbc@" "enworbbc"))
http://cbbrowne.com/info/linuxdistributions.html
(1) Sigs are preceded by the "sigdashes" line, ie "\n-- \n" (dash-dash-space).
(2) Sigs contain at least the name and address of the sender in the first line.
(3) Sigs are at most four lines and at most eighty characters per line.

-----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.

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200303041444
-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html

iD8DBQE+ZQJNvJuQZxSWSsgRArGEAKD4xs+4Ns3syG175T3k80B6MvNJvgCbBkvF
hCkf5SMjLzMJ84uMl1w4tMY=
=a2Uq
-----END PGP SIGNATURE-----

* 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.

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@izzy.net
http://khtml-win32.sourceforge.net/ - KHTML on Windows

* 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

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.

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

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

I've done a lot with XML lately, so I'll throw in my $0.02 worth.

One thing I have noticed about the schemes that are being advanced is that
they seem to be inherently unspecifiable, formally, because column names are
being used as tags.

An alternative might look something like this:

<?xml version="1.0"?>
<RESULTSET statement="select * from xmltest">
<COLUMNS>
<COLUMN name="scoops" type="int" />
<COLUMN name="flavor" type="varchar(40)" />
</COLUMNS>
<ROW>
<FIELD name="scoops" isNull="false">3</FIELD>
<FIELD name="flavor" isNull="false">chocolate</FIELD>
</ROW>
<ROW>
<FIELD name="scoops" isNull="false">2</FIELD>
<FIELD name="flavor" isNull="false">vanilla</FIELD>
</ROW>
</RESULTSET>

Numbering the rows should be redundant (XPath will give it to you using
"position()", for example). OTOH, reporting out a null value as opposed to
an empty one is probably a good idea.

The formal DTD would be something like this (courtesy of the wonderful tools
at http://www.hitsw.com/xml_utilites/:

<!ELEMENT RESULTSET ( COLUMNS, ROW* ) >
<!ATTLIST RESULTSET statement CDATA #REQUIRED >
<!ELEMENT COLUMNS ( COLUMN+ ) >

<!ELEMENT COLUMN EMPTY >
<!ATTLIST COLUMN name NMTOKEN #REQUIRED >
<!ATTLIST COLUMN type CDATA #REQUIRED >

<!ELEMENT ROW ( FIELD+ ) ><!ELEMENT FIELD ( #PCDATA ) >
<!ATTLIST FIELD isNull ( false| true ) "false" >
<!ATTLIST FIELD name NMTOKEN #REQUIRED >
or the equivalent in a schema:<?xml version="1.0" encoding="UTF-8" ?>

<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema&quot;&gt;
<xs:element name="COLUMN">
<xs:complexType>
<xs:attribute name="type" type="xs:string" use="required" />
<xs:attribute name="name" type="xs:NMTOKEN" use="required" />
</xs:complexType>
</xs:element>

<xs:element name="COLUMNS">
<xs:complexType>
<xs:sequence>
<xs:element ref="COLUMN" minOccurs="1" maxOccurs="unbounded" />
</xs:sequence>
</xs:complexType>
</xs:element>

<xs:element name="FIELD">
<xs:complexType mixed="true">
<xs:attribute name="isNull" use="optional" default="false">
<xs:simpleType>
<xs:restriction base="xs:NMTOKEN">
<xs:enumeration value="false" />
<xs:enumeration value="true" />
</xs:restriction>
</xs:simpleType>
</xs:attribute>
<xs:attribute name="name" type="xs:NMTOKEN" use="required" />
</xs:complexType>
</xs:element>

<xs:element name="RESULTSET">
<xs:complexType>
<xs:sequence>
<xs:element ref="COLUMNS" minOccurs="1" maxOccurs="1" />
<xs:element ref="ROW" minOccurs="0" maxOccurs="unbounded" />
</xs:sequence>
<xs:attribute name="statement" type="xs:string" use="required" />
</xs:complexType>
</xs:element>

<xs:element name="ROW">
<xs:complexType>
<xs:sequence>
<xs:element ref="FIELD" minOccurs="1" maxOccurs="unbounded" />
</xs:sequence>
</xs:complexType>
</xs:element>

</xs:schema>

greg@turnstep.com writes:

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?"

I think for processing-oriented output, the system described in the
SQL/XML standard draft is the way to go. Considering the people who wrote
it, it's probably pulled from, or bound to appear in, a major commercial
database.

I also think that psql is not the place to implement something like this.
It's most likely best put in the backend, as a function like

xmlfoo('select * from t1;')

Then any interface and application that likes it, not just psql-based
ones, can use it.

--
Peter Eisentraut peter_e@gmx.net

Andrew Dunstan writes:

One thing I have noticed about the schemes that are being advanced is that
they seem to be inherently unspecifiable, formally, because column names are
being used as tags.

The SQL/XML draft addresses this by specifying that a mapping from SQL
things to XML things spits out both the specification (XML Schema, IIRC)
and the data in one operation.

--
Peter Eisentraut peter_e@gmx.net

Bob Calco writes:

I would like to see PostgreSQL eventually support XQuery:

The specification is here:

ftp://sqlstandards.org/SC32/WG3/Progression_Documents/FCD/4FCD1-14-XML-2002-03.txt

Go for it.

--
Peter Eisentraut peter_e@gmx.net

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
%%

Thanks for the link - I think I just may give it a go. :)

- Bob

%% -----Original Message-----
%% From: pgsql-hackers-owner@postgresql.org
%% [mailto:pgsql-hackers-owner@postgresql.org]On Behalf Of Peter Eisentraut
%% Sent: Wednesday, March 05, 2003 5:39 PM
%% To: Bob Calco
%% Cc: Merlin Moncure; Alan Gutierrez; pgsql-hackers@postgresql.org
%% Subject: Re: [HACKERS] XML ouput for psql
%%
%%
%% Bob Calco writes:
%%
%% > I would like to see PostgreSQL eventually support XQuery:
%%
%% The specification is here:
%%
%% ftp://sqlstandards.org/SC32/WG3/Progression_Documents/FCD/4FCD1-1
%% 4-XML-2002-03.txt
%%
%% Go for it.
%%
%% --
%% Peter Eisentraut peter_e@gmx.net
%%
%%
%% ---------------------------(end of broadcast)---------------------------
%% TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
%%

Peter Eisentraut <peter_e@gmx.net> writes:

I also think that psql is not the place to implement something like this.

Agreed.

It's most likely best put in the backend, as a function like
xmlfoo('select * from t1;')

That seems a little bizarre. Wouldn't we want to have a switch that
just flips the SELECT output format from one style to the other?

This is also a good time to stop and ask whether the frontend/backend
protocol needs to change to support this. Not having read the spec,
I have no idea what the low-level transport needs are for XML output,
but I suspect our present protocol is not it ...

regards, tom lane