SQL Server / MySQL to DTD (+XML)

Question

Is there any way I can at least export a table schema from MSSQL or MySQL to a DTD file? Also the same for the data of the tables to XML.

I am really not getting how to write DTDs, so I badly need a generator, and seeing how I have the DB set up, I'd say that's the easiest way?

Answer 1

You could try using MySQL's mysqldump utility.

There is an option to make mysqldump export in XML

[redwards@lw-lts-155 ~]$ mysqldump --help | grep xml
  -X, --xml           Dump a database as well formed XML.

In your case, you could dump the table mydb.mytable like this:

mysqldump --xml mydb mytable > mytable.xml

EXAMPLE

For the given table

mysql> show create table groupby\G
*************************** 1. row ***************************
       Table: groupby
Create Table: CREATE TABLE `groupby` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `num` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=17 DEFAULT CHARSET=latin1
1 row in set (0.01 sec)

mysql> select * from groupby;
+----+------------+
| id | num        |
+----+------------+
|  1 |  360370418 |
|  2 |  429187192 |
|  3 | 1064824747 |
|  4 | 1328623077 |
|  5 |  740702064 |
|  6 | 1071610692 |
|  7 |  428008187 |
|  8 |  279178462 |
|  9 |  360370418 |
| 10 |  429187192 |
| 11 | 1064824747 |
| 12 | 1328623077 |
| 13 |  740702064 |
| 14 | 1071610692 |
| 15 |  428008187 |
| 16 |  279178462 |
+----+------------+
16 rows in set (0.00 sec)

mysql>

just mysqldump to a textfile.

Here is what the XML actually looks like

<?xml version="1.0"?>
<mysqldump xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<database name="eggyal">
        <table_structure name="groupby">
                <field Field="id" Type="int(11)" Null="NO" Key="PRI" Extra="auto_increment" Comment="" />
                <field Field="num" Type="int(11)" Null="YES" Key="" Extra="" Comment="" />
                <key Table="groupby" Non_unique="0" Key_name="PRIMARY" Seq_in_index="1" Column_name="id" Collation="A" Cardinality="16" Null="" Index_type="BTREE" Comment="" Index_comment="" />
                <options Name="groupby" Engine="MyISAM" Version="10" Row_format="Fixed" Rows="16" Avg_row_length="9" Data_length="144" Max_data_length="2533274790395903" Index_length="2048" Data_free="0" Auto_increment="17" Create_time="2012-11-26 22:39:22" Update_time="2012-11-26 22:39:22" Collation="latin1_swedish_ci" Create_options="" Comment="" />
        </table_structure>
        <table_data name="groupby">
        <row>
                <field name="id">1</field>
                <field name="num">360370418</field>
        </row>
        <row>
                <field name="id">2</field>
                <field name="num">429187192</field>
        </row>
        <row>
                <field name="id">3</field>
                <field name="num">1064824747</field>
        </row>
        <row>
                <field name="id">4</field>
                <field name="num">1328623077</field>
        </row>
        <row>
                <field name="id">5</field>
                <field name="num">740702064</field>
        </row>
        <row>
                <field name="id">6</field>
                <field name="num">1071610692</field>
        </row>
        <row>
                <field name="id">7</field>
                <field name="num">428008187</field>
        </row>
        <row>
                <field name="id">8</field>
                <field name="num">279178462</field>
        </row>
        <row>
                <field name="id">9</field>
                <field name="num">360370418</field>
        </row>
        <row>
                <field name="id">10</field>
                <field name="num">429187192</field>
        </row>
        <row>
                <field name="id">11</field>
                <field name="num">1064824747</field>
        </row>
        <row>
                <field name="id">12</field>
                <field name="num">1328623077</field>
        </row>
        <row>
                <field name="id">13</field>
                <field name="num">740702064</field>
        </row>
        <row>
                <field name="id">14</field>
                <field name="num">1071610692</field>
        </row>
        <row>
                <field name="id">15</field>
                <field name="num">428008187</field>
        </row>
        <row>
                <field name="id">16</field>
                <field name="num">279178462</field>
        </row>
        </table_data>
</database>
</mysqldump>

As for the DTD itself, you should look around StackOverflow and ServerFault

You could also XML dump just the structure using --no-data option

mysqldump --xml --no-data mydb mytable > mytable.xml

which produces

<?xml version="1.0"?>
<mysqldump xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<database name="eggyal">
        <table_structure name="groupby">
                <field Field="id" Type="int(11)" Null="NO" Key="PRI" Extra="auto_increment" Comment="" />
                <field Field="num" Type="int(11)" Null="YES" Key="" Extra="" Comment="" />
                <key Table="groupby" Non_unique="0" Key_name="PRIMARY" Seq_in_index="1" Column_name="id" Collation="A" Cardinality="16" Null="" Index_type="BTREE" Comment="" Index_comment="" />
                <options Name="groupby" Engine="MyISAM" Version="10" Row_format="Fixed" Rows="16" Avg_row_length="9" Data_length="144" Max_data_length="2533274790395903" Index_length="2048" Data_free="0" Auto_increment="17" Create_time="2012-11-26 22:39:22" Update_time="2012-11-26 22:39:22" Collation="latin1_swedish_ci" Create_options="" Comment="" />
        </table_structure>
</database>
</mysqldump>

Take that output and scult it into a DTD