Thread: 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:minInclusivevalue="-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:elementname='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@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'>
>
> <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/