Re: XML export

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