Re: 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/

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

Integrated, native XML support can only help PostgreSQL. IMO, I want
this in core.

Sincerely,

Joshua D. Drake

--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

On Feb 10, 2007, at 2:35 PM, Joshua D. Drake wrote:

> Peter Eisentraut wrote:
>> 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.
>>
>
> Integrated, native XML support can only help PostgreSQL. IMO, I want
> this in core.

Agreed. In the server would be more useful to more people I think.
It would be really convenient to be able to have "no effort" XML
results sets to queries.

// Theo Schlossnagle
// Principal(at)OmniTI: http://omniti.com
// Esoteric Curio: http://www.lethargy.org/~jesus/

Joshua D. Drake wrote:
> Peter Eisentraut wrote:
>> 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.
>>
>
> Integrated, native XML support can only help PostgreSQL. IMO, I want
> this in core.

+1

Regards, Dave.

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

I'm not really a XML fan - but nevertheless having something like this
in core sounds useful.

Stefan

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

How do you treat columns whose names are not legal XML names?

I'm glad to see you treat NULL as an attribute - that's definitely the
right way I think.

Have you thought about possibly using a standard encoding (e.g. base64)
for bytea? Not sure what the standard says on encoding.

cheers

andrew

Andrew Dunstan wrote:
> How do you treat columns whose names are not legal XML names?

There are escape mechanisms in place. You can verify yourself how they
work using

select xmlelement(name "something unusual");

> I'm glad to see you treat NULL as an attribute - that's definitely
> the right way I think.

The standard provides for the option of representing them the way I
showed or omitting them.

> Have you thought about possibly using a standard encoding (e.g.
> base64) for bytea? Not sure what the standard says on encoding.

It says to use base64 or hex. You can also verify that yourself using

select xmlelement(name foo, bytea 'something');

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

On Sat, Feb 10, 2007 at 11:35:08AM -0800, Joshua D. Drake wrote:
> Peter Eisentraut wrote:
> > 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.
> >
>
> Integrated, native XML support can only help PostgreSQL. IMO, I want
> this in core.

+1 :)

Cheers,
D
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter

Remember to vote!

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

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

> How would you express null in the values above?

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