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 - <[EMAIL PROTECTED]>: > 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" <[EMAIL PROTECTED]> > >To: <mysql@lists.mysql.com> > >CC: <mysql@lists.mysql.com> > >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 [EMAIL PROTECTED]; run by ezmlm > >List-ID: <mysql.mysql.com> > >Precedence: bulk > >List-Help: <mailto:[EMAIL PROTECTED]> > >List-Unsubscribe: > ><mailto:[EMAIL PROTECTED]> > >List-Post: <mailto:mysql@lists.mysql.com> > >List-Archive: http://lists.mysql.com/mysql/183030 > >Delivered-To: mailing list mysql@lists.mysql.com > >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: [EMAIL PROTECTED] > >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/[EMAIL PROTECTED] > > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
