Re: export data into xml

[Pavel Stehule <stehule@xxxxxxxxxxxxxxx>]

> hello everybody!
> 
> i just subscribed to the list, so hello to all friends of pgsql.

Hello here ;-)

> 
> now here is my concrete problem:
> 
> i have to exchange data with an other company. they are running a ms sql
> 2000 serer and want to import our data via xml. so, how do i get it out of
> the pgsql database?

if you can use plperl, then you can use following procedures or you can 
use one part of contrib

CREATE OR REPLACE FUNCTION ext.xml(varchar, varchar) RETURNS text AS $$
   use DBI;
   use XML::Generator::DBI;
   use XML::Handler::YAWriter;
   my $rv = spi_exec_query("SELECT current_setting('client_encoding'), " .
                           "current_database()", 1);
   my $encoding = lc($rv->{rows}[0]->{current_setting[5~});
   my $cdb = $rv->{rows}[0]->{current_database};
   my $dbh = DBI->connect ("DBI:Pg:dbname=$cdb", "", "", { RaiseError => 
1, PrintError => 0});
   my $ya = XML::Handler::YAWriter->new (Encoding=>$encoding, 
        Pretty => {PrettyWhiteNewline => 1, PrettyWhiteIndent => 1,});
   my $gen = XML::Generator::DBI->new (Handler => $ya, dbh => $dbh,
                                       ByColumnName => 1, ShowColumns => 
1,
				       Stylesheet => $_[1],);
   $gen->execute ($_[0]);
   $dbh->disconnect ();
   return join('',@{$ya->{Strings}});
$$ LANGUAGE plperlu;
SELECT ext.xml('SELECT * FROM ext.names LIMIT 10','x01.xsl');

CREATE OR REPLACE FUNCTION ext.rxml(varchar) RETURNS SETOF RECORD AS $$
   use XML::Parser; use Encode;
   my %rec; my @tref = ();
   my $parser = new XML::Parser (
      Handlers => {
         Start => sub {
	    my ($p, $tag) = @_; 
            if ($tag eq "row" && $p->depth==2) { %rec = ();}},
         End   => sub {
            my ($p, $tag) = @_;
	    if ($tag eq 'row') {push @tref, +{ %rec }; }},
         Char  => sub {
            my ($p, $data) = @_; 
            my $tag = $p->current_element ();
	    if ($p->depth == 4) {
	       chomp $data; $data =~ s/\^s+|\s+$//g;
	       $rec{$tag} .= encode("iso-8859-2", $data) if $data ne 
"";}}, 
      });
   $parser->parsefile ($_[0]);
   return \@tref;
$$ LANGUAGE plperlu;

CREATE VIEW ext.xmlj AS SELECT * FROM ext.rxml('/tmp/names.xml') AS (
  ixkontakt integer, jmeno varchar, prijmeni varchar, 
  titul varchar, email varchar, tel1 varchar);


regards
Pavel Stehule


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

               http://archives.postgresql.org