Create XML Schema from DB Structure
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.
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.
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).
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.
•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.
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.
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.