Allow backend to output result sets in XML

Brian Moore <brianmooreca@yahoo.com> writes:

i would like to begin work on the TODO item
Allow backend to output result sets in XML

I am not sure why it's phrased that way --- surely the code to hack on
is the client side, not the backend. Otherwise you need a protocol
revision to make this happen, which implies hacking *both* ends.

psql already has some code to output results as HTML tables; I'd think
adding functionality in that vicinity would be the way to go.

regards, tom lane

Brian Moore wrote:

i would like to begin work on the TODO item
Allow backend to output result sets in XML

Implementing this on the client side seems cleaner (and is trivial to
implement). Some people have in fact already done that. Search the
archives.

Tom Lane <tgl@sss.pgh.pa.us> writes:

Brian Moore <brianmooreca@yahoo.com> writes:

i would like to begin work on the TODO item
Allow backend to output result sets in XML

I am not sure why it's phrased that way --- surely the code to hack on
is the client side, not the backend. Otherwise you need a protocol
revision to make this happen, which implies hacking *both* ends.

Presumably libpq would continue to use the binary protocol, but other clients
could bypass libpq and just stream ascii xml queries.

Personally I don't see any point in xml, but if there was a standard query
protocol then a client could send queries to any database that supported it
without using any libraries. That might be useful. Of course you could do that
without xml, but people seem to get more excited about complying with
standards when they invoke xml.

psql already has some code to output results as HTML tables; I'd think
adding functionality in that vicinity would be the way to go.

That could also be useful, mainly in that it could include the data from the
query, as well as some meta data. Allowing import tools for programs like
spreadsheets to do more intelligent things with the data than currently.

--
greg

Greg Stark wrote:

Personally I don't see any point in xml, but if there was a standard

query

protocol then a client could send queries to any database that

supported

it
without using any libraries. That might be useful. Of course you could

do

that
without xml, but people seem to get more excited about complying with
standards when they invoke xml.

hm. I have to deal with xml quite frequently because I do a lot of DX
with the gov't and other entities that are rapidly standardizing on xml.

I like Oracle's approach to xml using object relational mappings to
allow composition of documents server side based on natural data
relationships. The XML document becomes something like a specialized
view. It would save me tons of app-level coding if the server could do
this for me.

Since postgres is already fairly Oracle-ish in design, IMO this is
definitely the way to go (XQuery = Insanity.). A FE/BE protocol
revision would be useful but not necessary...the XML doc could be
returned as a scalar.

Right now I think all xml processing is done in app-level code, because
the server (due to limitations of sql) is usually unable to return data
the way you want it...so simply adding xml output from psql would be
fairly useless for most real tasks (if it wasn't, someone would have
done it a long time ago). Also, contrib\xml can already handle most of
the simple things.

Merlin

Greg Stark wrote:

Tom Lane <tgl@sss.pgh.pa.us> writes:

Brian Moore <brianmooreca@yahoo.com> writes:

i would like to begin work on the TODO item
Allow backend to output result sets in XML

I am not sure why it's phrased that way --- surely the code to hack on
is the client side, not the backend. Otherwise you need a protocol
revision to make this happen, which implies hacking *both* ends.

Presumably libpq would continue to use the binary protocol, but other clients
could bypass libpq and just stream ascii xml queries.

I would think that you would still use libpq with the binary protocol
that understood an xml header request
of some sort??

J

Personally I don't see any point in xml, but if there was a standard query
protocol then a client could send queries to any database that supported it
without using any libraries. That might be useful. Of course you could do that
without xml, but people seem to get more excited about complying with
standards when they invoke xml.

psql already has some code to output results as HTML tables; I'd think
adding functionality in that vicinity would be the way to go.

That could also be useful, mainly in that it could include the data from the
query, as well as some meta data. Allowing import tools for programs like
spreadsheets to do more intelligent things with the data than currently.

-- 
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com
PostgreSQL Replicator -- production quality replication for PostgreSQL

