Database Connector XML Queries: Structure and Examples

Structure of Database Connector XML Queries

The underlying XML syntax is identical for all SQL queries.

Case sensitivity is handled in the following tags:

<table> <tableNames> <tableName>
<fields> <field> <fieldName>
<conditions> <condition> <leftValue>
<conditions> <condition> <rightValue> (if a column name is used as rightValue)

Case sensitivity is NOT available for the following tags:

<group> <field> <fieldName>
Column names used in the HAVING expression in the <having> tag
<sortOrders> <sort>

<queries>

Element that encloses a set of SQL statements.

<query>

Initial definition of an SQL statement. This can occur several times, e.g. to send several SQL statements in a single query.

Attribute Mandatory field Permitted values Explanation

Attribute	Mandatory field	Permitted values	Explanation
`type`	Yes	`select` `insert` `update` `delete` `call` `updateOrInsert`	Type of SQL statement
`properties`	No	`force`	If the value `force` is set, an SQL statement specified in the <`value`> element is executed immediately. All other specifications are ignored.
`forceResult`	No	`true` `false`	SELECT statement that begin with SELECT will be detected automatically. For all other statements (WITH statements or stored procedures) ResultSets are returned if the value is set to `true`.
`queryID`	No		Depending on the `type`, in the `ResultSet`, this attribute is filled with the value of the corresponding query. This applies to `type = select`.

type

Yes

select
insert
update
delete
call
updateOrInsert

Type of SQL statement

properties

force

If the value force is set, an SQL statement specified in the <`value`> element is executed immediately. All other specifications are ignored.

forceResult

true
false

SELECT statement that begin with SELECT will be detected automatically.
For all other statements (WITH statements or stored procedures) ResultSets are returned if the value is set to true.

queryID

Depending on the type, in the ResultSet, this attribute is filled with the value of the corresponding query. This applies to type = select.

<value>

For immediate passing of an SQL statement. To enable this, the properties attribute must be set to force in the <query> element. All other specifications are ignored.

<tables>

Introductory XML element for the definition of an SQL statement.

<table>

Introductory XML element for the definition of a single SQL statement. It can be used with the attribute type="subselect" as a subquery in a main query. It can occur multiple times.

Attribute

Mandatory field

Permitted values

Explanation

alias

Arbitrary identifier

type

subselect

Specifies that it is handled as a subquery.

<tableNames>

Enclosing XML element for all table names queried in the SQL statement.

<tableName>

Table name queried in the SQL statement. It can occur multiple times. Corresponds to the FROM part of an SQL statement.

Attribute Mandatory field Permitted values Explanation

Attribute	Mandatory field	Permitted values	Explanation
`alias`	No	Arbitrary identifier	Alias name of the table

alias

Arbitrary identifier

Alias name of the table

<fields>

Enclosing XML element for all column names to be used in the SQL statement.

<field>

XML element for the name of the table column queried in the XML statement. It can occur multiple times. Corresponds to the Select part of an SQL statement.

Attribute Mandatory field Permitted value Explanation

Attribute	Mandatory field	Permitted value	Explanation
`alias`	No	Arbitrary identifier	Alias name of the column

alias

Arbitrary identifier

Alias name of the column

<fieldName>

Name of a table column to be used in the SQL statement.

<fieldValue>

Values for individual fields or the name of a subquery (only INSERT and UPDATE).

Attribute Mandatory field Permitted values Explanation

Attribute	Mandatory field	Permitted values	Explanation
type	No	`subselect` `force`	`subselect`: For executing a subquery `force`: Automatic type recognition is disabled for this field. The text for `<fieldValue>` is passed immediately to the database. This is sensible if you want to e.g. execute database functions such as AVG or MAX.

type

subselect
force

subselect: For executing a subquery
force: Automatic type recognition is disabled for this field. The text for <fieldValue> is passed immediately to the database. This is sensible if you want to e.g. execute database functions such as AVG or MAX.

<conditions>

(not for INSERT)

Attribute Mandatory field Permitted values Explanation

Attribute	Mandatory field	Permitted values	Explanation
`type`	Yes	`AND` `OR` `NOT` `AND NOT` `OR NOT`	Operator for linking to other criteria in the preceding `<conditions>` element or `<condition>` element on the same level. `AND` is the default value.

type

Yes

AND
OR
NOT
AND NOT
OR NOT

Operator for linking to other criteria in the preceding <conditions> element or <condition> element on the same level. AND is the default value.

Enclosing XML element for the WHERE condition of an SQL statement.
Several <conditions> elements can be nested to group or parenthesize statements.

<condition>

Values for a criterion in the WHERE condition of an SQL statement. It can occur multiple times.

Attribute Mandatory field Permitted values Explanation

Attribute	Mandatory field	Permitted values	Explanation
`type`	Yes	`AND` `OR` `NOT` `AND NOT` `OR NOT`	Operator for linking to other criteria in the preceding `<conditions>` element or `<condition>` element on the same level. `AND` is the default value.

type

Yes

AND
OR
NOT
AND NOT
OR NOT

Operator for linking to other criteria in the preceding <conditions> element or <condition> element on the same level. AND is the default value.

<leftValue>

Column name on the left-hand side of a criterion in a WHERE condition.

<operation>

Operator within a criterion in a WHERE condition, e.g. =, <, >, >=, ⇐, IN, NOT IN ….

<rightValue>

