Copying xml SCHEMA file into a new postgresql database

wormguy_1
wormguy_1 used Ask the Experts™
on
Naive question I'm sure...I'm a project leader trying to test a new application independently.  A Java jar provides a user interface with a postgresl DB.  This is the second version of a database and has a slightly different schema than the first (I believe). The new app seems to work well, but cannot interact with our old DB.  Our engineer produced an xml file (shows up as SCHEMA file in WinXP) that defines an initial set of data and tables (13k lines) for the second version.  The previous version has (all public role) 2 domains, 692 functions, 6 sequences, 17 tables, 7 Trigger functions, and 3 views.  

QUESTION:  How do I populate a new db with this initial dataset?  I cannot RESTORE a text file.  Also the xml file only appears to contain table definitions and data, how do I copy, generate, or ask for the other necessary structural bits?

Sample ------------------
<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<BT2>
      <Table name="ABUNDANCE">

            <Col name="Record" type="integer">
                  <Row id="1" data="1" />
            </Col>
            <Col name="RecordCreationDate" type="timestamp">
                  <Row id="1" data="39807.4173611111" />
            </Col>
            <Col name="TaxonId" type="text">
                  <Row id="1" data="1" />
            </Col>
            <Col name="SampleID" type="text">
                  <Row id="1" data="TestSample_SiteA_Rep1" />
            </Col>
            <Col name="Abundance" type="integer">
                  <Row id="1" data="42" />
            </Col>
            <Col name="ChosenForOutput" type="boolean">
                  <Row id="1" data="FALSE" />
                  "
            </Col>

      </Table>

      <Table name="REF_MATERIAL">
            <Col name="MediaItemID" type="integer">
                  <Row id="1" data="1" />
                  <Row id="2" data="2" />
etc.

cheers,
Brian
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
Is there a way to feed this back in as if it were a pg_dump?
NerdsOfTechTechnology Scientist

Commented:
not sure if this works but I googled xml to postgre and came up with this:

http://ems-data-import-2007-for-postgresql.software.informer.com/3.0/
I ended up constructing Oo Calc tables from the XML and then reading into PostgreSQL.

Author

Commented:
Self solved after few Expert responses over extended period of time.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial