Executing SQL Statements
The steps for executing an SQL statement on a relational database are as follows:
-
Configure a transformation scenario
using the
Configure Transformation Scenario(s) action from the toolbar or the
menu.
A SQL transformation scenario needs a database connection. You can configure a connection using the
Preferences button from the SQL transformation dialog box.The dialog box contains the list of existing scenarios that apply to SQL documents. -
Set parameter values for SQL placeholders using the Parameters
button from the SQL transformation dialog box.
For example, in
SELECT * FROM `test`.`department` where DEPT = ? or DEPTNAME = ?the two parameters can be configured for the place holders (?) in the transformation scenario.When the SQL statement is executed, the first placeholder is replaced with the value set for the first parameter in the scenario, the second placeholder is 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 inline parameter values with values specified using the Parameters button of the scenario dialog box is not recommended. This is due to a limitation of the SQL Server driver for Java applications. An example of stored procedure that is not recommended:call dbo.Test(22, ?). -
Execute the SQL scenario by clicking the OK or
Apply associated button.
The result of a SQL transformation is displayed in a view at the bottom of the Oxygen XML Editor 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 or CLOB value) cannot be displayed entirely in the result table.