XML export

The issue of XML export has been discussed a few times throughout
history. Right now you've got the HTML output in psql. A few
people have proposed "real" XML output formats in psql or elsewhere.

I dug out some old code today that implements what SQL/XML has to say
on the matter and fitted the code to work with the current XML support
in the backend.

Below are examples of what it can do. I'm thinking about hosting this
on PgFoundry, but if the crowd thinks this should be somewhere else,
short of the moon, let me know.

regression=# select table_to_xml('select * from emp');
table_to_xml
---------------------------------------------------------------
<table xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance'>

<row>
<name>sharon</name>
<age>25</age>
<location>(15,12)</location>
<salary>1000</salary>
<manager>sam</manager>
</row>

...

<row>
<name>linda</name>
<age>19</age>
<location>(0.9,6.1)</location>
<salary>100</salary>
<manager xsi:nil='true'/>
</row>

</table>

(1 row)

As a use case of sorts, I've got an XSLT stylesheet that can convert
this to HTML tables.

regression=# select table_to_xmlschema('select * from emp');
table_to_xmlschema
-----------------------------------------------------------------------------------------------------------------
<xsd:schema
xmlns:xsd='http://www.w3.org/2001/XMLSchema'
xmlns:sqlxml='http://standards.iso.org/iso/9075/2003/sqlxml'>

<xsd:import
namespace='http://standards.iso.org/iso/9075/2003/sqlxml'
schemaLocation='http://standards.iso.org/iso/9075/2003/sqlxml.xsd'/>

<xsd:simpleType name="X-PostgreSQL.regression.pg_catalog.text">
<xsd:restriction base="xsd:string">
<xsd:maxLength value="MLIT"/>
</xsd:restriction>
</xsd:simpleType>

<xsd:simpleType name="INTEGER">
<xsd:restriction base='xsd:int'>
<xsd:maxInclusive value="2147483647"/>
<xsd:minInclusive value="-2147483648"/>
</xsd:restriction>
</xsd:simpleType>

<xsd:simpleType name='X-PostgreSQL.regression.pg_catalog.point'></xsd:simpleType>

<xsd:simpleType name='X-PostgreSQL.regression.pg_catalog.name'></xsd:simpleType>

<xsd:complexType name='RowType'>
<xsd:sequence>
<xsd:element name='name' type='X-PostgreSQL.regression.pg_catalog.text' nillable='true'></xsd:element>
<xsd:element name='age' type='INTEGER' nillable='true'></xsd:element>
<xsd:element name='location' type='X-PostgreSQL.regression.pg_catalog.point' nillable='true'></xsd:element>
<xsd:element name='salary' type='INTEGER' nillable='true'></xsd:element>
<xsd:element name='manager' type='X-PostgreSQL.regression.pg_catalog.name' nillable='true'></xsd:element>
</xsd:sequence>
</xsd:complexType>

<xsd:complexType name='TableType'>
<xsd:sequence>
<xsd:element name='row' type='RowType' minOccurs='0' maxOccurs='unbounded'/>
</xsd:sequence>
</xsd:complexType>

<xsd:element name='table' type='TableType'/>

</xsd:schema>
(1 row)

I also have a table function which can convert both of these back into
an table, so that would be XML import. But that doesn't work quite yet.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/