Contains the value on the right-hand side of a criterion in a WHERE condition. This can be a value, a column name, or the name of a Subselect.

Attribute Mandatory field Permitted values Explanation

Attribute	Mandatory field	Permitted values	Explanation
`type`	No	`subselect` `force`	`subselect`: For executing a subquery `force`: Automatic type recognition is disabled for this field. The text for `<rightValue>` is passed immediately to the database. This may for example be sensible if you want to immediately execute integrated database functions.
`isValue`	No	`true` `false`	`true`: `rightValue` is considered as a value. `false`: `rightValue` is considered as a column name. This is the default.

type

subselect
force

subselect: For executing a subquery
force: Automatic type recognition is disabled for this field. The text for <rightValue> is passed immediately to the database. This may for example be sensible if you want to immediately execute integrated database functions.

isValue

true
false

true: rightValue is considered as a value.
false: rightValue is considered as a column name. This is the default.

<sortOrders>

(only for SELECT)

Sorts the outputs in ascending or descending order in accordance with one field.

<sort>

Field that can be used as a sorting criterion.

Attribute Mandatory field Permitted values Explanation

Attribute	Mandatory field	Permitted values	Explanation
`order`	No	`ASC` (ascending) `DESC` (descending)	Sort order

order

ASC (ascending)
DESC (descending)

Sort order

<tail>

The value of the element is appended to the generated SQL request. This enables database-specific SQL extensions to be added.

The figure below depicts a Select in a tree structure:

Considering Case Sensitivity in Database Queries

The case-sensitive table and field names (the ones created with quotes) need to adhere to the following standards in the XML for them to work without errors:

If they are provided in the XML with quotes or with a force attribute, then they have to be in the same case as in the database irrespective of whether the Automatic type recognition of database object option in the Database Connection and Query Type dialog is enabled or not.
If they are provided in the XML without quotes, the Automatic type recognition of database object option in the Database Connection and Query Type dialog should be enabled.
System tables should be used in uppercase letters without quotes and with the Automatic type recognition of database object option in the Database Connection and Query Type dialog disabled.

Table and Fields Created with Quotes

XML query for the table Test with two columns Col1 and Col2. Auto-type recognition of database object can be enabled or disabled.

<queries>
    <query type="select">
        <tables>
            <table>
                <tableNames>
                    <tableName>"Test"</tableName>
                </tableNames>
                <fields>
                    <field>
                        <fieldName>"Test"."Col1"</fieldName>
                    </field>
                    <field>
                        <fieldName>"Test"."Col2"</fieldName>
                    </field>
                </fields>
            </table>
        </tables>
    </query>
</queries>

Table and Fields Created without Quotes

XML query for the table test with two columns col1 and col2. Auto-type recognition of database object must be enabled.

<queries>
    <query type="select">
        <tables>
            <table>
                <tableNames>
                    <tableName>test</tableName>
                </tableNames>
                <fields>
                    <field>
                        <fieldName>test.col1</fieldName>
                    </field>
                    <field>
                        <fieldName>test.col2</fieldName>
                    </field>
                </fields>
            </table>
        </tables>
    </query>
</queries>

Querying a System Table

With this XML query you access the DUAL system table. Auto-type recognition of database object must be disabled.

<queries>
    <query type="select">
        <tables>
            <table>
                <tableNames>
                    <tableName>DUAL</tableName>
                </tableNames>
                <fields>
                    <field>
                        <fieldName>*</fieldName>
                    </field>
                </fields>
            </table>
        </tables>
    </query>
</queries>

Select: Displaying Data Using XML Queries

SQL

SELECT name, address, phone, fax
FROM addresses
WHERE name = "Georg Miller"
ORDER BY name ASC, address DESC

XMLQuery

<queries>
    <query type="select">
        <tables>
            <table>
                <tableNames>
                    <tableName>addresses</tableName>
                </tableNames>
                <fields>
                    <field>
                        <fieldName>name</fieldName>
                    </field>
                    <field>
                        <fieldName>address</fieldName>
                    </field>
                    <field>
                        <fieldName>phone</fieldName>
                    </field>
                    <field>
                        <fieldName>fax</fieldName>
                    </field>
                </fields>
                <conditions>
                    <condition>
                        <leftValue>name</leftValue>
                        <operation>=</operation>
                        <rightValue>Georg Miller</rightValue>
                    </condition>
                    <condition type="OR">
                        <leftValue>name</leftValue>
                        <operation>=</operation>
                        <rightValue>Bob Brown</rightValue>
                    </condition>
                </conditions>
                <sortOrders>
                    <sort>name</sort>
                    <sort order="DESC">address</sort>
                </sortOrders>
            </table>
        </tables>
    </query>
</queries>

Select Distinct Using the Database Connector

With a SELECT DISTINCT, all of the different values of a table are returned.

SQL

SELECT DISTINCT abteilung
FROM mitarbeiter

XMLQuery

There are two ways of formulating a SELECT DISTINCT:

Specify DISTINCT for the column name:

<queries>
    <query type="select">
        <tables>
            <table>
                <tableNames>
                    <tableName>Mitarbeiter</tableName>
                </tableNames>
                <fields>
                    <field>
                        <fieldName>DISTINCT Abteilung</fieldName>
                    </field>
                </fields>
            </table>
        </tables>
    </query>
</queries>

Use the properties="force" attribute to pass the query immediately:

