Re: XML ouput for psql

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

Patch to add XML output to psql:

http://www.gtsm.com/xml.patch.txt

Notes and questions:

The basic output looks something like this:

<?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">
<a>1</a>
<b>pizza</b>
<c>2003-02-25 15:19:22.169797</c>
<"mucho nacho "></"mucho nacho ">
</row>
<row num="2">
<a>2</a>
<b>mushroom</b>
<c>2003-02-25 15:19:26.969415</c>
<"mucho nacho "></"mucho nacho ">
</row>
<footer>(2 rows)</footer>
</resultset>

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?

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?

The HTML, XML, and Latex modes should have better documentation -
I'll submit a separate doc patch when/if this gets finalized.

- --
Greg Sabino Mullane greg(at)turnstep(dot)com
PGP Key: 0x14964AC8 200302261518

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

iD8DBQE+XSR/vJuQZxSWSsgRAi2jAJ9IAKnMBmNcVEEI8TXQBBd/rtm4XQCg0Vjq
IO9OsCSkdnNJqnrYYutM3jw=
=9kwY
-----END PGP SIGNATURE-----

greg(at)turnstep(dot)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(at)turnstep(dot)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(at)gmx(dot)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(at)turnstep(dot)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(at)turnstep(dot)com
PGP Key: 0x14964AC8 200302280938

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

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

greg(at)turnstep(dot)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(at)gmx(dot)net

> greg(at)turnstep(dot)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(at)turnstep(dot)com
PGP Key: 0x14964AC8 200303041444
-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html

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

* greg(at)turnstep(dot)com <greg(at)turnstep(dot)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(at)izzy(dot)net
http://khtml-win32.sourceforge.net/ - KHTML on Windows

* greg(at)turnstep(dot)com <greg(at)turnstep(dot)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(at)izzy(dot)net
http://khtml-win32.sourceforge.net/ - KHTML on Windows

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">
<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(at)turnstep(dot)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(at)gmx(dot)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(at)gmx(dot)net

Peter Eisentraut <peter_e(at)gmx(dot)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

Tom Lane wrote:
> 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 ...

It might be interesting to modify the protocol (and the backend at the
point of projection to the front end) so that a user defined formating
function could be applied and either accepted or rejected by the front
end. Perhaps one flavor of XML output is a start, but I could imagine
wanting a custom or even different "standard" output format.

Joe

> Peter Eisentraut <peter_e(at)gmx(dot)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?

Ah, but this approach has the merit that it doesn't require pushing out
a completely new set of tools.

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

That could be; there's enough variation in what one might want to do
with XML that it is not trivial to suggest an 'ideal' answer.

We have already seen the proposal of:
<record a="b" c="d" e="f">
<record a="c" c="e" e="g">
<record a="d" c="f" e="h">
<record a="e" c="g" e="i">

I would rather prefer something like:
<tablea>
<record>
<a>b</a> <c>d</c> <e>f</e>
</record>
<record>
<a>c</a> <c>d</c> <e>f</e>
</record>
<record>
<a>d</a> <c>d</c> <e>f</e>
</record>
<tablea>

(Note that both approaches are quite rational possibilities.)

I'd think that the "protocol" would involve passing back a row-as-string
for each row in the result set.
--
output = ("cbbrowne" "@cbbrowne.com")
http://www.ntlug.org/~cbbrowne/xml.html
"There are two major products that come out of Berkeley: LSD and Unix.
We don't believe this to be a coincidence." - Jeremy S. Anderson

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

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

Do you have a link to the exact section? I've found conflicting versions
of what constitutes the "standard" for xml output of SQL data.

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

I think that is a good long-term solution, but I still think we need
to address the TODO item in the short run, and allow for a simple
reformatting of the query results from psql. If not, we should remove
that TODO item form psql and add a different one to the backend section.

- --
Greg Sabino Mullane greg(at)turnstep(dot)com
PGP Key: 0x14964AC8 200303061020

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

iD8DBQE+Z2jHvJuQZxSWSsgRAj7IAJ4hLEos9OlE67O02gVrrqxwT9n3AQCeJxto
N2LFyvXPfGY2whPUs5k+PQA=
=PYfs
-----END PGP SIGNATURE-----

Peter Eisentraut kirjutas N, 06.03.2003 kell 00:37:
> greg(at)turnstep(dot)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.

I have written an aggregate function in pl/python for my own needs that
returns underlying query fomatted as XML, but it has some problems:

1) both the row-to-xml-fragment and
collect-the-fragments-to-wellformed-xml-doc have to be defined for each
and every different query (the actual function text is the same).

2) it is unneccesaryly hard to define a function that takes a record as
argument - the record type is lost: for even simple things like this

select * from (select * from mytable) mtab;

the result of inner query is _not_ of rowtype mytable, i.e.

you can do

