By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,514 Members | 794 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,514 IT Pros & Developers. It's quick & easy.

XML?

P: n/a
I need to convert recordsets to XML, is there an automatic way to do this
in PostgreSQL or a tool I can use? Or do I have to code this manually?
Regards,

BTJ
-----------------------------------------------------------------------------------------------
Bjørn T Johansen (BSc,MNIF)
Executive Manager
bt*@havleik.no Havleik Consulting
Phone : +47 67 54 15 17 Conradisvei 4
Fax : +47 67 54 13 91 N-1338 Sandvika
Cellular : +47 926 93 298 http://www.havleik.no
-----------------------------------------------------------------------------------------------
"The stickers on the side of the box said "Supported Platforms: Windows
98, Windows NT 4.0,
Windows 2000 or better", so clearly Linux was a supported platform."
-----------------------------------------------------------------------------------------------
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 11 '05 #1
Share this Question
Share on Google+
4 Replies

P: n/a
Here's some PHP code I use to do just that:

<xml version="1.0">
<?php
function returnRecord($resultid, $row, $level) {
$prepend = "";
for ( $y = 0; $y < $level; $y++ )
$prepend .= "\t";

$record = $prepend . "<RECORD>\n";
for ( $y = 0; $y < pg_NumFields($resultid); $y++ ) {
$record .= $prepend . "\t<" . pg_FieldName($resultid, $y) . ">";
$data = Trim(pg_Result($resultid, $row, $y));
$data = ereg_replace("&", "&amp;", $data);
$record .= $data;
$record .= "</" . pg_FieldName($resultid, $y) . ">\n";
}
$record .= $prepend . "</RECORD>\n";
return $record;
}

$conn = pg_Connect("my database connect string");
$result = pg_Query($conn, "SELECT * FROM MY_TABLE;");
$returnData = "";
$rows = pg_NumRows($result);
for ( $y = 0; $y < $rows; $y++ )
$returnData .= returnRecord($result, $y, 1);
pg_FreeResult($result);
pg_Close($conn);
?>
</xml>

Hope this helps,

Gavin

Bjorn T Johansen wrote:
I need to convert recordsets to XML, is there an automatic way to do this
in PostgreSQL or a tool I can use? Or do I have to code this manually?
Regards,

BTJ
-----------------------------------------------------------------------------------------------
Bjørn T Johansen (BSc,MNIF)
Executive Manager
bt*@havleik.no Havleik Consulting
Phone : +47 67 54 15 17 Conradisvei 4
Fax : +47 67 54 13 91 N-1338 Sandvika
Cellular : +47 926 93 298 http://www.havleik.no
-----------------------------------------------------------------------------------------------
"The stickers on the side of the box said "Supported Platforms: Windows
98, Windows NT 4.0,
Windows 2000 or better", so clearly Linux was a supported platform."
-----------------------------------------------------------------------------------------------
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster



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

Nov 11 '05 #2

P: n/a
Well, it helps a little (I don't have to think that much on how to
implement the code, which is java btw... :) )
But I was kinda hoping for a feature like the one that Oracle has, where I
can tell Oracle that I want the resultset from the query to be returned as
XML. (If this is not a feature in PostgreSQL maybe it should be?)
BTJ
Here's some PHP code I use to do just that:

<xml version="1.0">
<?php
function returnRecord($resultid, $row, $level) {
$prepend = "";
for ( $y = 0; $y < $level; $y++ )
$prepend .= "\t";

$record = $prepend . "<RECORD>\n";
for ( $y = 0; $y < pg_NumFields($resultid); $y++ ) {
$record .= $prepend . "\t<" . pg_FieldName($resultid, $y) . ">";
$data = Trim(pg_Result($resultid, $row, $y));
$data = ereg_replace("&", "&amp;", $data);
$record .= $data;
$record .= "</" . pg_FieldName($resultid, $y) . ">\n";
}
$record .= $prepend . "</RECORD>\n";
return $record;
}

$conn = pg_Connect("my database connect string");
$result = pg_Query($conn, "SELECT * FROM MY_TABLE;");
$returnData = "";
$rows = pg_NumRows($result);
for ( $y = 0; $y < $rows; $y++ )
$returnData .= returnRecord($result, $y, 1);
pg_FreeResult($result);
pg_Close($conn);
?>
</xml>

Hope this helps,

Gavin

Bjorn T Johansen wrote:
I need to convert recordsets to XML, is there an automatic way to do this
in PostgreSQL or a tool I can use? Or do I have to code this manually?
Regards,

