Обсуждение: XML -> PG ?
Is there a way to read an XML file into a postgres DB? I’m thinking that it will create and relate whatever tables are necessary to reflect whatever’s implied by the XML file structure.
Thanks for any pointers !
On Wed, May 6, 2009 at 10:47 AM, Gauthier, Dave <dave.gauthier@intel.com> wrote: > Is there a way to read an XML file into a postgres DB? I’m thinking that it > will create and relate whatever tables are necessary to reflect whatever’s > implied by the XML file structure. since xml is basically completely unstructured, you are not giving enough information about what you'd like to do. that said, be sure and check out the pg xml extensions... merlin
On May 6, 2009, at 10:47 AM, Gauthier, Dave wrote:
Is there a way to read an XML file into a postgres DB? I’m thinking that it will create and relate whatever tables are necessary to reflect whatever’s implied by the XML file structure.
There's no built-in functionality that does what you describe, although building such a thing would be very straight-forward. There are many application-specific decisions you'd need to make (what level of hierarchy in the XML file corresponds to a database/schema/table, for example, and how to handle nested "fields" and missing ones).
Maybe...
<ALL>
<EMPLOYEES>
<EMP EMP_NAME="JOE" JOB="CARPENTER" />
EMP EMP_NAME="FRANK" JOB="PLUMBER"/>
EMP EMP_NAME="SUE" JOB="CARPENTER"/>
</EMPLOYEES>
<JOBS>
<JOB JOB_NAME="CARPENTER" SALARY=25.50 />
<JOB JOB_NAME="PLUMBER" SALARY=28.75 />
</JOBS>
</ALL>
...equals...
create table employees (emp_name varchar[64], job varchar[64]);
create table jobs (job_name varchar[64], salary float);
insert into employees (emp_name,job) values ('JOE','CARPENTER');
insert into employees (emp_name,job) values ('FRANK','PLUMBER');
insert into employees (emp_name,job) values ('SUE','CARPENTER');
insert into jobs (job_name,salary) values ("CARPENTER",25.50);
insert into jobs (job_name,salary) values ("PLUMBER",28.75);
After that, it's up to the user to understand that employees.job = jobs.job_name and index appropriately.
-----Original Message-----
From: Merlin Moncure [mailto:mmoncure@gmail.com]
Sent: Wednesday, May 06, 2009 11:23 AM
To: Gauthier, Dave
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] XML -> PG ?
On Wed, May 6, 2009 at 10:47 AM, Gauthier, Dave <dave.gauthier@intel.com> wrote:
> Is there a way to read an XML file into a postgres DB? I'm thinking that it
> will create and relate whatever tables are necessary to reflect whatever's
> implied by the XML file structure.
since xml is basically completely unstructured, you are not giving
enough information about what you'd like to do. that said, be sure and
check out the pg xml extensions...
merlin
hello, as a perl addict I am... I recommend checking this out: http://search.cpan.org/~cmungall/DBIx-DBStag/DBIx/DBStag/Cookbook.pm it's pretty flexible and allows you to specify to some extent just how the database structure is infered from the XML... check it out Joao On Wed, 2009-05-06 at 11:31 -0400, Christophe wrote: > > On May 6, 2009, at 10:47 AM, Gauthier, Dave wrote: > > > Is there a way to read an XML file into a postgres DB? I’m thinking > > that it will create and relate whatever tables are necessary to > > reflect whatever’s implied by the XML file structure. > > > There's no built-in functionality that does what you describe, > although building such a thing would be very straight-forward. There > are many application-specific decisions you'd need to make (what level > of hierarchy in the XML file corresponds to a database/schema/table, > for example, and how to handle nested "fields" and missing ones).
On Wed, 2009-05-06 at 16:53 +0100, Joao Ferreira gmail wrote: > hello, > > as a perl addict I am... I recommend checking this out: > > http://search.cpan.org/~cmungall/DBIx-DBStag/DBIx/DBStag/Cookbook.pm > > it's pretty flexible and allows you to specify to some extent just how > the database structure is infered from the XML... ... maybe start here to get a faster grasp: http://search.cpan.org/~cmungall/DBIx-DBStag/DBIx/DBStag.pm j > > check it out > > Joao > > > > > On Wed, 2009-05-06 at 11:31 -0400, Christophe wrote: > > > > On May 6, 2009, at 10:47 AM, Gauthier, Dave wrote: > > > > > Is there a way to read an XML file into a postgres DB? I’m thinking > > > that it will create and relate whatever tables are necessary to > > > reflect whatever’s implied by the XML file structure. > > > > > > There's no built-in functionality that does what you describe, > > although building such a thing would be very straight-forward. There > > are many application-specific decisions you'd need to make (what level > > of hierarchy in the XML file corresponds to a database/schema/table, > > for example, and how to handle nested "fields" and missing ones). > >
Gauthier, Dave, 06.05.2009 17:40:
> Maybe...
>
> <ALL>
> <EMPLOYEES>
> <EMP EMP_NAME="JOE" JOB="CARPENTER" />
> EMP EMP_NAME="FRANK" JOB="PLUMBER"/>
> EMP EMP_NAME="SUE" JOB="CARPENTER"/>
> </EMPLOYEES>
> <JOBS>
> <JOB JOB_NAME="CARPENTER" SALARY=25.50 />
> <JOB JOB_NAME="PLUMBER" SALARY=28.75 />
> </JOBS>
> </ALL>
>
> ...equals...
>
> create table employees (emp_name varchar[64], job varchar[64]);
> create table jobs (job_name varchar[64], salary float);
> insert into employees (emp_name,job) values ('JOE','CARPENTER');
> insert into employees (emp_name,job) values ('FRANK','PLUMBER');
> insert into employees (emp_name,job) values ('SUE','CARPENTER');
> insert into jobs (job_name,salary) values ("CARPENTER",25.50);
> insert into jobs (job_name,salary) values ("PLUMBER",28.75);
>
You could use XSLT to tranform the XML into the approriate SQL Statements.
Thomas
On Wed, 2009-05-06 at 16:53 +0100, Joao Ferreira gmail wrote: > hello, > > as a perl addict I am... I recommend checking this out: > > http://search.cpan.org/~cmungall/DBIx-DBStag/DBIx/DBStag/Cookbook.pm > > it's pretty flexible and allows you to specify to some extent just how > the database structure is infered from the XML... > maybe start here to get a faster grasp: http://search.cpan.org/~cmungall/DBIx-DBStag/DBIx/DBStag.pm j > check it out > > Joao > > > > > On Wed, 2009-05-06 at 11:31 -0400, Christophe wrote: > > > > On May 6, 2009, at 10:47 AM, Gauthier, Dave wrote: > > > > > Is there a way to read an XML file into a postgres DB? I’m thinking > > > that it will create and relate whatever tables are necessary to > > > reflect whatever’s implied by the XML file structure. > > > > > > There's no built-in functionality that does what you describe, > > although building such a thing would be very straight-forward. There > > are many application-specific decisions you'd need to make (what level > > of hierarchy in the XML file corresponds to a database/schema/table, > > for example, and how to handle nested "fields" and missing ones). > >
Gauthier, Dave wrote: > > Is there a way to read an XML file into a postgres DB? I’m thinking > that it will create and relate whatever tables are necessary to > reflect whatever’s implied by the XML file structure. > > > > Thanks for any pointers ! > That's a pretty common problem, and not one that needs to have a postgresql-specific solution. There are definitely solutions out there, but it may take some Googling to find a good one for your needs. One option might be data binding tools (Castor and Liquid XML come to mind) with which you can definitely go from XML to SQL, though many of them may require going through an intermediate object model. A simple approach (simple depending what technologies you're using and are already familiar with) might be to use XSLT to transform your XML either directly into SQL or into some other format easily loaded to a database (such as the XML format used by DbUnit). Eric
Gauthier, Dave wrote: > > Is there a way to read an XML file into a postgres DB? I’m thinking > that it will create and relate whatever tables are necessary to > reflect whatever’s implied by the XML file structure. > > Thanks for any pointers ! > As others have said, the fundamental problem is that generalized XML is freeform and inherently unstructured. Every piece of data can have a different structure. IMHO, XML is fundamentally flawed by mixing the metadata with the data.
On May 6, 2009, at 4:16 PM, Eric Schwarzenbach wrote: > Gauthier, Dave wrote: >> >> Is there a way to read an XML file into a postgres DB? I’m thinking >> that it will create and relate whatever tables are necessary to >> reflect whatever’s implied by the XML file structure. >> >> >> >> Thanks for any pointers ! >> > That's a pretty common problem, and not one that needs to have a > postgresql-specific solution. There are definitely solutions out > there, > but it may take some Googling to find a good one for your needs. One > option might be data binding tools (Castor and Liquid XML come to > mind) > with which you can definitely go from XML to SQL, though many of them > may require going through an intermediate object model. > > A simple approach (simple depending what technologies you're using and > are already familiar with) might be to use XSLT to transform your XML > either directly into SQL or into some other format easily loaded to a > database (such as the XML format used by DbUnit). > > Eric Call me a GUI boy, but I use JasperETL :) Ries