<queries>
    <query type="select" properties="force">
        <value>SELECT DISTINCT Abteilung FROM Mitarbeiter</value>
    </query>
</queries>

Join: Data from Different Tables

A join is a query that relates the datasets of two or more different table columns with each other, for example:

SQL

SELECT *
FROM Gartenmoebel, Kategorien
WHERE Gartenmoebel.KategorieNr = Kategorien.Kategorienummer

XMLQuery

<queries>
    <query type="select">
        <tables>
            <table>
                <tableNames>
                    <tableName>Gartenmoebel</tableName>
                    <tableName>Kategorien</tableName>
                </tableNames>
                <fields>
                    <field>
                        <fieldName>*</fieldName>
                    </field>
                </fields>
                <conditions>
                    <condition>
                        <leftValue>Gartenmoebel.KategorieNr</leftValue>
                        <operation>=</operation>
                        <rightValue>Kategorien.Kategorienummer</rightValue>
                    </condition>
                </conditions>
            </table>
        </tables>
    </query>
</queries>

Insert: Inserting Data Sets in the Database Connector

You use INSERT to add a dataset to a table.

To bulk insert datasets, use a prepared statement; refer to Prepared Statement in a Query.

SQL

INSERT INTO addresses (name, address, phone, fax)
VALUES ("Georg Miller", "Hauptstrasse 7", "253263", "253264")

XMLQuery

<queries>
    <query type="insert">
        <tables>
            <table>
                <tableNames>
                    <tableName>addresses</tableName>
                </tableNames>
                <fields>
                    <field>
                        <fieldName>name</fieldName>
                        <fieldValue>Georg Miller</fieldValue>
                    </field>
                    <field>
                        <fieldName>address</fieldName>
                        <fieldValue>Hauptstrasse 7</fieldValue>
                    </field>
                    <field>
                        <fieldName>phone</fieldName>
                        <fieldValue>253263</fieldValue>
                    </field>
                    <field>
                        <fieldName>fax</fieldName>
                        <fieldValue>253264</fieldValue>
                    </field>
                </fields>
            </table>
        </tables>
    </query>
</queries>

Update: Updating Datasets in the Database Connector

You use UPDATE to update existing datasets in a table.

To bulk update datasets, use a prepared statement; refer to Prepared Statement in a Query.

SQL

UPDATE addresses
SET address = "Hauptstrasse 8"
WHERE name = "Georg Miller"

XMLQuery

<queries>
    <query type="update">
        <tables>
            <table>
                <tableNames>
                    <tableName>addresses</tableName>
                </tableNames>
                <fields>
                    <field>
                        <fieldName>address</fieldName>
                        <fieldValue>Hauptstrasse 8</fieldValue>
                    </field>
                </fields>
                <conditions>
                    <condition>
                        <leftValue>name</leftValue>
                        <operation>=</operation>
                        <rightValue>Georg Miller</rightValue>
                    </condition>
                </conditions>
            </table>
        </tables>
    </query>
</queries>

UpdateOrInsert in the Database Connector

This SQL statement represents the Insert and Update SQL statements. First, an attempt is made to update a dataset; if this is not possible because the dataset is not available, the dataset is inserted. The Update is executed first because it is less time-consuming than the Insert.

<queries>
    <query type="updateOrInsert">
        <tables>
            <table>
                <tableNames>
                    <tableName>OrderTable</tableName>
                </tableNames>
                <fields>
                    <field skip="update">
                        <fieldName>OrderNr</fieldName>
                        <fieldValue>Kunde11</fieldValue>
                    </field>
                    <field>
                        <fieldName>Kunde</fieldName>
                        <fieldValue>123</fieldValue>
                    </field>
                    <field>
                        <fieldName>Text1</fieldName>
                        <fieldValue>info1</fieldValue>
                    </field>
                    <field>
                        <fieldName>Text2</fieldName>
                        <fieldValue>info2</fieldValue>
                    </field>
                </fields>
                <conditions>
                    <condition type="OR">
                        <leftValue>OrderNr</leftValue>
                        <operation>=</operation>
                        <rightValue>Kunde11</rightValue>
                    </condition>
                </conditions>
            </table>
        </tables>
    </query>
</queries>

Delete: Deleting Data Sets in the Database Connector

You use a Delete to delete datasets. In the example below, all data on Georg Miller is deleted.

SQL

DELETE
FROM addresses
WHERE name="Georg Miller"

XMLQuery

<queries>
    <query type="delete">
        <tables>
            <table>
                <tableNames>
                    <tableName>addresses</tableName>
                </tableNames>
                <conditions>
                    <condition>
                        <leftValue>name</leftValue>
                        <operation>=</operation>
                        <rightValue>Georg Miller</rightValue>
                    </condition>
                </conditions>
            </table>
        </tables>
    </query>
</queries>

Multiple SQL Statements in a Single Query

If the SQL queries derive from input messages, multiple statements can also be used in a single query. This is not possible for static queries.

XMLQuery

