Forum OpenACS Development: Postgres to XML

Collapse
Posted by Dan Lieberman on
Does anyone know if there is a procedure (tcl or plpgsql) that takes
a postgres class, array of attributes and a query string and returns
a file of XML?
Collapse
Posted by Dan Wickstrom on
Look in packages/cms/www/modules/items/export-xml.tcl.  This is used to export a content revision, but it could be used as model for creating what you want.  It will only work for oracle, as postgresql doesn't include xml utilities.  Probably, these routines should be rewritten to use ns_xml, so both the oracle and postgresql versions can use the same code.  The corresponding sql routine is content_revision.export_xml, and it is located in  packages/acs-content-repository/sql/oracle/content-revision.sql.
Collapse
Posted by Dan Lieberman on

Sounds good - I'll take a close look. I saw how Oracle does it and thought we could clone their approach, so I started by specing out plpgsql functions that takes a pg class, attributes and sql query and output an xml file by introspecting thru the system tables (pg_class, pg_type, pg_attribute). In the meantime - i've written some simple-minded tcl code that does a similar thing.

Thanks, Danny

Collapse
Posted by Roberto Mello on
Look in the contrib/xml directory of PostgreSQL. Another option would be to integrate TclDOM in PL/TclU:

rbm@zippy:~$ apt-cache show postgresql-contrib
Package: postgresql-contrib
Priority: optional
Section: misc
Installed-Size: 1480
Maintainer: Oliver Elphick
Description: Additional facilities for PostgreSQL
 The PostgreSQL contrib package provides several additional features for
 the PostgreSQL database. contrib often serves as a testbed for features
 before they are adopted into PostgreSQL proper:
 .
...
xml            - Manipulate XML documents in the database