5

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?

4

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

| improve this answer | |
  • Very interesting. Will definitely try this. Thank you! – Eduard Luca Nov 27 '12 at 22:01
  • but this isn´t a DTD is an schema .. is there any way to generate the DTD directly? – user21710 Mar 25 '13 at 10:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.