X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 8C9E9DBAE7
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 18 Nov 2005 17:16:24 -0400 (AST)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 94561-01
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 18 Nov 2005 21:16:25 +0000 (GMT)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from mail.mailpen.net (mail.mailpen.net [66.235.70.248])
	by svr1.postgresql.org (Postfix) with ESMTP id D1D1BDBAE2
	for <pgsql-sql@postgresql.org>; Fri, 18 Nov 2005 17:16:21 -0400 (AST)
Received: from [10.0.1.38] (64-1-201-228.daf.concentric.net [64.1.201.228])
	(using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits))
	(No client certificate requested)
	by mail.mailpen.net (Postfix) with ESMTP id 9930B18
	for <pgsql-sql@postgresql.org>; Fri, 18 Nov 2005 13:16:22 -0800 (PST)
Message-ID: <437E44A5.508@ultimeth.com>
Date: Fri, 18 Nov 2005 13:16:21 -0800
From: "Dean Gibson (DB Administrator)" <postgresql4@ultimeth.com>
Organization: UltiMeth Systems
User-Agent: Thunderbird 1.5 (Windows/20051025)
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Re: Export to XML
References: <CGEJILOBMIAEJLIJBGFFMEPLCDAA.murray_warren@bah.com>
In-Reply-To: <CGEJILOBMIAEJLIJBGFFMEPLCDAA.murray_warren@bah.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0 required=5 tests=[none]
X-Spam-Score: 0
X-Spam-Level: 
X-Archive-Number: 200511/152
X-Sequence-Number: 23403

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