From: Date: April 24 2005 9:39pm
Subject: RE: MYSQL to XML
List-Archive: http://lists.mysql.com/mysql/183030
Message-Id: <001601c54905$4c29d040$0100a8c0@redfox>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit

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
>>