Skip site navigation (1) Skip section navigation (2)

Re: PostgreSQL's XML support comparison against other

From: SCassidy(at)overlandstorage(dot)com
To: "Ted Byers" <r(dot)ted(dot)byers(at)rogers(dot)com>
Cc: "pgsql general" <pgsql-general(at)postgresql(dot)org>
Subject: Re: PostgreSQL's XML support comparison against other
Date: 2006-03-29 20:34:53
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-general
If you have just a column containing a chunk of xml, and want to transform
it into individual columns, such that each element/attribute is its own
column, it should be pretty easy.  Here's a simple perl example:

                            Table "public.testxml1"
 Column  |  Type   |                         Modifiers
 id1     | integer | not null default
 xmltext | text    |

 id1 |       ltext
   1 | <animal>
    <breed>Cocker Spaniel</breed>
    <price currency="US dollars">100.00</price>
   2 | <animal>
    <breed>Toy poodle</breed>
    <price currency="US dollars">120.00</price>
   3 | <animal>
    <price currency="US dollars">125.00</price>
(3 rows)

Create new table, with a column per element/attribute:

create table flatdata (
  id              serial primary key,
  type            varchar(50),
  breed           varchar(50),
  color           varchar(50),
  sex             varchar(10),
  stock_number    varchar(30),
  price           numeric (8,2),
  currency        varchar(30)

Run the perl program (below) against the original xml data, and get new

 id | type |     breed      | color |  sex   | stock_number | price  |
  7 | dog  | Cocker Spaniel | black | male   | 123456       | 100.00 | US
  8 | dog  | Toy poodle     | white | female | 234567       | 120.00 | US
  9 | dog  | Chihuahua      | tan   | male   | 345678       | 125.00 | US
(3 rows)

#=========================  perl program:  ====================

use DBI;
use XML::Twig;
our $dbh;
our $sth;
our $sth2;

my $dbname='mytestdb1';
my $dbport=5432;


#=============== connect to database =============================
$dbh = DBI->connect("dbi:Pg:dbname=$dbname", $dbuser, "", {PrintError => 0,
PrintWarn => 0}) or
 errexit( "Unable to connect to dbname $dbname, err: $DBI::errstr");

print "Connect done\n";

my (@data);

my $twig=new XML::Twig(
  keep_spaces=> 1,
  twig_roots => {
         'animal' => \&process_entry }

my $stmt=qq(select xmltext from testxml1);
execute_db_statement($stmt, __LINE__);

my $insert=<<"EOF";
 INSERT into flatdata (type, breed, color, sex, stock_number, price,
  values (?, ?, ?, ?, ?, ?, ?)

our $cnt=0;

#prepare the insert statement, with placeholders:
$sth2=$dbh->prepare($insert) ||
    errexit("bad prepare for stmt $insert, error: $DBI::errstr");

#fetch each xml row, transform into individual columns:
while (@data = $sth->fetchrow_array) {
  foreach (@data) { $_='' unless defined}
  next if ($data[0] eq '');

#check for problems with premature termination
errexit($sth->errstr) if $sth->err;

sub process_entry {
  my ($t, $element)=(at)_;

  #process elements under the 'animal' element:

  my $type=$element->first_child('type');
  my $type_text;
  if ($type) {

  my $breed=$element->first_child('breed');
  my $breed_text;
  if ($breed) {

  my $color=$element->first_child('color');
  my $color_text;
  if ($color) {

  my $sex=$element->first_child('sex');
  my $sex_text;
  if ($sex) {

  my $stock_number=$element->first_child('stock_number');
  my $stock_number_text;
  if ($stock_number) {

  my $price=$element->first_child('price');
  my ($price_text, $price_currency);
  if ($price) {
    $price_currency=$price->{'att'}->{'currency'}; #currency attribute


  my $rc2=$sth2->execute($type_text, $breed_text, $color_text, $sex_text,
        $price_text, $price_currency) ||
    die("can't insert data for item: $type, $breed, $color, $stock_number,
DB error: $DBI::errstr");

} # end sub process_entry

sub errexit {
  my (@msg)=(at)_;
  print @msg,"\n";
  exit 1;

sub execute_db_statement {
  #this subroutine will prepare and execute a statement for the database,
  # and errexit if it fails either step
  my ($statement, $lineno)=(at)_;
  my ($rc);
  $sth=$dbh->prepare($statement) ||
    errexit("bad prepare for stmt $statement at line $lineno, error:
  $rc=$sth->execute() ||
    errexit("can't execute statement:\n$statement\n at line $lineno, ",
     "return code $rc: DB error: $DBI::errstr");
} # end sub execute_db_statement

#=======================================  end perl pgm

Easy!  It could be fancied up, of course, this was quick and dirty.


                           "Ted Byers"                                                                                                    
                      <r(dot)ted(dot)byers(at)rogers(dot)com        To:       "pgsql general" <pgsql-general(at)postgresql(dot)org>                             
                      >                              cc:                                                                                  
                           Sent by:                  Subject:  Re: [GENERAL] PostgreSQL's XML support comparison against other RDBMSes    
                      pgsql-general-owner(at)pos         | [ ] Expand Groups |                                                               
                           03/29/2006 10:24                                                                                               

May I ask a question about this?

I will be working on an older database in which the original developer
stored XML documents as a single variable length text field.  To process
it has to be retrieved in full and parsed.  But the structure of it is
simple in that it has an element for each field in the DB that replaced the

earily version.  But people are still using the earlier one because they
still need access to the old data and no tool has yet been written by my
predecessors to move the old data over to the new DB.  Does the XML support

you're all talking about make it less tedious to develop tools to parse
these old XML files and put their data into the right field of the right
table?  I can develop the tool our users need using the resources I have at

hand, but the proces of parsing these XML files is certain to be very
tedious; something I am not looking forward to.  There is a reason I don't
do much with XML even though I know how.

----- Original Message -----
From: "Steve Atkins" <steve(at)blighty(dot)com>
To: "pgsql general" <pgsql-general(at)postgresql(dot)org>
Sent: Wednesday, March 29, 2006 12:21 PM
Subject: Re: [GENERAL] PostgreSQL's XML support comparison against other

> On Mar 29, 2006, at 2:57 AM, Ivan Zolotukhin wrote:
>> On 3/29/06, Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:
>>> Ivan Zolotukhin wrote:
>>>> BTW, are there any plans for its improvement? What are the first
>>>> steps for the people who want to contribute to PostgreSQL in this
>>>> field?
>>> The first step would be making a detailed analysis of what "XML
>>> support"
>>> is supposed to mean.
>> So let's make such analysis here within PostgreSQL community! There
>> were a lot of talks about XML support in Postgres, but they did not
>> lead to any steady work in this direction. IMHO, it's time to start it
>> now, no?
> The people who want it should explain what it is, ideally by
> pointing at the relevant standards documents.
> Cheers,
>   Steve
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>       choose an index scan if your joining column's datatypes do not
>       match

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Simply protected storage solutions ensure that your information is
automatically safe, readily available and always there, visit us at

pgsql-general by date

Next:From: karlyDate: 2006-03-29 20:40:21
Subject: Re: ODed on overloads
Previous:From: Joshua D. DrakeDate: 2006-03-29 20:27:23
Subject: Re: PostgreSQL client api

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group