The steps for executing an SQL statement on a relational database are the
following:
-
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.
-
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,
?).
-
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 Developer plugin
window.
-
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.
-
Right click on the cell containing the complex value.
-
Select the action Copy cell from the popup
menu.
The action will copy the value in the clipboard.
-
Paste the value where you need it.
For example you can paste the value in an opened XQuery editor
panel of
Oxygen XML Developer plugin
.