XML question

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

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.

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