Thread: XML?
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 btj@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." -----------------------------------------------------------------------------------------------
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("&", "&", $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
>btj@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
>
>
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("&", "&", $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
>>btj@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
>>
>>
Add an
echo $returnData
right after the pg_Close or it wont output the data... in response to
your followup, I doubt it would be hard to implement something similar
in java, it's just using the standard pg api to build the xml.
Gavin
Gavin M. Roy wrote:
> 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("&", "&", $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
>> btj@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
>
What would be really cool (although a lot harder to implement) would be
the ability to generate a hierarchical XML document when using foreign key
relationships. Trying to tell PG how to format that might be a bit of an
issue, though.
Jon
On Thu, 7 Aug 2003, Gavin M. Roy wrote:
> Add an
> echo $returnData
> right after the pg_Close or it wont output the data... in response to
> your followup, I doubt it would be hard to implement something similar
> in java, it's just using the standard pg api to build the xml.
>
> Gavin
>
> Gavin M. Roy wrote:
>
> > 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("&", "&", $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
> >> btj@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
> >
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
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("&", "&", $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
>>>btj@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
>
>
On Fri, 8 Aug 2003 07:07:42 +0200 (CEST) "Bjorn T Johansen" <btj@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 > btj@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 (pablo@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