RE: MYSQL to XML

[Mikel -]

Mathias
Thanks for your help, I really appreciated it. And I was just wondering if 
MySQL has another statment (besides show create table) that only displays 
the foreign key, but I see that only with the "show create table MyTable" 
could get this.

Greetings

>From: mfatene@stripped
>To: Mikel - <ironmitss@stripped>
>CC: mysql@stripped
>Subject: RE: MYSQL to XML
>Date: Mon, 25 Apr 2005 17:41:47 +0200
>MIME-Version: 1.0
>X-Originating-IP: 213.41.126.253
>Received: from lists.mysql.com ([213.136.52.31]) by mc5-f35.hotmail.com 
>with Microsoft SMTPSVC(6.0.3790.211); Mon, 25 Apr 2005 08:44:24 -0700
>Received: (qmail 21182 invoked by uid 109); 25 Apr 2005 15:41:56 -0000
>Received: (qmail 21160 invoked from network); 25 Apr 2005 15:41:55 -0000
>Received: pass (lists.mysql.com: local policy)
>X-Message-Info: JGTYoYF78jHafVH/hFUFqKtbfGVCQG07u9r/IzplGqc=
>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/183082
>Delivered-To: mailing list mysql@stripped
>References: <BAY9-F18965890035AE8805612CCA5200@stripped>
>User-Agent: Internet Messaging Program (IMP) 3.2.5
>X-Virus-Checked: Checked
>Return-Path: mysql-return-183082-ironmitss=hotmail.com@stripped
>X-OriginalArrivalTime: 25 Apr 2005 15:44:24.0943 (UTC) 
>FILETIME=[A82A1FF0:01C549AD]
>
>Hi Mikel,
>Show create table shows a line CONSTRAINT ... FORIEGN KEY ...
>
>you can add a grep on this line. But this will be difficult. You can 
>construct
>another desc2xml using just "show create table" to have it easier.
>
>Mathias
>
>Selon Mikel - <ironmitss@stripped>:
>
> > 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
> > >
> >
> >
> >
>
>
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>