<queries>
    <query type="insert">
        <tables>
            <table>
                <tableNames>
                    <tableName>eins</tableName>
                </tableNames>
                <fields>
                    <field>
                        <fieldName>id</fieldName>
                        <fieldValue>1</fieldValue>
                    </field>
                    <field>
                        <fieldName>meldung</fieldName>
                        <fieldValue>
                            spießbraten köcheln
                        </fieldValue>
                    </field>
                    <field>
                        <fieldName>datum</fieldName>
                        <fieldValue>
                            2003-03-20 10:20:00.000000000
                        </fieldValue>
                    </field>
                    <field>
                        <fieldName>wert</fieldName>
                        <fieldValue>10.12</fieldValue>
                    </field>
                </fields>
            </table>
        </tables>
    </query>
    <query type="select">
        <tables>
            <table>
                <tableNames>
                    <tableName>eins</tableName>
                </tableNames>
                <fields>
                    <field>
                        <fieldName>*</fieldName>
                    </field>
                </fields>
                <conditions>
                    <condition type="OR">
                        <leftValue>id</leftValue>
                        <operation>=</operation>
                        <rightValue>1</rightValue>
                    </condition>
                </conditions>
            </table>
        </tables>
    </query>
    <query type="update">
        <tables>
            <table>
                <tableNames>
                    <tableName>eins</tableName>
                </tableNames>
                <fields>
                    <field>
                        <fieldName>id</fieldName>
                        <fieldValue>2</fieldValue>
                    </field>
                </fields>
                <conditions>
                    <condition type="OR">
                        <leftValue>id</leftValue>
                        <operation>=</operation>
                        <rightValue>1</rightValue>
                    </condition>
                </conditions>
            </table>
        </tables>
    </query>
    <query type="delete">
        <tables>
            <table>
                <tableNames>
                    <tableName>eins</tableName>
                </tableNames>
            </table>
        </tables>
    </query>
</queries>

Force: Passing SQL Statements Directly Using the Database Connector

XMLQuery

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
    <xsl:output method="xml" encoding="ISO-8859-1"/>
    <xsl:template match="/">
        <queries>
            <query type="select" properties="force">
                <value>
                    create table trade_user ( id_user NUMBER(20,0) PRIMARY KEY, since_user NUMBER(15,0),
                    lastlogin_user NUMBER(15,0), sex_user NUMBER(1,0), language_user VARCHAR2(5), title_user
                    VARCHAR2(4000), firstname_user VARCHAR2(200), lastname_user VARCHAR2(200), company_user
                    NUMBER(20,0), function_user VARCHAR2(4000), street_user VARCHAR2(4000), zip_user
                    VARCHAR2(4000), land_user VARCHAR2(4000), city_user VARCHAR2(4000), phone_user
                    VARCHAR2(4000), mobile_user VARCHAR2(4000), fax_user VARCHAR2(4000), email_user
                    VARCHAR2(200) NOT NULL UNIQUE, web_user VARCHAR2(4000), via_user VARCHAR2(4000),
                    enable_user NUMBER(2,0), comment_user VARCHAR2(4000), abbo_news_user NUMBER(1,0),
                    abbo_mail_user NUMBER(1,0), keyaccount_user NUMBER(20,0), lastforum_user NUMBER(20,0),
                    contentarea_user VARCHAR2(4000), deliveryplace_user VARCHAR2(4000) DEFAULT '1',
                    originplace_user VARCHAR2(4000) DEFAULT '1', sms_mail_user VARCHAR2(1900),
                    customer_protection_user NUMBER(1,0) )
                </value>
            </query>
            <query type="select" properties="force">
                <value>
                    insert into xtrade_user( id_user, firstname_user, lastname_user, company_user,
                    email_user ) values (1,'Hans','Müller',1,'h.mueller@karstadt.de')
                </value>
            </query>
            <query properties="force">
                <value>DROP TABLE "ORATEST"."TESTTABLE"</value>
            </query>
        </queries>
    </xsl:template>
</xsl:stylesheet>

Prepared Statement in a Query

You can use a Prepared Statement to execute arbitrary queries without the risk of SQL injection attacks.

XMLQuery

<queries>
    <query type="preparedStatement">
        <preparedStatement>SELECT NAME FROM TESTTABLE WHERE ID = ? </preparedStatement>
        <params>
            <paramValue>10</paramValue>
        </params>
    </query>
</queries>

In this case a preparedStatement can only be executed for SELECT, INSERT, UPDATE and DELETE queries.

Execute Multiple Queries with a Prepared Statement

You can use a Prepared Statement to execute multiple queries.

XMLQuery

<queries>
    <query type="preparedStatement">
        <preparedStatement>INSERT INTO TESTTABLE(id, name) VALUES(?,?)</preparedStatement>
        <params>
            <paramValue>10</paramValue>
            <paramValue>test</paramValue>
        </params>
        <params>
            <paramValue>20</paramValue>
            <paramValue>test2</paramValue>
        </params>
    </query>
</queries>

In this case a preparedStatement can only be executed for INSERT, UPDATE and DELETE queries.

Multiple Prepared Statements in a single XML

You can use multiple Prepared Statement queries in the same XML.

XMLQuery

<queries>
    <query type="preparedStatement">
        <preparedStatement>INSERT INTO TESTTABLE(id, name) VALUES(?,?)</preparedStatement>
        <params>
            <paramValue>10</paramValue>
            <paramValue>test</paramValue>
        </params>
    </query>
    <query type="preparedStatement">
        <preparedStatement>SELECT NAME FROM TESTTABLE WHERE ID = ? </preparedStatement>
        <params>
            <paramValue>10</paramValue>
        </params>
    </query>
</queries>

In this case a preparedStatement can only be executed for SELECT, INSERT, UPDATE and DELETE queries.

Subqueries Using Subselects in the Database Connector