select xmlfrag(mytable) from mytable;

but not

select xmlfrag(mytable) from (select * from mytable) mytable;

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

Tom Lane writes:

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

The spec defines "mappings" between tables, schemas, and catalogs on the
one side and each time a pair of XML documents on the other side, one of
which is an XML schema document (sort of a document type declaration) and
the other is an XML data document that follows the constraints of the
schema document and contains the actual data. A table could of course
more or less be interpreted to mean a query result. That means, this
functionality provides both query result retrieval via XML and a pg_dump
type mechanism with XML output.

So I imagine, if this is done fully with changes in the protocol layer,
then certain commands like "get table schema in XML" would have to exist
in the protocol, which doesn't seem right. Also, the XML output isn't a
sibling of the current text/binary tuples, since an XML result is always
a whole document, not tuple data.

What we could perhaps consider is a family of functions like I
illustrated, but then provide a fast-path-driven layer on the client side,
like for large objects. Initially, the development of these mapping
functions could take place totally in user-space.

--
Peter Eisentraut peter_e(at)gmx(dot)net

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> So I imagine, if this is done fully with changes in the protocol layer,
> then certain commands like "get table schema in XML" would have to exist
> in the protocol, which doesn't seem right. Also, the XML output isn't a
> sibling of the current text/binary tuples, since an XML result is always
> a whole document, not tuple data.

I would envision a distinction comparable to the existing one between T
and D messages (RowDescription and AsciiRow, using the documentation's
names): you send the table schema first, then the data. Also note that
there is no "command" to get the T message; it comes for free whenever
a SELECT result is sent to the frontend.

> What we could perhaps consider is a family of functions like I
> illustrated, but then provide a fast-path-driven layer on the client side,
> like for large objects. Initially, the development of these mapping
> functions could take place totally in user-space.

I don't object to that as a quick-and-dirty context for prototyping work,
but I'd sure hate to see it as the production version. The fastpath
protocol is a mess, and until/unless we get it cleaned up, we ought not
increase dependency on it.

A larger point is that this is still a protocol revision; pretending it
ain't is just willful obscurantism. You can tell it's a protocol revision
because you will need to rewrite client-side libraries to take advantage
of it. If we try to look the other way and pretend it isn't one, then
we'll just be incurring pain --- the most obvious pain being that it
will be hard for those client libraries to tell whether the protocol
extension is supported or not.

The way I'd prefer to see this handled is by providing alternatives to
the printtup.c DestReceiver routines. The backend could be switched to
any desired output representation just by invoking different sets of
receiver routines. What we seem to need first is a context for doing
that, in particular a way to understand how different output formats can
be fit into the FE/BE protocol.

regards, tom lane

Greg, do you have a newer patch to address the feedback you received, or
is this one good?

---------------------------------------------------------------------------

greg(at)turnstep(dot)com wrote:
[ There is text before PGP section. ]
>
[ PGP not available, raw data follows ]
> -----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(at)turnstep(dot)com
> PGP Key: 0x14964AC8 200302280938
>
> -----BEGIN PGP SIGNATURE-----
> Comment: http://www.turnstep.com/pgp.html
>
> iD8DBQE+X3k5vJuQZxSWSsgRAuXFAKDGO1IsjB9Lwtkcws1xJy47PibcLQCg3dx5
> fsy27qguZv841lPvCjzdUic=
> =4f9B
> -----END PGP SIGNATURE-----
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
>
[ Decrypting message... End of raw data. ]

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

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

> Greg, do you have a newer patch to address the feedback you received, or
> is this one good?

I have a newer patch, but I am not 100% sure a consensus was reached. I recall
the thread veering into talk of XML on the backend, but don't recall if anyone
still had strong objections to a quick psql wrapper. If not, I will clean up
the existing patch and resubmit tomorrow.

- --
Greg Sabino Mullane greg(at)turnstep(dot)com
PGP Key: 0x14964AC8 200303171641

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

iD8DBQE+dkE5vJuQZxSWSsgRAkVSAJ9aLoLC23OoNcVEw4hQiaBrPcSqNQCfTxH3
crC4ssFKbBo60gHvJT3WsU0=
=Qsif
-----END PGP SIGNATURE-----

I like the idea of doing XML in psql --- it seems like a natural place
for it.

---------------------------------------------------------------------------

