MySQL ODBC Connector - MySQL to XML

Author

MySQL to XML

Mikel -

2005-04-22, 8:23 pm

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

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

mathias fatene

2005-04-24, 8:23 pm

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=localhos
t";

my $xmlout = DBIx::XML_RDB-> new($dsn,'mysql',$us
erid, $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=w
orld;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=localhos
t";

my $dbh = DBI-> connect($dsn,$userid
, $password,{'RaiseErr
or' => 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','O
ceania','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

[color=darkred]
format:[color=darkre
d]
size="255"/>[color=darkred]
onDelete="none">[color=darkred]
you >> can[color=darkred]

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Mikel -

2005-04-25, 9:24 am

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@free.fr>
>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: JGTYoYF78jEQFMtosA6G
PW/w+/WF28t94KBGDmreITY=
>Mailing-List: contact mysql-help@lists.mysql.com; run by ezmlm
>List-ID: <mysql.mysql.com>
>Precedence: bulk
>List-Help: <mailto:mysql-help@lists.mysql.com>
>List-Unsubscribe:
><mailto:mysql-unsubscribe-ironmitss=hotmail.com@lists.mysql.com>
>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: mysql-return-183030-ironmitss=hotmail.com@lists.mysql.com
>X- OriginalArrivalTime:
24 Apr 2005 19:42:26.0285 (UTC)
> FILETIME=[BE1839D0:0
1C54905]
>
>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=localhos
t";
>
> my $xmlout = DBIx::XML_RDB-> new($dsn,'mysql',$us
erid, $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=w
orld;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=localhos
t";
>
> my $dbh = DBI-> connect($dsn,$userid
, $password,{'RaiseErr
or' => 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','O
ceania','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
>
>
>
>format:
>size="255"/>
>onDelete="none">
>you >> can
>
>
>
>
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe: http://lists.mysql.com/mysql? unsub...m
ail.com
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

mfatene@free.fr

2005-04-25, 11:23 am

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@hotmail.com>:

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

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Mikel -

2005-04-26, 8:23 pm

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@free.fr
>To: Mikel - <ironmitss@hotmail.com>
>CC: mysql@lists.mysql.com
>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@lists.mysql.com; run by ezmlm
>List-ID: <mysql.mysql.com>
>Precedence: bulk
>List-Help: <mailto:mysql-help@lists.mysql.com>
>List-Unsubscribe:
><mailto:mysql-unsubscribe-ironmitss=hotmail.com@lists.mysql.com>
>List-Post: <mailto:mysql@lists.mysql.com>
>List-Archive: http://lists.mysql.com/mysql/183082
>Delivered-To: mailing list mysql@lists.mysql.com
>References: <BAY9- F18965890035AE880561
2CCA5200@phx.gbl>
>User-Agent: Internet Messaging Program (IMP) 3.2.5
>X-Virus-Checked: Checked
>Return-Path: mysql-return-183082-ironmitss=hotmail.com@lists.mysql.com
>X- OriginalArrivalTime:
25 Apr 2005 15:44:24.0943 (UTC)
> FILETIME=[A82A1FF0:0
1C549AD]
>
>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@hotmail.com>:
>
>program
>foreign
>table"
>help
>-0000
>die
>your
>followin
>that
>http://lists.mysql.com/mysql? unsub...m
ail.com
>
>
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe: http://lists.mysql.com/mysql? unsub...m
ail.com
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Sponsored Links