You can nest SQL queries to evaluate the result of a Subselect in the same Select.

SQL

SELECT LieferscheinId , ErstellungsDatum
FROM LieferscheinTable
WHERE
        factory=(SELECT factory FROM SpeditionsTable
                 WHERE status!=X AND TransportId=10000037)
  AND gate=(SELECT gate FROM SpeditionsTable
WHERE status!=X AND TransportId=10000037 )
  AND status = F

XMLQuery

For each Subselect, an additional table element with the attributes type="subselect" and alias=[NameOfSubselects] is created. The table element must come before the main Select.

The result of the Subselect is integrated into the main Select in field and/or condition elements using the type="subselect" attribute. The value of the field and/or condition element is the name of the subselect:

<tables>
    <table type="subselect" alias="subFactory">
        <tableNames>
            <tableName>SpeditionsTable</tableName>
        </tableNames>
        <fields>
            <field>
                <fieldName>factory</fieldName>
            </field>
        </fields>
        <conditions>
            <condition type="AND">
                <leftValue>status</leftValue>
                <operation>!=</operation>
                <rightValue>X</rightValue>
            </condition>
            <condition type="AND">
                <leftValue>TransportId</leftValue>
                <operation>=</operation>
                <rightValue>
                    <xsl:value-of select="FormPrepare/
            Response/Panel/Transporte/SLB-Nr"/>
                </rightValue>
            </condition>
        </conditions>
    </table>
    <table type="subselect" alias="subGate">
        <tableNames>
            <tableName>SpeditionsTable</tableName>
        </tableNames>
        <fields>
            <field>
                <fieldName>gate</fieldName>
            </field>
        </fields>
        <conditions>
            <condition type="AND">
                <leftValue>status</leftValue>
                <operation>!=</operation>
                <rightValue>X</rightValue>
            </condition>
            <condition type="AND">
                <leftValue>TransportId</leftValue>
                <operation>=</operation>
                <rightValue>
                    <xsl:value-of select="FormPrepare/
          Response/Panel/Transporte/SLB-Nr"/>
                </rightValue>
            </condition>
        </conditions>
    </table>
    <table>
        <tableNames>
            <tableName>LieferscheinTable</tableName>
        </tableNames>
        <fields>
            <field>
                <fieldName>LieferscheinId</fieldName>
            </field>
            <field>
                <fieldName>ErstellungsDatum</fieldName>
            </field>
        </fields>
        <conditions>
            <condition type="AND">
                <leftValue>factory</leftValue>
                <operation>=</operation>
                <rightValue type="subselect">
                    subFactory
                </rightValue>
            </condition>
            <condition type="AND">
                <leftValue>gate</leftValue>
                <operation>=</operation>
                <rightValue type="subselect">
                    subGate
                </rightValue>
            </condition>
            <condition type="AND">
                <leftValue>status</leftValue>
                <operation>=</operation>
                <rightValue>F</rightValue>
            </condition>
        </conditions>
    </table>
</tables>

Calling Stored Procedures in the Database Connector

In a stored procedure, entire sequences of statements can be stored under a name, made available on the database server, and executed.

The example below depicts the calling of stored procedures using the <method name="[NAME]"> element, with and without the passing of parameters:

XMLQuery

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
    <xsl:output method="xml" encoding="ISO-8859-1"/>
    <xsl:template match="/">
        <queries>
            <query type="call">
                <method name="TESTPROC_WITHOUTPARAM"/>
            </query>
            <query type="call">
                <method name="TESTPROC_IN">
                    <parameter direction="IN" type="DECIMAL">2</parameter>
                </method>
            </query>
            <query type="call">
                <method name="TESTPROC_OUT">
                    <parameter direction="OUT" type="VARCHAR"/>
                </method>
            </query>
            <query type="call">
                <method name="TESTPROC_INOUT">
                    <parameter direction="INOUT" type="NUMERIC">4</parameter>
                </method>
            </query>
            <query type="call">
                <method name="TESTPROC_INOUT2">
                    <parameter direction="IN" type="NUMERIC">5</parameter>
                    <parameter direction="OUT" type="NUMERIC">5</parameter>
                </method>
            </query>
            <query type="call">
                <method name="TESTFUNC_OUT">
                    <result type="DATE"/>
                </method>
            </query>
            <query type="call">
                <method name="TESTFUNC_INOUT">
                    <parameter type="NUMERIC" direction="IN">2</parameter>
                    <result type="NUMERIC"/>
                </method>
            </query>
        </queries>
    </xsl:template>
</xsl:stylesheet>

Using the name attribute

In case no name attribute is used for the query parameters, the order of the parameters must match the order in the query.

Example:

<query type="call">
    <method name="TESTPROC_INOUT2">
        <parameter direction="IN" type="NUMERIC">5</parameter>
        <parameter direction="IN" type="VARCHAR">5</parameter>
        <parameter direction="OUT" type="NUMERIC">5</parameter>
    </method>
</query>

We recommend using the name attribute for stored procedures and functions. A change in the order of incoming parameters does not have any negative effect on the workflow due because the parameter values are referenced the by the name attribute.

Example with an optional name attribute:

<query type="call">
    <method name="TESTPROC_INOUT2">
        <parameter direction="IN" name="MyNumericParam" type="NUMERIC">5</parameter>
        <parameter direction="IN" name="MyVarcharParam" type="VARCHAR">5</parameter>
        <parameter direction="OUT" type="NUMERIC">5</parameter>
    </method>