greg(at)turnstep(dot)com wrote:
[ There is text before PGP section. ]
>
[ PGP not available, raw data follows ]
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
>
> > Greg, do you have a newer patch to address the feedback you received, or
> > is this one good?
>
> I have a newer patch, but I am not 100% sure a consensus was reached. I recall
> the thread veering into talk of XML on the backend, but don't recall if anyone
> still had strong objections to a quick psql wrapper. If not, I will clean up
> the existing patch and resubmit tomorrow.
>
> - --
> Greg Sabino Mullane greg(at)turnstep(dot)com
> PGP Key: 0x14964AC8 200303171641
>
> -----BEGIN PGP SIGNATURE-----
> Comment: http://www.turnstep.com/pgp.html
>
> iD8DBQE+dkE5vJuQZxSWSsgRAkVSAJ9aLoLC23OoNcVEw4hQiaBrPcSqNQCfTxH3
> crC4ssFKbBo60gHvJT3WsU0=
> =Qsif
> -----END PGP SIGNATURE-----
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
[ Decrypting message... End of raw data. ]

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> I like the idea of doing XML in psql --- it seems like a natural place
> for it.

Not really; what of applications other than shell scripts that would
like to get XML-formatted output?

There was some talk in the FE/BE protocol thread of adding hooks to
support more than one output format from the backend. Much of the
infrastructure already exists (see DestReceiver in the backend); we
just need an agreement on the protocol. On the whole I'd rather see
it done that way than burying the logic in psql.

regards, tom lane

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > I like the idea of doing XML in psql --- it seems like a natural place
> > for it.
>
> Not really; what of applications other than shell scripts that would
> like to get XML-formatted output?
>
> There was some talk in the FE/BE protocol thread of adding hooks to
> support more than one output format from the backend. Much of the
> infrastructure already exists (see DestReceiver in the backend); we
> just need an agreement on the protocol. On the whole I'd rather see
> it done that way than burying the logic in psql.

Well, programs can run psql using popen. It seems overkill to get the
protocol involved, specially since it is output-only. I can't imagine
who would bother with the wire protocol messiness just to get xml.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> Tom Lane wrote:
>> Not really; what of applications other than shell scripts that would
>> like to get XML-formatted output?

> Well, programs can run psql using popen. It seems overkill to get the
> protocol involved, specially since it is output-only. I can't imagine
> who would bother with the wire protocol messiness just to get xml.

Having to popen a psql isn't overkill? This seems like a far messier
solution than the other. Furthermore, it's just plain not an available
solution in many scenarios (think of a Java program running JDBC; it may
not have privileges to do popen, and may not have access to a copy of
psql anyway).

If we were not already opening up the protocol for changes, I'd be
resistant to the idea too. But since we are, I think it should be fixed
where it's cleanest to fix it.

regards, tom lane

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > Tom Lane wrote:
> >> Not really; what of applications other than shell scripts that would
> >> like to get XML-formatted output?
>
> > Well, programs can run psql using popen. It seems overkill to get the
> > protocol involved, specially since it is output-only. I can't imagine
> > who would bother with the wire protocol messiness just to get xml.
>
> Having to popen a psql isn't overkill? This seems like a far messier
> solution than the other. Furthermore, it's just plain not an available
> solution in many scenarios (think of a Java program running JDBC; it may
> not have privileges to do popen, and may not have access to a copy of
> psql anyway).
>
> If we were not already opening up the protocol for changes, I'd be
> resistant to the idea too. But since we are, I think it should be fixed
> where it's cleanest to fix it.

What would be interesting would be to enable libpq to dump XML, and have
psql use that. Why put XML capability in the backend? Of course, that
doesn't help jdbc. How do you propose the backend would do XML?

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> What would be interesting would be to enable libpq to dump XML, and have
> psql use that.

... or in the backend so libpq could use it, and thence psql.

> Why put XML capability in the backend?

So that non-libpq-based clients could use it.

regards, tom lane

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > What would be interesting would be to enable libpq to dump XML, and have
> > psql use that.
>
> ... or in the backend so libpq could use it, and thence psql.
>
> > Why put XML capability in the backend?
>
> So that non-libpq-based clients could use it.

OK, I have two ideas here. First, can we create a function that takes a
query result and returns one big XML string. I am not sure how to pump
a result into a function. The other downside is that we would have to
construct the entire result string in memory.

The other idea I had was a GUC variable that returned all query results
as one big XML string. That would prevent creating the entire string in
backend memory, and might enable cursor fetches through the XML string.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

I assume we are not moving in the XML/psql direction, right? We want it
int he backend, or the psql HTML converted to XHTML?

---------------------------------------------------------------------------

