Executing SQL Statements

The steps for executing an SQL statement on a relational database are the following:
  1. Configure a transformation scenario from the Configure Transformation Scenario button from the Transformation toolbar. A SQL transformation scenario needs a database connection. You can configure a connection from the Preferences button from the scenario dialog. The dialog that appears contains the list of existing scenarios that apply to SQL documents.
  2. Set parameter values for SQL placeholders from the Parameters button from the scenario dialog. For example in SELECT * FROM `test`.`department` where DEPT = ? or DEPTNAME = ? two parameters can be configured for the place holders (?) in the transformation scenario. When the SQL statement will be executed, the first placeholder will be replaced with the value set for the first parameter in the scenario, the second placeholder will be replaced by the second parameter value and so on.
    Restriction: When a stored procedure is called in an SQL statement executed on an SQL Server database mixing in-line parameter values with values specified using the Parameters button of the scenario dialog is not recommended. It is due to a limitation of the SQL Server driver for Java applications. An example of stored procedure call that is not recommended is: call dbo.Test(22, ?).
  3. Execute the SQL scenario from the Transform now button of the scenario dialog.

    The result of a SQL transformation will be displayed in a view at the bottom of the Oxygen XML Editor plugin window.

  4. View more complex return values of the SQL transformation in a separate editor panel. A more complex value returned by the SQL query (for example an XMLTYPE value or a CLOB one) cannot be displayed entirely in the result table.
    1. Right click on the cell containing the complex value.
    2. Select the action Copy cell from the popup menu. The action will copy the value in the clipboard.
    3. Paste the value where you need it. For example you can paste the value in an opened XQuery editor panel of Oxygen XML Editor plugin .