</query>

For Oracle only: Using the UseDefault attribute for calling Oracle functions

To call Oracle functions having default values for the input parameters, you have to use the useDefault=true attribute in the method tag. The useDefault attribute is not mandatory, and the value is false if not specified.

The useDefault attribute is supported only for Oracle functions, and when it is used, the name attribute is mandatory for all the <parameter> tags. Otherwise, an error might occur.

Example

<query type="call">
    <method name="GET1" useDefault="true">
        <parameter type="VARCHAR" name="P1" direction="IN">a</parameter>
        <parameter type="VARCHAR" name="P3" direction="OUT"></parameter>
        <result type="NUMERIC"/>
    </method>
</query>

Supporting ResultSet output (MS SQL Server only)

If a stored procedure returning a ResultSet shall called from Database Connector, the following tag has to be used in the input XML.

Example:

<queries>
    <query type="call">
        <method name="Test">
            <result type="RESULTSET"/>
        </method>
    </query>
</queries>

Using the TO_TIMESTAMP Function For Oracle Databases

For Oracle databases, the TO_TIMESTAMP function can be used in the SELECT, INSERT, UPDATE, and INSERT or UPDATE query types as well as in WHERE clause conditions.

SELECT: Using the TO_TIMESTAMP Function

SQL Query

SELECT ID, MESSAGE, TO_TIMESTAMP(DATE_TIMESTAMP,'YYYY-MM-DD HH24:MI:SS.FF'), TEST_DATE
FROM TABLE_TIMESTAMP

XML Format

<query type="select">
    <tables>
        <table>
            <tableNames>
                <tableName/>
            </tableNames>
            <fields>
                <field function="to_timestamp" format="YYYY-MM-DD HH24:MI:SS.FF">
                    <fieldName/>
                </field>
            </fields>
            <conditions>
                <condition type="OR">
                    <leftValue/>
                    <operation/>
                    <rightValue/>
                </condition>
            </conditions>
            <group>
                <field>
                    <fieldName/>
                </field>
                <expression/>
            </group>
            <having>
                <expression/>
            </having>
            <sortOrders>
                <sort order="ASC"/>
            </sortOrders>
            <tail/>
        </table>
    </tables>
</query>

Example

<query type="select">
    <tables>
        <table>
            <tableNames>
                <tableName>TABLE_TIMESTAMP</tableName>
            </tableNames>
            <fields>
                <field>
                    <fieldName>ID</fieldName>
                </field>
                <field>
                    <fieldName>MESSAGE</fieldName>
                </field>
                <field>
                    <fieldName function="to_timestamp" format="YYYY-MM-DD HH24:MI:SS.FF">DATE_TIMESTAMP</fieldName>
                </field>
                <field>
                    <fieldName>TEST_DATE</fieldName>
                </field>
            </fields>
        </table>
    </tables>
</query>

INSERT: Using the TO_TIMESTAMP Function

SQL Query

INSERT INTO TABLE_TIMESTAMP(ID, DATE_TIMESTAMP, TEST_DATE)
        VALUES (101, TO_TIMESTAMP('2017-07-20 15:28:42.124','YYYY-MM-DD HH24:MI:SS.FF'),'2017-08-15 10:10:12.231')

XML Format

<query type="insert">
    <tables>
        <table>
            <tableNames>
                <tableName/>
            </tableNames>
            <fields>
                <field>
                    <fieldName/>
                    <fieldValue function="to_timestamp" format="YYYY-MM-DD HH24:MI:SS.FF"/>
                </field>
            </fields>
        </table>
    </tables>
</query>

Example

<query type="insert">
    <tables>
        <table>
            <tableNames>
                <tableName>TABLE_TIMESTAMP</tableName>
            </tableNames>
            <fields>
                <field>
                    <fieldName>ID</fieldName>
                    <fieldValue>101</fieldValue>
                </field>
                <field>
                    <fieldName>DATE_TIMESTAMP</fieldName>
                    <fieldValue function="to_timestamp" format="YYYY-MM-DD HH24:MI:SS.FF">2017-07-20 15:28:42.124</fieldValue>
                </field>
                <field>
                    <fieldName>TEST_DATE</fieldName>
                    <fieldValue>2017-08-15 10:10:12.231</fieldValue>
                </field>
            </fields>
        </table>
    </tables>
</query>

UPDATE: Using the TO_TIMESTAMP Function

SQL Query

UPDATE TABLE_TIMESTAMP
SET MESSAGE='anotherString', DATE_TIMESTAMP=TO_TIMESTAMP('2017-07-20 15:28:42.123','YYYY-MM-DD HH24:MI:SS.FF')
WHERE ID = 101

XML Format

<query type="update">
    <tables>
        <table>
            <tableNames>
                <tableName/>
            </tableNames>
            <fields>
                <field>
                    <fieldName/>
                    <fieldValue function="to_timestamp" format="YYYY-MM-DD HH24:MI:SS.FF"/>
                </field>
            </fields>
            <conditions>
                <condition type="OR">
                    <leftValue/>
                    <operation/>
                    <rightValue/>
                </condition>
            </conditions>
        </table>
    </tables>
</query>

Example