greg(at)turnstep(dot)com wrote:
[ There is text before PGP section. ]
>
[ PGP not available, raw data follows ]
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
>
> Patch to add XML output to psql:
>
> http://www.gtsm.com/xml.patch.txt
>
> Notes and questions:
>
> The basic output looks something like this:
>
> <?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">
> <a>1</a>
> <b>pizza</b>
> <c>2003-02-25 15:19:22.169797</c>
> <"mucho nacho "></"mucho nacho ">
> </row>
> <row num="2">
> <a>2</a>
> <b>mushroom</b>
> <c>2003-02-25 15:19:26.969415</c>
> <"mucho nacho "></"mucho nacho ">
> </row>
> <footer>(2 rows)</footer>
> </resultset>
>
> 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?
>
> 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?
>
> The HTML, XML, and Latex modes should have better documentation -
> I'll submit a separate doc patch when/if this gets finalized.
>
>
> - --
> Greg Sabino Mullane greg(at)turnstep(dot)com
> PGP Key: 0x14964AC8 200302261518
>
> -----BEGIN PGP SIGNATURE-----
> Comment: http://www.turnstep.com/pgp.html
>
> iD8DBQE+XSR/vJuQZxSWSsgRAi2jAJ9IAKnMBmNcVEEI8TXQBBd/rtm4XQCg0Vjq
> IO9OsCSkdnNJqnrYYutM3jw=
> =9kwY
> -----END PGP SIGNATURE-----
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
[ Decrypting message... End of raw data. ]

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

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

> I assume we are not moving in the XML/psql direction, right? We want it
> int he backend, or the psql HTML converted to XHTML?

I don't think a consensus was ever reached. It would certainly be better if
this was done on the backend, but that seems to be a long time away, and
some have argued that it is not the job of the engine to do this anyway.

I agree at the very least we should update the HTML ourput for psql: I'll
try to make a patch for that this weekend.

I still think we should at least have a rudimentary xml output option inside
of psql. It won't be perfect, but we can certainly have the flag toggle
a backend variable when/if the backend supports XML directly.

- --
Greg Sabino Mullane greg(at)turnstep(dot)com
PGP Key: 0x14964AC8 200305301452
-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html

iD8DBQE+16npvJuQZxSWSsgRAnTHAJ0UN3HFWVybqDd/5lnsV2CcotRxSgCgp7md
W9Iho/Y1mwUYEl8SX/9oAVc=
=G1Jx
-----END PGP SIGNATURE-----

> > I assume we are not moving in the XML/psql direction, right? We
> > want it int he backend, or the psql HTML converted to XHTML?
>
> I don't think a consensus was ever reached. It would certainly be
> better if this was done on the backend, but that seems to be a long
> time away, and some have argued that it is not the job of the engine
> to do this anyway.

Few points for the archives regarding XML and databases (spent 9mo
working on this kinda stuff during the .com days):

*) Use libxml2. MIT Licensed, most complete opensource XML
implementation available, and fast. See the XML benchmarks on
sf.net for details. To avoid library naming conflicts, the library
should likely be renamed to pgxml.so and imported into the src
tree. Mention java in this context and risk being clubbed to death.

*) There should be two storage formats for XML data:

a) DOM-esque storage: broken down xmlNodes. This is necessary for
indexing specific places in documents (ala XPath queries).
Actual datums on the disk should be similar in structure to the
xmlNode struct found in libxml2 (would help with the
serialization in either direction). In database xslt
transformations are also possible with the data stored this way.

b) SAX-esque storage: basically a single BYTEA/TEXT column. Not
all documents need to be indexed/searchable and SAX processing
of data is generally more efficient if you don't know what
you're looking for. This format is the low hanging fruit
though.

-sc

--
Sean Chittenden

Sean Chittenden kirjutas R, 30.05.2003 kell 23:20:
> > > I assume we are not moving in the XML/psql direction, right? We
> > > want it int he backend, or the psql HTML converted to XHTML?
> >
> > I don't think a consensus was ever reached. It would certainly be
> > better if this was done on the backend, but that seems to be a long
> > time away, and some have argued that it is not the job of the engine
> > to do this anyway.
>
> Few points for the archives regarding XML and databases (spent 9mo
> working on this kinda stuff during the .com days):
>
> *) Use libxml2. MIT Licensed, most complete opensource XML
> implementation available, and fast. See the XML benchmarks on
> sf.net for details. To avoid library naming conflicts, the library
> should likely be renamed to pgxml.so and imported into the src
> tree. Mention java in this context and risk being clubbed to death.

Agree completely on all points ;)

> *) There should be two storage formats for XML data:
>
> a) DOM-esque storage: broken down xmlNodes. This is necessary for
> indexing specific places in documents (ala XPath queries).
> Actual datums on the disk should be similar in structure to the
> xmlNode struct found in libxml2 (would help with the
> serialization in either direction). In database xslt
> transformations are also possible with the data stored this way.
>
> b) SAX-esque storage: basically a single BYTEA/TEXT column. Not
> all documents need to be indexed/searchable and SAX processing
> of data is generally more efficient if you don't know what
> you're looking for. This format is the low hanging fruit
> though.

I think that Oleg and Todor very recently proposed somethink that could
use b) and still provide indexed access.

Most flexible would be some way to define, how much of a tree is kept
together, as xmlNode/tuple would probably be too much overhead for most
operations, whereas xmlFile/tuple would also, just for other ops;)

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