XML ouput for psql
gr...@turnstep.com
-----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 gr...@turnstep.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?
Hannu Krosing
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
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
Peter Eisentraut
> 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 pet...@gmx.net
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majo...@postgresql.org so that your
message can get through to the mailing list cleanly
gr...@turnstep.com
-----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 gr...@turnstep.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 majo...@postgresql.org