<query type="update">
    <tables>
        <table>
            <tableNames>
                <tableName>TABLE_TIMESTAMP</tableName>
            </tableNames>
            <fields>
                <field>
                    <fieldName>MESSAGE</fieldName>
                    <fieldValue>anotherString</fieldValue>
                </field>
                <field>
                    <fieldName>DATE_TIMESTAMP</fieldName>
                    <fieldValue function="to_timestamp" format="YYYY-MM-DD HH24:MI:SS.FF">2017-07-20 15:28:42.123</fieldValue>
                </field>
            </fields>
            <conditions>
                <condition type="AND">
                    <leftValue>ID</leftValue>
                    <operation>=</operation>
                    <rightValue>101</rightValue>
                </condition>
            </conditions>
        </table>
    </tables>
</query>

UPDATE or INSERT: Using the TO_TIMESTAMP Function

SQL Query

UPDATE TABLE_TIMESTAMP
SET ID=102,
    MESSAGE='anotherString',
    DATE_TIMESTAMP=TO_TIMESTAMP('2017-07-20 15:28:42.124','YYYY-MM-DD HH24:MI:SS.FF'),
    TEST_DATE='2017-07-20 15:28:42.122'
WHERE ID = 102
   OR
INSERT INTO TABLE_TIMESTAMP(ID, MESSAGE, DATE_TIMESTAMP, TEST_DATE)
VALUES (102, 'anotherString', TO_TIMESTAMP('2017-07-20 15:28:42.124','YYYY-MM-DD HH24:MI:SS.FF'),'2017-07-20 15:28:42.122')

XML Format

<query type="updateOrInsert">
    <tables>
        <table>
            <tableNames>
                <tableName/>
            </tableNames>
            <fields>
                <field skip="update">
                    <fieldName/>
                    <fieldValue function="to_timestamp" format="YYYY-MM-DD HH24:MI:SS.FF"/>
                </field>
            </fields>
            <conditions>
                <condition type="OR">
                    <leftValue/>
                    <operation/>
                    <rightValue/>
                </condition>
            </conditions>
        </table>
    </tables>
</query>

Example

<query type="updateOrInsert">
    <tables>
        <table>
            <tableNames>
                <tableName>TABLE_TIMESTAMP</tableName>
            </tableNames>
            <fields>
                <field>
                    <fieldName>ID</fieldName>
                    <fieldValue>102</fieldValue>
                </field>
                <field>
                    <fieldName>MESSAGE</fieldName>
                    <fieldValue>anotherString</fieldValue>
                </field>
                <field>
                    <fieldName>DATE_TIMESTAMP</fieldName>
                    <fieldValue function="to_timestamp" format="YYYY-MM-DD HH24:MI:SS.FF">2017-07-20 15:28:42.124</fieldValue>
                </field>
                <field>
                    <fieldName>TEST_DATE</fieldName>
                    <fieldValue>2017-07-20 15:28:42.122</fieldValue>
                </field>
            </fields>
            <conditions>
                <condition type="AND">
                    <leftValue>ID</leftValue>
                    <operation>=</operation>
                    <rightValue>102</rightValue>
                </condition>
            </conditions>
        </table>
    </tables>
</query>

WHERE Clause: Using the TO_TIMESTAMP Function

SQL Query

TO_TIMESTAMP as
    left value:
SELECT *
FROM TABLE_TIMESTAMP
WHERE TO_TIMESTAMP(DATE_TIMESTAMP,'YYYY-MM-DD HH24:MI:SS.FF') = '2017-07-20 15:28:42.122'
    Right Value:
SELECT *
FROM TABLE_TIMESTAMP
WHERE TEST_DATE = TO_TIMESTAMP('2017-07-20 15:28:42.124','YYYY-MM-DD HH24:MI:SS.FF')

XML Format

<conditions>
    <condition type="OR">
        <leftValue function="to_timestamp" format="YYYY-MM-DD HH24:MI:SS.FF"/>
        <operation/>
        <rightValue function="to_timestamp" format="YYYY-MM-DD HH24:MI:SS.FF"/>
    </condition>
</conditions>

Using Null Values In Oracle Database

For Oracle databases, the NULL function can be used in the INSERT, UPDATE, and INSERT or UPDATE query types.

INSERT: Using Null Values

SQL Query

INSERT INTO NULLTABLE(COL1,COL2) VALUES (123,null)

XML Format

<query type="insert">
    <tables>
        <table>
            <tableNames>
                <tableName/>
            </tableNames>
            <fields>
                <field>
                    <fieldName/>
                    <fieldValue/>
                </field>
            </fields>
        </table>
    </tables>
</query>

Example

<query type="insert">
    <tables>
        <table>
            <tableNames>
                <tableName>NULLTABLE</tableName>
            </tableNames>
            <fields>
                <field>
                    <fieldName>COL1</fieldName>
                    <fieldValue>123</fieldValue>
                </field>
                <field>
                    <fieldName>COL2</fieldName>
                    <fieldValue>@null</fieldValue>
                </field>
            </fields>
        </table>
    </tables>
</query>

UPDATE: Using Null Values

SQL Query

UPDATE NULLTABLE
SET COL1=null, COL2='anotherString'
WHERE COL1=123

XML Format

<query type="update">
    <tables>
        <table>
            <tableNames>
                <tableName/>
            </tableNames>
            <fields>
                <field>
                    <fieldName/>
                    <fieldValue/>
                </field>
            </fields>
            <conditions>
                <condition type="OR">
                    <leftValue/>
                    <operation/>
                    <rightValue/>
                </condition>
            </conditions>
        </table>
    </tables>
