IBM DB2 XML Support

Available only in the Enterprise edition
  read the article

The support for IBM DB2 Pure XML database includes: multiple server connections, resource management, XML Schema registration, XQuery and SQL execution, and table data editing. See configuration details.

Data Source Explorer View

The Data Source Explorer view can be used to browse the database content.

Database Perspective

Drag-and-drop support between tree nodes and an opened SQL editor is also available: https://www.oxygenxml.com/doc/ug-editor/topics/sql-dnd-support.html#sql-dnd-support.

One of the interesting operations available on table nodes is the "Export to XML" action. That allows the database table content to be exported as an XML structure. Please note that Oxygen is able create a basic XSD schema for the table you need to export.

Export Table Dialog

Oxygen also features a tool that is able to generate an XSD schema based on a set of database tables. The Convert DB structure to XML Schema tool is available under the "Tools" menu. Basically, if you select a set of tables, Oxygen can generate an XSD schema that describes table data definitions and it takes the key relationships into account.

Generate Schema Tool

Table Explorer View

The Table Explorer view presents a database table content or the result of an SQL interrogation. If you want to edit the table content of a database, you can use the Edit operation from the contextual menu of the Data Source Explorer view.

The view allows you to add, delete, duplicate table rows, and more. If the database constraints are violated due to your changes, you will be presented with an error message that will help you to correct the problem.

Table Explorer View

The table columns can be sorted by clicking on the table header.

Editing XML Content of the XML Type Columns

The true power of Oxygen is realized when you need to edit content from the XML type columns. The data in these columns can be opened directly in Oxygen XML Editor so you can benefit from all its editing features.

Editing XML Cells

Other Operations Available for an XML Cell:

  • Insert XML file (inserts the content of an XML file on the particular cell)
  • Validate (validates the content of the current XML cell against a previously registered XSD schema)

XML Schema Repository

Every DB2 database schema has an associated "XML Schema repository" where all the XSD schemas available to validate XML content of the XML type columns are stored. The "XML Schema repository" is available as a child node in the Database view for any database schema node. You can expand the "XML Schema repository" node and perform operations such as registering a new schema or dropping an existing one.

Register Schema Dialog

For more information, see: https://www.oxygenxml.com/doc/ug-editor/topics/db2-xml-schema-repository-level.html#db2-xml-schema-repository-level.

The XSD schema stored in the "XML Schema repository" can be viewed in the Oxygen XSD editor.

Viewing XML Repository Schema

SQL, SQL/XML Support

You can use Oxygen to run SQL (including DDL) or SQL/XML interrogations.

SQL Editor

If you want to re-use the SQL queries, you can specify parameter markers (?) in the SQL content and add their corresponding mapping in the Configure Parameters dialog box for the associated scenario. For example, suppose you need to write an SQL interrogation to get a report with all employees from one department and their last evaluation dates. For this SQL query, you could use a parameter marker (the ID of the department) configured in the Transformation Scenario so you can easily re-use the query for another department.

Parameters Dialog

XQuery Support

DB2 pureXML supports XQuery interrogations when working with the XML content of the XML column types. For example, if you want to generate an XQuery to measure the employee satisfaction levels regarding the company rules, you can open an XQuery template (New/XQuery), configure the transformation scenario to match the DB2 connection for the transformer field, write the XQuery, and then execute it.

XQuery Editor