Create DB Structure from XML Schema
XMLSpy allows you to create an empty database (or skeleton database) based on an existing schema file. The method described below is generally the same for each type of database.
| 1. | Open the schema file in Schema/WSDL View |
| 2. | Select the menu command Convert | Create DB Structure from XML Schema. This pops up the Connect to a Data Source dialog, which enables you to connect to a database (DB). |
| 3. | Use the steps described in the section Connecting to a Data Source to connect to the required database. For example, to connect to a Microsoft Access database, select the Microsoft Access radio button, and continue the process to select a database. You can use an existing database or create a new database in which the schema structure will be contained. |
| 4. | In the Create DB Structure from XML Schema dialog, tables are created from the schema and displayed in a tree format at the location where they will occur in the DB. For example, in the screenshot below, the Address table is created and selected for export. Tables that should not be exported should be deselected (by unchecking the check box or selecting the appropriate item from the context menu for that table). |
Creating DB tables with relationships
If the XML Schema from which the DB structure is generated has relationships defined in the form of identity constraints, then these relationships are automatically created in the generated DB structure and displayed in the Table Structure. Tables with relationships are listed under the sections: Tables with ForeignKeys and Tables used by ForeignKeys. Tables without relationships are listed in the Independent Tables section.
In the Relationships tab, you can create and modify table relationships. The tab lists all possible primary-key/foreign-key relationships (screenshot below).
To create a relationship, do the following:
1.Select one of the possible primary-key/foreign-key relationships.
2.In the lower pane of the dialog, click the Plus button to create a relationship.
3.Select the required columns in each of the two tables from the respective dropdown lists.
You can also remove a relationship by selecting it and then clicking the Minus button.
Notes on database structure and connecting
The schema structure, defined by the identity constraints, is mirrored in the resulting database. The table below shows the type of database created, the restrictions, and the connecting methods, when using the Create DB Structure from XML Schema menu command.
Directly | Using ODBC | Using ADO | |
MS Access (2000 and 2003) | OK * | OK | OK |
MS SQL Server | OK * | OK | OK |
Oracle | OK * | OK | OK |
MySQL | - | OK * | OK + |
Sybase | - | OK * | OK |
IBM DB2 | - | OK * | OK |
* Recommended connection method for each database.
+ MySQL: When creating the ADO connection based on ODBC, it is recommended to use either the User or System DSN.
- Not supported
XMLSpy will map both hierarchical and flat formatted schemas. XMLSpy recognizes both formats automatically.
The flat format is mapped to SQL in two different ways.
•SQL Server DB, Oracle DB, or Sybase DB:
A schema that was generated in flat format, for one of the above databases, will have the schema catalog name extracted and used in the generated SQL script as the DB name. This means that the resulting SQL script will be executed on a target DB whose name must be identical to the schema catalog name.
•Access (2000 or 2003), MySQL, or DB2 DB:
A schema that was generated in flat format, for one of the above databases, will ignore the schema catalog name when the SQL script is generated. This means that the resulting SQL script will be executed on a target database that was logged into.
Datatype conversions
Information about the conversion of XML Schema datatypes to database datatypes is listed in the Appendices.