</query>

Example

<query type="update">
    <tables>
        <table>
            <tableNames>
                <tableName>NULLTABLE</tableName>
            </tableNames>
            <fields>
                <field>
                    <fieldName>COL1</fieldName>
                    <fieldValue>@null</fieldValue>
                </field>
                <field>
                    <fieldName>COL2</fieldName>
                    <fieldValue>anotherString</fieldValue>
                </field>
            </fields>
            <conditions>
                <condition type="AND">
                    <leftValue>COL1</leftValue>
                    <operation>=</operation>
                    <rightValue>123</rightValue>
                </condition>
            </conditions>
        </table>
    </tables>
</query>

UPDATE or INSERT: Using Null Values

SQL Query

UPDATE NULLTABLE
SET COL1=50, COL2=null
WHERE COL1=50
   OR
INSERT INTO NULLTABLE(COL1,COL2)
VALUES (50,null)

XML Format

<query type="updateOrInsert">
    <tables>
        <table>
            <tableNames>
                <tableName/>
            </tableNames>
            <fields>
                <field skip="update">
                    <fieldName/>
                    <fieldValue/>
                </field>
            </fields>
            <conditions>
                <condition type="OR">
                    <leftValue/>
                    <operation/>
                    <rightValue/>
                </condition>
            </conditions>
        </table>
    </tables>
</query>

Example

<query type="updateOrInsert">
    <tables>
        <table>
            <tableNames>
                <tableName>NULLTABLE</tableName>
            </tableNames>
            <fields>
                <field>
                    <fieldName>COL1</fieldName>
                    <fieldValue>50</fieldValue>
                </field>
                <field>
                    <fieldName>COL2</fieldName>
                    <fieldValue>@null</fieldValue>
                </field>
            </fields>
            <conditions>
                <condition type="OR">
                    <leftValue>COL1</leftValue>
                    <operation>=</operation>
                    <rightValue>50</rightValue>
                </condition>
            </conditions>
        </table>
    </tables>
</query>

Using is null/is not null conditions in the Condition Tag of SQL Queries

In the select, update, and delete query types, you can use both conditions is null and is not null.

Examples

XML query with is null condition

<query type="select">
    <tables>
        <table>
            <tableNames>
                <tableName>NULLTABLE</tableName>
            </tableNames>
            <fields>
                <field>
                    <fieldName>COL1</fieldName>
                </field>
            </fields>
            <conditions>
                <condition type="AND">
                    <leftValue>COL2</leftValue>
                    <operation>is</operation>
                    <rightValue>@null</rightValue>
                </condition>
            </conditions>
        </table>
    </tables>
</query>

XML query with is not null condition

<query type="select">
    <tables>
        <table>
            <tableNames>
                <tableName>NULLTABLE</tableName>
            </tableNames>
            <fields>
                <field>
                    <fieldName>COL1</fieldName>
                </field>
            </fields>
            <conditions>
                <condition type="AND">
                    <leftValue>COL2</leftValue>
                    <operation>is not</operation>
                    <rightValue>@null</rightValue>
                </condition>
            </conditions>
        </table>
    </tables>
</query>

Select from Dual Tables

Use dual in the <tableName> tag in lowercase or uppercase letters without quotes as follows:
- <tableName>dual</tableName>
- <tableName>DUAL</tableName>
Disable the Automatic type recognition of database object option in the Database Connection and Query Type dialog.

Fetching the Value of a Field

XML Query for the table DUAL to fetch the value of the username.

<queries>
    <query type="select">
        <tables>
            <table>
                <tableNames>
                    <tableName>dual</tableName>
                </tableNames>
                <fields>
                    <field>
                        <fieldName>user</fieldName>
                    </field>
                </fields>
            </table>
        </tables>
    </query>
</queries>

XML Query for the table DUAL to fetch the sequence value.

<queries>
    <query type="select">
        <tables>
            <table>
                <tableNames>
                    <tableName>dual</tableName>
                </tableNames>
                <fields>
                    <field>
                        <fieldName>sequenceA.nextval</fieldName>
                    </field>
                </fields>
            </table>
        </tables>
    </query>
</queries>

XML Query for the table DUAL to fetch the system date value.

<queries>
    <query type="select">
        <tables>
            <table>
                <tableNames>
                    <tableName>dual</tableName>
                </tableNames>
                <fields>
                    <field>
                        <fieldName>sysdate</fieldName>
                    </field>
                </fields>
            </table>
        </tables>
    </query>
</queries>

Fetching a Value by Performing an Operation

XML query for the table DUAL to fetch the value 15+10 by performing the operation 15+10.

<queries>
    <query type="select">
        <tables>
            <table>
                <tableNames>
                    <tableName>dual</tableName>
                </tableNames>
                <fields>
                    <field>
                        <fieldName>15+10</fieldName>
                    </field>
                </fields>
            </table>
        </tables>
    </query>
</queries>

Fetching a Value by Performing a Function

XML query for the table DUAL to fetch the value EXAMPLE by performing the conversion function UPPER('Example').

<queries>
    <query type="select">
        <tables>
            <table>
                <tableNames>
                    <tableName>dual</tableName>
                </tableNames>
                <fields>
                    <field>
                        <fieldName>UPPER('Example')</fieldName>
                    </field>
                </fields>
            </table>
        </tables>
    </query>
</queries>