Thread: Export to XML

What is the process to export a PostgreSQL table to XML?  How is it done?
Thanks!

Warren L Murray
Booz Allen Hamilton
Phone: 404.518.7940
Email: murray_warren@bah.com

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

You can try to use this patch:
http://archives.postgresql.org/pgsql-patches/2005-09/msg00138.php

This is implementation of basic functions of SQL/XML (14th part of
SQL:2003, http://www.sqlx.org/)

I've successfully applied it to 8.1 version - no troubles.

On 11/11/05, Warren Murray <murray_warren@bah.com> wrote:
> What is the process to export a PostgreSQL table to XML?  How is it done?
> Thanks!
>
> Warren L Murray
> Booz Allen Hamilton
> Phone: 404.518.7940
> Email: murray_warren@bah.com
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>


--
Best regards,
Nikolay