Home / Database & Server / Database & Server / Creare XML da MySQL
Mattepuffo

Creare XML da MySQL

Creare XML da MySQL

MySQL incorpora delle opzioni per eseguire delle query e rappresentare in formato XML i risultati estrapolati.

Ovviamente se deve essere fatto su un sito web è meglio farlo usando un linguaggio lato server (PHP, Asp.NET, ecc).

Però questo può comunque risultare utile.

La sintassi generale è questa:

$ mysql -u utente --xml -e 'SELECT* FROM db.table' -p

In pratica è il classico comando di login al quale però aggiungiamo due flag (--xml e -e) e la query specificando però anche il db della tabella da interrogare.

Eccovi un esempio concreto:

$ mysql -u root --xml -e 'SELECT * FROM library.bookv' -p
Enter password:
<?xml version="1.0"?>

<resultset statement="select * from library.bookv
" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<row>
<field name="book_id">10</field>
<field name="title">siddharta</field>
<field name="author_name">herman hesse</field>
<field name="editor_name">adelphi</field>
<field name="price">7.5</field>
<field name="isbn">88-459-0184-x</field>
<field name="note"></field>
</row>

<row>
<field name="book_id">1</field>
<field name="title">hacker 6.0</field>
<field name="author_name">mcclure,scambray,kurtz</field>
<field name="editor_name">apogeo</field>
<field name="price">0</field>
<field name="isbn" xsi:nil="true" />
<field name="note" xsi:nil="true" />
</row>

<row>
<field name="book_id">6</field>
<field name="title">sviluppare applicazioni web 2.0 con php</field>
<field name="author_name">quentin zervaas</field>
<field name="editor_name">apogeo</field>
<field name="price">42</field>
<field name="isbn">978-88-503-2772-0</field>
<field name="note"></field>
</row>

<row>
<field name="book_id">7</field>
<field name="title">c++ fondamenti di programmazione</field>
<field name="author_name">deitel</field>
<field name="editor_name">apogeo</field>
<field name="price">45</field>
<field name="isbn">88-503-2386-7</field>
<field name="note"></field>
</row>

<row>
<field name="book_id">2</field>
<field name="title">odissea</field>
<field name="author_name">clive cussler</field>
<field name="editor_name">longanesi</field>
<field name="price">0</field>
<field name="isbn"></field>
<field name="note"></field>
</row>

.......

</resultset>

Se vogliamo salvare l'output su un file abbiamo due possibillità.

La prima è questa:

$ mysql -u root --xml -e 'select * from library.bookv' -p > mysql.xml
Enter password:
$ cat mysql.xml
<?xml version="1.0"?>

<resultset statement="select * from library.bookv
" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<row>
<field name="book_id">10</field>
<field name="title">siddharta</field>
<field name="author_name">herman hesse</field>
<field name="editor_name">adelphi</field>
<field name="price">7.5</field>
<field name="isbn">88-459-0184-x</field>
<field name="note"></field>
</row>

<row>
<field name="book_id">1</field>
<field name="title">hacker 6.0</field>
<field name="author_name">mcclure,scambray,kurtz</field>
<field name="editor_name">apogeo</field>
<field name="price">0</field>
<field name="isbn" xsi:nil="true" />
<field name="note" xsi:nil="true" />
</row>

<row>
<field name="book_id">6</field>
<field name="title">sviluppare applicazioni web 2.0 con php</field>
<field name="author_name">quentin zervaas</field>
<field name="editor_name">apogeo</field>
<field name="price">42</field>
<field name="isbn">978-88-503-2772-0</field>
<field name="note"></field>
</row>

.....

</resultset>

Il secondo modo consiste nell'usare mysqldump con questa sintassi:

$ mysqldump --xml -u root -p library book > bookv.xml
Enter password:
casa@casa ~$ cat bookv.xml
<?xml version="1.0"?>
<mysqldump xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<database name="library">
<table_structure name="book">
<field Field="book_id" Type="int(11)" Null="NO" Key="PRI" Extra="auto_increment" Comment="" />
<field Field="title" Type="varchar(100)" Null="NO" Key="UNI" Extra="" Comment="" />
<field Field="author_id" Type="int(11)" Null="NO" Key="MUL" Extra="" Comment="" />
<field Field="editor_id" Type="int(11)" Null="NO" Key="MUL" Extra="" Comment="" />
<field Field="price" Type="double" Null="YES" Key="" Default="0" Extra="" Comment="" />
<field Field="isbn" Type="varchar(50)" Null="YES" Key="" Extra="" Comment="" />
<field Field="note" Type="text" Null="YES" Key="" Extra="" Comment="" />
<key Table="book" Non_unique="0" Key_name="PRIMARY" Seq_in_index="1" Column_name="book_id" Collation="A" Cardinality="10" Null="" Index_type="BTREE" Comment="" Index_comment="" />
<key Table="book" Non_unique="0" Key_name="title" Seq_in_index="1" Column_name="title" Collation="A" Cardinality="10" Null="" Index_type="BTREE" Comment="" Index_comment="" />
<key Table="book" Non_unique="1" Key_name="editor_ix" Seq_in_index="1" Column_name="editor_id" Collation="A" Cardinality="10" Null="" Index_type="BTREE" Comment="" Index_comment="" />
<key Table="book" Non_unique="1" Key_name="author_ix" Seq_in_index="1" Column_name="author_id" Collation="A" Cardinality="10" Null="" Index_type="BTREE" Comment="" Index_comment="" />
<options Name="book" Engine="InnoDB" Version="10" Row_format="Compact" Rows="10" Avg_row_length="1638" Data_length="16384" Max_data_length="0" Index_length="49152" Data_free="9437184" Auto_increment="11" Create_time="2011-04-18 10:07:41" Collation="utf8_general_ci" Create_options="" Comment="" />
</table_structure>
<table_data name="book">
<row>
<field name="book_id">1</field>
<field name="title">hacker 6.0</field>
<field name="author_id">1</field>
<field name="editor_id">1</field>
<field name="price">0</field>
<field name="isbn" xsi:nil="true" />
<field name="note" xsi:nil="true" />
</row>
<row>
<field name="book_id">2</field>
<field name="title">odissea</field>
<field name="author_id">2</field>
<field name="editor_id">2</field>
<field name="price">0</field>
<field name="isbn"></field>
<field name="note"></field>
</row>
<row>
<field name="book_id">3</field>
<field name="title">c# e .net 4</field>
<field name="author_id">6</field>
<field name="editor_id">4</field>
<field name="price">69.9</field>
<field name="isbn"></field>
<field name="note"></field>
</row>
<row>
<field name="book_id">4</field>
<field name="title">walhalla</field>
<field name="author_id">2</field>
<field name="editor_id">2</field>
<field name="price">0</field>
<field name="isbn"></field>
<field name="note"></field>
</row>

.....

</resultset>

Come vedete l'output è un pò diverso.

Inoltre mentre nel primo ho interrogato una vista, nel secondo devo specificare una tabella.

Direi che il primo modo mi piace di più......