Re: XML export - Mailing list pgsql-hackers

Peter Eisentraut schrieb:
> 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.
> 

How would you express null in the values above?

Regards
Tino