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

Re: Export to XML

From: "Dean Gibson (DB Administrator)" <postgresql4(at)ultimeth(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Re: Export to XML
Date: 2005-11-18 21:16:21
Message-ID: 437E44A5.508@ultimeth.com (view raw or flat)
Thread:
Lists: pgsql-sql
On 2005-11-11 10:26, Warren Murray wrote:
> What is the process to export a PostgreSQL table to XML?  How is it done?
> Thanks!
>   

Two ways using PSQL:

1. Select HTML output ("\H" command) and then do a very minor amount of 
post processing (details left to the reader).

2. Select "expanded" output ("\x" command), and then use SED to 
post-format the output into XML:

sed -r 's:^-\[ RECORD (.*) \]-+$:</row>\n<row number="\1">:;s:([^ ]*) 
+\| (.*):  <\1>\2</\1>:;s:^$:</row>:;1s:</row>\n::'

There's probably a cleaner way with AWK, but the above is simple enough 
(your eMail reader may break the above into separate lines at the 
positions where I had a space).  The last two "s" commands just handle 
the first/last line cases.

The only thing the above does not handle is NULL values as distinct from 
zero-length strings.

-- Dean


In response to

pgsql-sql by date

Next:From: Jeremy PalmerDate: 2005-11-18 22:49:49
Subject: DISTINCT ON
Previous:From: Andrew SullivanDate: 2005-11-18 15:19:29
Subject: Re: how to do a find and replace

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