Altova XMLSpy 2024 Enterprise Edition

Create XML Schema from DB Structure

Home Prev Top Next
icCreateSchemaFromDBStructure

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. For instructions, see Connecting to a Database.

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

 

Select the data structure to import

After having established a connection to the database, the Create XML Schema from DB Structure dialog is displayed (screenshot below). Here  you can select the database structure to import. After doing this, click Import to create an XML Schema  that defines this structure for an XML document.

Click to expand/collapse

 

Data selection method

In the Selection tab (screenshot above), the Source pane (screenshot below) displays either a representation of the tables of the database or an editable SQL statement for selecting the required tables, each view being selected by clicking the respective radio button.

ImportDBDataTableSelectionSQL

 

Table selection options

In the Table selection view, you can select the database tables to import by checking the table's check box (see the Table Selection screenshot above). The contents of the table can then be displayed in the Preview pane by clicking the Preview button.

 

The table selection view provides selection commands via icons in a toolbar (screenshot below).

ImportDBDataTableOptions

These icons are, from left:

 

Folders Layout: which enables you to organize database objects into: (i) folders based on object type; (ii) folders based on object type, but without schema folders; (iii) in a hierarchy, but without folders; and (iv) categories of tables, based on their relationships with other tables.

Filter folder contents: applies a filter to the selected folder, enabling the folder's objects to be filtered. For example, in the screenshot below, a filter has been applied to display tables that contain the text SysNav in its name. Clicking the icon pops up a menu with a list of filter possibilities.

Convert_TableSelectionFilters

Show favorites: Filters the objects displayed to favorites.

Show checked objects only: Filter the objects displayed to checked objects.

Object Locator: Displays a text field which behaves like a Search entry field. You can enter a text string and the dropdown list will display all the objects with names that contain the text string. Selecting one of these objects from the dropdown  list will highlight that object in the tree.

 

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.

ImportDBDataStructureForXSDOptions

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 www.iso.org.

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 Preview button in the Preview pane to display the table. Click the Reload button to refresh the preview.

ImportDBDataPreview

When the records are imported, each field can be imported as either an element or an attribute of the record. Alternatively, you can choose to not import a field. To specify whether a field is to be imported as an element or attribute or not imported at all, click the symbol to the left of the column name. Repeated clicks will cycle you through the three options. In the screenshot above, for example, the Book_ID field has been set to be imported as an attribute, the NumPages field to not be imported, and all other fields to be imported as elements.

 

Datatype conversions

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

 

© 2017-2023 Altova GmbH