Postgres Pro
Downloads
Home   >   mailing lists

Thread: XML question

XML question

From
Tony Grant
Date:
hello,

Just a couple of questions:

    - what is the _most popular_ way of storing XML data in posgresql?

    - what is the _best_ way of stocking XML data in postgresql?

Quick and nasty opinion poll...

Cheers

Tony Grant

--
RedHat Linux on Sony Vaio C1XD/S
http://www.animaproductions.com/linux2.html
Macromedia UltraDev with PostgreSQL
http://www.animaproductions.com/ultra.html

Re: XML question

From
Peter Eisentraut
Date:
Tony Grant writes:

>     - what is the _most popular_ way of storing XML data in posgresql?
>
>     - what is the _best_ way of stocking XML data in postgresql?

'text' is probably the only possible and reasonable answer to either of
these (except for 'varchar').

--
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter

Re: XML question

From
Manuel Sugawara
Date:
Peter Eisentraut <peter_e@gmx.net> writes:

> Tony Grant writes:
>
> >     - what is the _most popular_ way of storing XML data in posgresql?
> >
> >     - what is the _best_ way of stocking XML data in postgresql?
>
> 'text' is probably the only possible and reasonable answer to either of
> these (except for 'varchar').

I'm using xml-schema to specify my schema, postgreSQL to store the
data and some scripts to do the translation.  The idea is: every
element that may appear in the document is stored in a table which has
the same attributes as the element, ie if you have the definition of
the element as follows:

<complexType name="student">
  <attribute name="id" type="xs:integer"/>
  <attribute name="name" type="xs:normalizedString"/>
</complexType>

it will be mapped into postgres as:

create table student
(
  id primary key,
  name text
);

so, when I read an element of the form:

<student id="10" name="masm"/>

it is stored into postgres using something like:

insert into student (id,name) values (10,xs_normalize('masm'));

Ok, this may get more complex when you use sequence's inside the
element definition, but it is done using references. This
idea may not work with mixed content, or complex documents as html,
but it does work for me :-)

hth,
Manuel.

RE: XML question

From
"Andrew Snow"
Date:
If you're asking how to get XML data into PostgreSQL, for perl users:

Matt Sergeant's DBIx::XML_RDB module looks handy.  (I think it just got
added to the FreeBSD ports collection too).
http://www.xml.com/pub/a/2000/12/13/perlxmldb.html

Or use any XML parser module (XML::Simple!) and pump the data into
PostgreSQL using Pg or DBD.

Or roll your own parser, this doesn't take long in perl!

(I have successfully used the latter two methods)

- Andrew



> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Tony Grant
> Sent: Wednesday, 6 June 2001 12:49 AM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] XML question
>
>
> hello,
>
> Just a couple of questions:
>
>     - what is the _most popular_ way of storing XML data in posgresql?
>
>     - what is the _best_ way of stocking XML data in postgresql?
>
> Quick and nasty opinion poll...