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]
