XML export

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@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@fetter.org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter

Remember to vote!

Hello

If you integrate xml_export to core, you don't need string argument, which
isn't too handy, but you can use COPY stmt aparat. I don't speak about
enhancing stmt COPY.

Regards
Pavel Stehule

p.s.
it's can be great if xmloutput will be independent on datestyle
root=# set datestyle TO German ;
SET
root=# select xmlelement(name bbb, current_date);
xmlelement
-----------------------
<bbb>11.02.2007</bbb>

_________________________________________________________________
Emotikony a pozadi programu MSN Messenger ozivi vasi konverzaci.
http://messenger.msn.cz/

Pavel Stehule wrote:

If you integrate xml_export to core, you don't need string argument,
which isn't too handy, but you can use COPY stmt aparat. I don't
speak about enhancing stmt COPY.

Then what do you speak about?

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

Pavel Stehule wrote:

If you integrate xml_export to core, you don't need string argument,
which isn't too handy, but you can use COPY stmt aparat. I don't
speak about enhancing stmt COPY.

Then what do you speak about?

I thought about some special function. But why not? COPY is perfect for this
task.

Regards
Pavel Stehule

_________________________________________________________________
Najdete si svou lasku a nove pratele na Match.com. http://www.msn.cz/

Pavel Stehule wrote:

I thought about some special function. But why not? COPY is perfect
for this task.

I don't understand what you are asking for. Please show an example.

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

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&#39;&gt;

<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&#39;
xmlns:sqlxml='http://standards.iso.org/iso/9075/2003/sqlxml&#39;&gt;

<xsd:import
namespace='http://standards.iso.org/iso/9075/2003/sqlxml&#39;
schemaLocation='http://standards.iso.org/iso/9075/2003/sqlxml.xsd&#39;/&gt;

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