X-Original-To: pgsql-performance-postgresql.org@postgresql.org Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id 61FE89FCCC5 for ; Mon, 28 Aug 2006 02:31:20 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 44714-02 for ; Mon, 28 Aug 2006 05:31:13 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from py-out-1112.google.com (py-out-1112.google.com [64.233.166.181]) by postgresql.org (Postfix) with ESMTP id B8EE29FC9BC for ; Mon, 28 Aug 2006 02:31:12 -0300 (ADT) Received: by py-out-1112.google.com with SMTP id d80so2290666pyd for ; Sun, 27 Aug 2006 22:31:12 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=l8q4v5gvv9BcNceQNvMcYmbluSveBA9RfTNuGl3tOPoBfHmv7ezGFCAIGvFFtRz/NoxMTS6hxlcvThl3zq7ganfNwDT6G3d/eWUFdwteBlnUEsa2kIvpk6WvscFUpcxT5oBO/8RkLcWA3BPhuY8rhKMqGcb6ms+VALziRjVcYug= Received: by 10.35.20.14 with SMTP id x14mr11626236pyi; Sun, 27 Aug 2006 22:31:12 -0700 (PDT) Received: by 10.35.9.8 with HTTP; Sun, 27 Aug 2006 22:31:11 -0700 (PDT) Message-ID: <9f2e40a90608272231k3eccfcefkb98d77f8f953d0ef@mail.gmail.com> Date: Mon, 28 Aug 2006 11:01:11 +0530 From: "soni de" To: "George Pavlov" Subject: Re: Related to Inserting into the database from XML file Cc: "Jeff Davis" , pgsql-performance@postgresql.org In-Reply-To: <8C5B026B51B6854CBE88121DBF097A863CD6DB@ehost010-33.exch010.intermedia.net> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_170601_7933493.1156743071934" References: <1156522767.1347.22.camel@dogma.v10.wvs> <8C5B026B51B6854CBE88121DBF097A863CD6DB@ehost010-33.exch010.intermedia.net> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.334 tagged_above=0 required=5 tests=DNS_FROM_RFC_ABUSE, HTML_50_60, HTML_MESSAGE, SPF_PASS X-Spam-Level: X-Archive-Number: 200608/418 X-Sequence-Number: 20603 ------=_Part_170601_7933493.1156743071934 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline I am little bit confused between whether to insert XML file as it is or insert data from the XML file in to a particular field from the table. I will decided it depending upon the performance factor For storing the XML file as it is, will there be any performance cause if compared to storing values in particular fields. If performance issue is not there for XML formats then we have around 12 to 13 tables, if we store XML data as it is in all tables then is there any generic format for select query? Thanks Soni On 8/27/06, George Pavlov wrote: > > > On Fri, 2006-08-25 at 21:23 +0530, soni de wrote: > > > Hello, > > > > > > I want to ask, Is there any way to insert records from XML > > > file to the postgres database? > > > > Try the contrib/xml2 module. > > Alas, that module will not help you much with the insertion of records. > It is more about querying XML that is stored within the database. > > A basic question is whether you want to store XML in the DB or you just > have data that is in XML now and you want it loaded into a table > structure. The xml2 module will only be useful in the first case. > > In either case the approach is to transform the data into a form that > PGSQL's COPY understands or into a bunch of INSERT statements (much less > performant). To that end you probably want to become familiar with XSLT > unless the data is so simple that a processing with regular tools (perl, > sed, awk) will suffice. > > George > ------=_Part_170601_7933493.1156743071934 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline

I am little bit confused between whether to insert XML file as it is or insert data from the XML file in to a particular field from the table.

I will decided it depending upon the performance factor

 

For storing the XML file as it is, will there be any performance cause if compared to storing values in particular fields.

 

If performance issue is not there for XML formats then we have around 12 to 13 tables,

if we store XML data as it is in all tables then is there any generic format for select query?

 

 

 Thanks
Soni

 
On 8/27/06, George Pavlov <gpavlov@mynewplace.com> wrote:
> On Fri, 2006-08-25 at 21:23 +0530, soni de wrote:
> > Hello,
> >
> > I want to ask, Is there any way to insert records from XML
> > file to the postgres database?
>
> Try the contrib/xml2 module.

Alas, that module will not help you much with the insertion of records.
It is more about querying XML that is stored within the database.

A basic question is whether you want to store XML in the DB or you just
have data that is in XML now and you want it loaded into a table
structure. The xml2 module will only be useful in the first case.

In either case the approach is to transform the data into a form that
PGSQL's COPY understands or into a bunch of INSERT statements (much less
performant). To that end you probably want to become familiar with XSLT
unless the data is so simple that a processing with regular tools (perl,
sed, awk) will suffice.

George

------=_Part_170601_7933493.1156743071934--