Import from database

Import table content as XML document

To import from a database, select File->Import->Database data Next, in the "Select database table" choose the driver and the URL for the database. You can edit, delete or add a new JDBC driver: click on the "Configure JDBC Driver" button (next to the Driver combo box) and the "Preferences" dialog will open at Database/JDBC Drivers. You'll also have to provide a username and a password. Click Connect.

Figure 9.2. Select database table Dialog

Select database table Dialog
Driver

Choose a driver from the list. To configure an existing driver or adding a new driver for accessing your database server go to the related Preferences panel.

URL

Specify the URL of the database table.

User

Provide a user for the database

Password

Provide a password

Stored sessions

If you want to save the current session (Driver, URL, User and Password) type a name in the text field and click Save. To load the data of a saved session select its name from the list and click on Load. A saved session can be removed from the list by selecting it and clicking on Delete.

From the catalogs list click on a schema and choose the required table. Click Ok.

The "Import criteria" Dialog will open next, showing a default Query string like "select * from table" in SQL Query. You can click the "SQL Preview" button to see the input data displayed in a tabular form and the XML Import Preview containing an example of what the generated XML will look like. The SQL Query message is editable: "*" selects all the fields from the chosen table. You can specify what fields should be taken into consideration: just replace "*" with the required fields, separated by comma.

Figure 9.3. Import from Database Criteria Dialog

Import from Database Criteria Dialog

If you edit the query string so that the query does a join of two or more tables and selects columns with the same name from different tables you should use an alias for the columns like in the following example. That will avoid a confusion of two columns mapped to the same name in the result document of the importing operation.

select s.subcat_id,
                      s.nr as s_nr,
                      s.name,
                      q.q_id,
                      q.nr as q_nr,
                      q.q_text
              from faq.subcategory s,
                      faq.question q
              where  ...
SQL Preview

Displays the labels that will be used in the XML document and its preview. Import setting: If the "SQL Preview" button is pressed, it shows the labels that will be used in the XML document and the first 5 lines from the database. All data items in the input will be converted by default to element content, but this can be over-ridden by clicking on the individual column headers. Clicking once on a column header (ex Heading0) will cause the data from this column to be used as attribute values on the row elements. Clicking a second time - the column's data will be ignored when generating the XML file. You can cycle through these three options by continuing to click on the column header. If the data column will be converted to element content, the header will contain the "<>" symbol. If the data column will be converted to attribute content, the header will contain the "=" symbol, and if it will be skipped, the header will contain "x".

Change labels

This button opens a new dialog, allowing you to edit the names of the root and row elements, change the XML name and the conversion criterion.

The XML names can be edited by double-clicking on the desired item and entering the required label. The conversion criterion can also be modified by selecting from the drop-down list ELEMENT, ATTRIBUTE or SKIPPED.

Save in file

If checked, the new XML document will be saved at the specified path.

Note

If only Open in editor is checked, the newly created document will be opened in the editor, but as an unsaved file.

Generate XML Schema

Allows you to specify the path of the generated XML Schema file.

Convert table structure to XML Schema

Figure 9.4. Select database table

Select database table

Next, in the "Select database table" choose the driver and the URL for the database. You can edit, delete or add a new JDBC driver: click on the "Configure JDBC Driver" button (next to the Driver combo box) and the "Preferences" dialog will open at Database/JDBC Drivers. You'll also have to provide a username and a password.

Driver

Choose a driver from the list.

URL

Specify the URL of the database table.

User

Provide a user for the database

Password

Provide a password

Stored sessions

If you want to save the current session (Driver, URL, User and Password) type a name in the text field and click Save. To load the data of a saved session select its name from the list and click on Load. A saved session can be removed from the list by selecting it and clicking on Delete.

Format

Enables you to choose a format for the structure.

  • Flat - Generates an XML Schema according to the ISO-ANSI Working draft (Part 14: XML Related Specifications SQL/XML).

  • Hierarchical - Represents the database structure as a tree hierarchy taking into account the relationship between tables.

Enable Attachments

If checked, the database table is selected for conversion.

Criterion

The Criterion options allow the user to specify the name of the selected database column and also how it should be converted into XML. The following options are available:

  • Element: When checked the selected column will be converted into an XML element.

  • Attribute: If checked the selected column will be converted into an XML attribute.

  • Skipped: Is to be selected if the intention is to skip that column from being imported.

  • Name: Allows you to specify the name of the column to be imported. Implicitly <oXygen/> suggests an import name that is according to SQL/XML Specification.

  • Type: Displays the data type of the imported column.