Обсуждение: Allow backend to output result sets in XML

Поиск
Список
Период
Сортировка

Allow backend to output result sets in XML

От
Brian Moore
Дата:
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


Re: Allow backend to output result sets in XML

От
Tom Lane
Дата:
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


Re: Allow backend to output result sets in XML

От
Peter Eisentraut
Дата:
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.



Re: Allow backend to output result sets in XML

От
Greg Stark
Дата:
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



Re: Allow backend to output result sets in XML

От
"Merlin Moncure"
Дата:
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


Re: Allow backend to output result sets in XML

От
"Joshua D. Drake"
Дата:
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>

Re: Allow backend to output result sets in XML

От
"Bort, Paul"
Дата:
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! 



Re: Allow backend to output result sets in XML

От
Andrew Dunstan
Дата:
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.
>
>
>
>  
>



Re: Allow backend to output result sets in XML

От
Peter Eisentraut
Дата:
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.



Re: Allow backend to output result sets in XML

От
"Merlin Moncure"
Дата:
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





Re: Allow backend to output result sets in XML

От
Scott Lamb
Дата:
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