Thread: Allow backend to output result sets in XML
hello, i would like to begin work on the TODO item Allow backend to output result sets in XML i would like to know if anyone has already begun work on this item. if someone has already started work, i would love to help! thanks much in advance, b __________________________________ Do you Yahoo!? Yahoo! Hotjobs: Enter the "Signing Bonus" Sweepstakes http://hotjobs.sweepstakes.yahoo.com/signingbonus
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:<br /><blockquote cite="mid87fze9z7ki.fsf@stark.xeocode.com" type="cite"><pre wrap="">Tom Lane <a class="moz-txt-link-rfc2396E"href="mailto:tgl@sss.pgh.pa.us"><tgl@sss.pgh.pa.us></a> writes: </pre><blockquote type="cite"><pre wrap="">Brian Moore <a class="moz-txt-link-rfc2396E" href="mailto:brianmooreca@yahoo.com"><brianmooreca@yahoo.com></a>writes: </pre><blockquote type="cite"><pre wrap="">iwould like to begin work on the TODO item Allow backend to output result sets in XML </pre></blockquote><prewrap="">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. </pre></blockquote><pre wrap=""> Presumably libpq would continue to use the binary protocol, but other clients could bypass libpq and just stream ascii xml queries. </pre></blockquote> I would think that you would still use libpq withthe binary protocol that understood an xml header request<br /> of some sort??<br /><br /> J<br /><br /><br /><br /><blockquotecite="mid87fze9z7ki.fsf@stark.xeocode.com" type="cite"><pre wrap=""> 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. </pre><blockquote type="cite"><pre wrap="">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. </pre></blockquote><pre wrap=""> 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. </pre></blockquote><br /><br /><pre class="moz-signature" cols="72">-- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - <a class="moz-txt-link-abbreviated" href="mailto:jd@commandprompt.com">jd@commandprompt.com</a> - <a class="moz-txt-link-freetext"href="http://www.commandprompt.com">http://www.commandprompt.com</a> PostgreSQL Replicator -- production quality replication for PostgreSQL</pre>
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: >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. > > > > >
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