Altova XMLSpy 2022 Professional Edition

Create XML Schema from DB Structure

Home Prev Top Next

The Create XML Schema from DB Structure command enables you to create an XML Schema from the structure of any of a variety of databases. The XML Schema-creation mechanism involves two steps:


1.A connection to the database is established.

2.Options for the database data selection and the XML Schema are specified.


To create an XML Schema from a DB structure you would do the following:


1.When you click the Create XML Schema from DB Structure command, the Database Connection Wizard box appears.

2.Using the Database Connection Wizard, you establish a connection to the database (for instructions, see Connecting to a Database ).

3.After the connection to the database is established, the Create XML Schema from DB Structure dialog (screenshot below) displays tabs and windows that enable you to select the database structure to import. These options are described below. After finishing, click the Import button to import the database data.



Selection tab

In the Selection tab (screenshot below), the data source is listed in the Source Database pane. The Source pane displays either a representation of the tables of the database or an editable SQL statement for selecting the required tables.


In the Table view, you can select the tables in the database that you want to import by checking the table's check box (screenshot above). The contents of the table can then be displayed in the Preview pane. The table selection can be further filtered in the Preview pane (see below). You can configure the display in the Source pane as described in the section Import Database Data.


Options tab

In the Options tab (screenshot below), you can specify the format of the schema, its extension type, whether columns should be imported as elements or attributes, and the database constraints that should be generated in the schema.


Schema format: You can select between a flat (SQL/XML Standard) and a hierarchical schema form

The flat schema model is based on an ISO-ANSI SQL/XML specification INCITS/ISO/IEC 9075-14-2008. The SQL/XML specification defines how to map databases to XML. Relationships are defined in schemas using identity constraints; there are no references to elements. Hence the schema is flat structure which resembles a tree-like view of the database. The specification can be purchased at the ANSI store. For more information, see

The hierarchical schema model displays the table dependencies visually, in a type of tree view where dependent tables are shown as indented child elements in the content model. Table dependencies are also displayed in the Identity constraints tab.

Tables are listed as global elements in the schema, and columns are the elements or attributes of these global elements (The user decides whether to map the columns as elements or as attributes). Relationships are created in a hierarchical way so that a foreign key field in one table is actually a reference to the global element that represents that table.


Schema extension type: Schema extension information is additional information read from a database that is then embedded in the schema as either annotation data or attributes. There are four extension type options when generating schemas: (i) no extensions information; (ii) SQL/XML extensions; (iii) MS SQL Server extensions; and (iv) Oracle extensions. These are described below:


None: No additional information is provided by the database.

SQL XML: SQL/XML extensions are only inserted when generating schemas in a flat format. The extension information is stored in annotations and is described in the SQL/XML specification (INCITS/ISO/IEC 9075-14-2008).

MS SQL Server: Selecting Microsoft SQL Server, generates SQL Server extensions. See SQL Server Books Online for resources and MSDN's information about annotating XSD schemas. The following annotation-related elements are generated in the schema: sql:relation, sql:field, sql:datatype, sql:mapped.

Oracle: Oracle extensions are selected by default when working with an Oracle database. Additional database information is stored as attributes. Detailed information can be found in Oracle's online documentation. The following subset of attributes is currently generated: SQLName, SQLType, SQLSchema.


Note:Although SQL Server and Oracle extensions can be generated for their respective databases they are not restricted in this way. This proves useful when working with a third database and wanting to generate a schema that later should be working with either SQL Server or Oracle.


Preview pane

The Preview pane (screenshot below) displays the structure of the table currently selected in the Selection tab. When a new table is selected in the Selection tab, click the Reload button in the Preview pane to refresh the preview.


A field can be specified to be imported as an element or attribute, or not to be imported, by clicking the symbol to the left of the column name. You can click through the element, attribute, and ignore options. In the screenshot above, the city field, for example, has been set to be imported as an element  while the street field has been set to be imported as an attribute.


Datatype conversions

Information about the conversion of database datatypes to XML Schema datatypes is listed in the Appendices.


© 2016-2022 Altova GmbH