Please forgive me if this is silly, but if you wanted XML from the server,
couldn't you just write a PL/Perl untrusted function that takes a SELECT
statement as its parameter, and returns a single scalar containing the XML?
- The XML:: modules in Perl help with the XML formatting
- DBD::PgSPI could be handed the query as-is
- No change to BE/FE or wire protocols
- No impact on people who don't want it
- Probably works across versions with minimal fuss

Returning a simple XML structure with column names and rows should only take
a few lines. (I'd write an example if I knew XML:: better.)

I'll go back to lurking now. Thanks all for the great database!

There is apparently a standard (or at least a draft) on using XML with
SQL that can be seen here: http://www.wiscorp.com/sql/sql_2003_standard.zip

I have no idea if it is of great use - I found it a fairly opaque
document to read. It's a pity that unlike the document on SQL/JRT they
didn't provide a sample + tutorial appendix.

Oracle has a few examples here:
http://otn.oracle.com/tech/xml/xmldb/htdocs/sql_xml_codeexamples.html

IBM has some research info here:
http://www.research.ibm.com/journal/sj/414/reinwald.pdf

cheers

andrew

Merlin Moncure wrote:

Tom Lane wrote:

Brian Moore <brianmooreca@yahoo.com> writes:

i would like to begin work on the TODO item
Allow backend to output result sets in XML

I am not sure why it's phrased that way --- surely the code to hack
on is the client side, not the backend. Otherwise you need a
protocol revision to make this happen, which implies hacking *both*
ends.

psql already has some code to output results as HTML tables; I'd
think adding functionality in that vicinity would be the way to go.

Converting a libpq result set (or a JDBC result set or ...) to an XML
document should be a trivial string concatenation job that anyone can
implement in half an hour. The more interesting questions are: what
XML schema do you want to use and why? What do you want to do with the
XML in the first place? Would a streaming interface be a better? Do
you want a text document or a preparsed structure? What good would a,
say, libpq implementation be if it's more work to make a wrapper in one
of the other language bindings than implement it from scratch there?

I think "output XML" is just buzz. Give us a real use scenario and an
indication that a majority also has that use scenario (vs. the other
ones listed above), then we can talk.

Peter Eisentraut wrote:

I think "output XML" is just buzz. Give us a real use scenario and an
indication that a majority also has that use scenario (vs. the other
ones listed above), then we can talk.

Consider:

create table person (name varchar primary key, age int);
create table account (number varchar primary key, name varchar
references person);
insert into person values ('Fred', 35);
insert into person values ('Barney', 37);
insert into account values ('1234', 'Fred');
insert into account values ('5678', 'Fred');
insert into account values ('abcd', 'Barney');

test=# select * from person into xml natural;
<?xml version="1.0" encoding="UTF-8" ?>
<result>
<row n="1">
<person>
<name>Fred</name>
<age>35</age>
<account>
<number>1234</number>
</account>
<account>
<number>5678</number>
</account>
</person>
</row>
</result>
<row n="2">
[...]

now consider:
select * from person into xml natural
namespace is 'some_uri'
schema is 'person.xsd';

this returns result set above, but with schema and namespace
declarations included. Of course, there is tons of complexity hiding in
there, but is this worth considering?

Merlin

On Jan 21, 2004, at 12:19 PM, Peter Eisentraut wrote:

I think "output XML" is just buzz. Give us a real use scenario and an
indication that a majority also has that use scenario (vs. the other
ones listed above), then we can talk.

I do this all the time.

I have JDBC code to take a java.sql.ResultSet and push out SAX events
in a standard schema. I also have a XSLT stylesheet that formats them
in a decent way. In this manner, it's very easy for me to make
database-driven webpages. I can inherit the "normal" stylesheet and
then just code the exceptions.

However, I'm quite happy doing this on the client side. I'm not sure
why it would be beneficial to do this as part of the PostgreSQL server.

Scott Lamb