RE: MYSQL to XML

[Mikel -]

Thanx Mathias for your quick and effective response, I see that your program 
almost display the format that I need, the thing is that I need the foreign 
key information too, Does MySQL have a statement besides "show create table" 
to display this information?....Thanks again for your suggestions and help

Greetings

>From: "mathias fatene" <mfatene@stripped>
>To: <mysql@stripped>
>CC: <mysql@stripped>
>Subject: RE: MYSQL to XML
>Date: Sun, 24 Apr 2005 21:39:14 +0200
>MIME-Version: 1.0
>Received: from lists.mysql.com ([213.136.52.31]) by mc3-f23.hotmail.com 
>with Microsoft SMTPSVC(6.0.3790.211); Sun, 24 Apr 2005 12:42:26 -0700
>Received: (qmail 15912 invoked by uid 109); 24 Apr 2005 19:40:50 -0000
>Received: (qmail 15893 invoked from network); 24 Apr 2005 19:40:50 -0000
>Received: pass (lists.mysql.com: local policy)
>X-Message-Info: JGTYoYF78jEQFMtosA6GPW/w+/WF28t94KBGDmreITY=
>Mailing-List: contact mysql-help@stripped; run by ezmlm
>List-ID: <mysql.mysql.com>
>Precedence: bulk
>List-Help: <mailto:mysql-help@stripped>
>List-Unsubscribe: 
><mailto:mysql-unsubscribe-ironmitss=hotmail.com@stripped>
>List-Post: <mailto:mysql@stripped>
>List-Archive: http://lists.mysql.com/mysql/183030
>Delivered-To: mailing list mysql@stripped
>X-MSMail-Priority: Normal
>X-Mailer: Microsoft Outlook, Build 10.0.2616
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
>X-Virus-Checked: Checked
>Return-Path: mysql-return-183030-ironmitss=hotmail.com@stripped
>X-OriginalArrivalTime: 24 Apr 2005 19:42:26.0285 (UTC) 
>FILETIME=[BE1839D0:01C54905]
>
>Hi Mikel,
>There are a lot of possibilities including commercial (:o)) products.
>I suggest you those solutions. The output should be reparsed for your
>needs :
>1. the -X on client :
>     C:\Mysql>mysql -u mathias world -X -e "desc country"
>     <?xml version="1.0"?>
>
>     <resultset statement="desc country">
>       <row>
>         <Field>Code</Field>
>         <Type>char(3)</Type>
>         <Null></Null>
>         <Key>PRI</Key>
>         <Default></Default>
>         <Extra></Extra>
>       </row>
>       <row>
>             <Field>Name</Field>
>             <Type>char(52)</Type>
>             <Null></Null>
>             <Key></Key>
>             <Default></Default>
>             <Extra></Extra>
>       </row>
>
>       <row>
>             <Field>Continent</Field>
>...
>...
>
>2. install perl DBI and DBIx-XML_RDB modules :
>#!perl -w
># ---------------------------------------------------------------
># Describe2xml
># Author : Mathias FATENE
># Date   : 24 april 2005
># ---------------------------------------------------------------
>use DBIx::XML_RDB;
>
>  my $userid='root';
>  my $password='**************';
>  my $dbname='world';
>  my $dsn = "DBI:mysql:database=$dbname;host=localhost";
>
>  my $xmlout = DBIx::XML_RDB->new($dsn,'mysql',$userid, $password) || die
>"Failed to make new xmlout";
>
>   $xmlout->DoSql("describe country");
>   print $xmlout->GetData;
>
>C:\Mysql>perl describe.pl
><?xml version="1.0"?>
><DBI driver="DBI:mysql:database=world;host=localhost">
>         <RESULTSET statement="describe country">
>                 <ROW>
>                         <Field>Code</Field>
>                         <Type>char(3)</Type>
>                         <Null></Null>
>                         <Key>PRI</Key>
>                         <Default></Default>
>                         <Extra></Extra>
>                 </ROW>
>                 <ROW>
>                         <Field>Name</Field>
>                         <Type>char(52)</Type>
>                         <Null></Null>
>                         <Key></Key>
>                         <Default></Default>
>                         <Extra></Extra>
>                 </ROW>
>                 <ROW>
>...
>...
>3. install Perl DBI and DBD-Mysql and use my program (formatted for your
>needs) :
>#!perl -w
># ---------------------------------------------------------------
># Describe2xml
># Author : Mathias FATENE
># Date   : April, 24 2005
># ---------------------------------------------------------------
>use DBI;
>
>  my $userid='root';
>  my $password='************';
>  my $dbname='world';
>  my $dsn = "DBI:mysql:database=$dbname;host=localhost";
>
>  my $dbh = DBI->connect($dsn,$userid, $password,{'RaiseError' => 1});
>   # ---------------------------------------------------------------
>   # describe country table and print it in XML format
>   # ---------------------------------------------------------------
>   my $table="country";
>   $sth = $dbh->prepare("describe $table");
>   $sth->execute();
>
>   print "\<table name=\"$table\"\>\n";
>   while (my @ref = $sth->fetchrow_array()) {
>        print "\<column name=\"$ref[0]\" required=\"true\"
>type=\"$ref[1]\"";
>        print " primaryKey=\"true\"" if ($ref[3] eq "PRI") ;
>        print "/\>\n";
>   }
>   $sth->finish();
>   print "\</table\>\n";
>
>   # Disconnect from the database.
>   $dbh->disconnect();
>
>C:\Mysql>perl desc.pl country
><table name="country">
><column name="Code" required="true" type="char(3)" primaryKey="true"/>
><column name="Name" required="true" type="char(52)"/>
><column name="Continent" required="true"
>type="enum('Asia','Europe','North
>America','Africa','Oceania','Antarctica','South America')"/>
><column name="Region" required="true" type="char(26)"/>
><column name="SurfaceArea" required="true" type="float(10,2)"/>
><column name="IndepYear" required="true" type="smallint(6)"/>
><column name="Population" required="true" type="int(11)"/>
><column name="LifeExpectancy" required="true" type="float(3,1)"/>
><column name="GNP" required="true" type="float(10,2)"/>
><column name="GNPOld" required="true" type="float(10,2)"/>
><column name="LocalName" required="true" type="char(45)"/>
><column name="GovernmentForm" required="true" type="char(45)"/>
><column name="HeadOfState" required="true" type="char(60)"/>
><column name="Capital" required="true" type="int(11)"/>
><column name="Code2" required="true" type="char(2)"/>
></table>
>
>is this beautifull ?
>
>I will modify Describe2xml.pl to be more parametrized (user, db, pass,
>FK, ...) as soon as possible.
>
>Mathias
>
>
>
> >>  Hi list, does it possible for MySQL to generate XML in the followin
>format:
> >>
> >>  <table name="ServiceType">
> >>      <column name="idTipoServicio" primaryKey="true"
> >>              required="true" type="VARCHAR" size="10"/>
> >>      <column name="nombre" required="true" type="VARCHAR"
>size="255"/>
> >>      <column name="costo" required="true" type="FLOAT" size="9"/>
> >>      <column name="idGrupo" required="true" type="INTEGER"/>
> >>      <column name="activa" required="true" type="BOOLEANINT"/>
> >>
> >>      <foreign-key foreignTable="Grupo" onUpdate="none"
>onDelete="none">
> >>          <reference foreign="idGrupo" local="idGrupo"/>
> >>      </foreign-key>
> >>    </table>
> >>
> >>  This XML is the structure of the ServiceType table,  I'll hope that
>you >>   can
> >>  help me
> >>
> >>  Thnx in advanced
> >>
> >>  Greetings
> >>
> >>  P.S. Any suggestions (tools) will be appreciated
> >>
> >>
> >>
> >>  Thread
> >>
> >>      * MySQL to XML - Mikel -, April 23 2005 1:07am
> >>
>
>
>
>
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>