BTJ
-----------------------------------------------------------------------------------------------
Bjørn T Johansen (BSc,MNIF)
Executive Manager
bt*@havleik.no Havleik Consulting
Phone : +47 67 54 15 17 Conradisvei 4
Fax : +47 67 54 13 91 N-1338 Sandvika
Cellular : +47 926 93 298 http://www.havleik.no
-----------------------------------------------------------------------------------------------
"The stickers on the side of the box said "Supported Platforms: Windows
98, Windows NT 4.0,
Windows 2000 or better", so clearly Linux was a supported platform."
-----------------------------------------------------------------------------------------------
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 11 '05 #3

P: n/a
It might not help in this case anyway, but it seems like the psql \H
(toggle html output) could be a starting point* for a \X (toggle XML
output) to provide a minimal XML implementation for output.

* For someone smarter than me.

Bjorn T Johansen wrote:
Well, it helps a little (I don't have to think that much on how to
implement the code, which is java btw... :) )
But I was kinda hoping for a feature like the one that Oracle has, where I
can tell Oracle that I want the resultset from the query to be returned as
XML. (If this is not a feature in PostgreSQL maybe it should be?)
BTJ
Here's some PHP code I use to do just that:

<xml version="1.0">
<?php
function returnRecord($resultid, $row, $level) {
$prepend = "";
for ( $y = 0; $y < $level; $y++ )
$prepend .= "\t";

$record = $prepend . "<RECORD>\n";
for ( $y = 0; $y < pg_NumFields($resultid); $y++ ) {
$record .= $prepend . "\t<" . pg_FieldName($resultid, $y) . ">";
$data = Trim(pg_Result($resultid, $row, $y));
$data = ereg_replace("&", "&amp;", $data);
$record .= $data;
$record .= "</" . pg_FieldName($resultid, $y) . ">\n";
}
$record .= $prepend . "</RECORD>\n";
return $record;
}

$conn = pg_Connect("my database connect string");
$result = pg_Query($conn, "SELECT * FROM MY_TABLE;");
$returnData = "";
$rows = pg_NumRows($result);
for ( $y = 0; $y < $rows; $y++ )
$returnData .= returnRecord($result, $y, 1);
pg_FreeResult($result);
pg_Close($conn);
?>
</xml>

Hope this helps,

Gavin

Bjorn T Johansen wrote:
I need to convert recordsets to XML, is there an automatic way to do this
in PostgreSQL or a tool I can use? Or do I have to code this manually?
Regards,

BTJ
-----------------------------------------------------------------------------------------------
Bjørn T Johansen (BSc,MNIF)
Executive Manager
bt*@havleik.no Havleik Consulting
Phone : +47 67 54 15 17 Conradisvei 4
Fax : +47 67 54 13 91 N-1338 Sandvika
Cellular : +47 926 93 298 http://www.havleik.no
-----------------------------------------------------------------------------------------------
"The stickers on the side of the box said "Supported Platforms: Windows
98, Windows NT 4.0,
Windows 2000 or better", so clearly Linux was a supported platform."
-----------------------------------------------------------------------------------------------
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster


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

Nov 11 '05 #4

P: n/a
On Fri, 8 Aug 2003 07:07:42 +0200 (CEST)
"Bjorn T Johansen" <bt*@havleik.no> wrote:
I need to convert recordsets to XML, is there an automatic way to do this
in PostgreSQL or a tool I can use? Or do I have to code this manually?
Agata Report (agata.codigolivre.org.br) does that.

Pablo


Regards,

BTJ
-----------------------------------------------------------------------------------------------
Bjørn T Johansen (BSc,MNIF)
Executive Manager
bt*@havleik.no Havleik Consulting
Phone : +47 67 54 15 17 Conradisvei 4
Fax : +47 67 54 13 91 N-1338 Sandvika
Cellular : +47 926 93 298 http://www.havleik.no
-----------------------------------------------------------------------------------------------
"The stickers on the side of the box said "Supported Platforms: Windows
98, Windows NT 4.0,
Windows 2000 or better", so clearly Linux was a supported platform."
-----------------------------------------------------------------------------------------------
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

:: Pablo Dall'Oglio (pa***@php.net) +55 (51) 3714-7040
:: Solis - Cooperativa de Solucoes Livres
:: www.solis.coop.br - Lajeado, RS - Brasil
:: www.varianet.com.br (personal)
:: "Life's a Journey, Not a Destination" - Steven Tyler

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 11 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.