Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 4 down vote accepted

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

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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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