Show TOC

Defining XML Documents for Message Protocol XML SQL FormatLocate this document in the navigation structure

Use

You can modify one or more database tables by means of an XI message. Depending on the content of the message payload, you can insert (INSERT), update (UPDATE), or delete (DELETE) data in database tables.

You can also include results from queries (SELECT) in the response in XML format for synchronous messages.

You use EXECUTE to execute stored procedures and SQL QUERY to forward more complex SQL statements to the database by using the adapter.

Structure of Message Payload

<root>
  <StatementName1>
<dbTableName action="ACTION>
<table>realDbTableName</table>
<access>
<col1>val1</col1>
<col2>val2</col2>
</access>
<key1>
<col2>val2old</col2>
<col4>val4</col4>
</key1>
<key2>
<col2>val2old2</col2>
</key2>
</dbTableName>
  </StatementName1>
  <StatementName2>
...
...
  </StatementName2>
...
</root>
Procedure
  1. Define a mapping that converts the payload of an XI message to the required XML structure.

Defining the XML Document Structure

  1. Define a
    <root>
    tag. You can use a name of your choice for the tag.
  2. Under the
    <root>
    tag, you can define one or more statements.

    You can use names of your choice for the tags for statements.

    Each statement contains the description of a database action.

    With the exception of the execution description for a stored procedure, you use a common structure for all statements.

  3. Enter the name of the database table in the element under the statement element (
    dbTableNameactionUPDATEINSERTUPDATE_INSERTDELETE,SELECT, EXECUTESQL-QUERY
    ) and the attribute with the value , , , , or .
  4. If you use the optional <table> element, the specified value is used as the database table name.
    Do not change the name of the tag.
    <table><dbTableName>
    must be the first element in the block within .

    This enables you, for example, to define table names that contain non-XML-compatible characters or characters that cannot be used in interface definitions in the Integration Builder.

  5. At the next level there is (except for in the
    DELETEaccess
    action) an element with the name and one or more elements with arbitrary names. In the above example, these elements are called keyN.
    • Enter the
      access
      element first and define the table columns that are to be accessed.

      Do not change the name of the tag.

    • Use the <
      key>
      elements to describe the condition for accessing the table columns.
  6. Define one or more statements for editing database tables:

Defining Attributes in the <key>Elements

  1. You have the option of setting the following attributes in the <key> elements:
    • compareOperation= <compareType>

      You use this attribute to set the logical compare operation for the respective element. The following values are permitted:

      • EQ: Equal (default value)
      • NEQ: Not equal
      • LT: Less than
      • LTEQ: Less than or equal to
      • GT: Greater than
      • GTEQ: Greater than or equal to
      • LIKE: Likeness (of strings). In the corresponding value, the SQL placeholders "%" or "_" can then also be used.

      Here is an example of how you can use the parameters:

      <key1>

      <col2 compareOperation="NEQ">val2old</col2>

      <col4 compareOperation="LIKE">val%</col4>

      </key1>

      "

      SELECT col1,col2,col3 FROM dbTableName  WHERE ((col2<>'val2old' AND col4 LIKE 'val%') OR (col2='val2old2'))

      "

    • hasQuot= YES|NO

      During construction of the WHERE condition of the SQL statement, the table column type determines whether the default is to set the values in quotation marks (text column types) or not (numerical column types). In a few cases (for example, when using functions), you may have to override this.

      • To always set quotation marks, set YES.
      • To never set quotation marks round values with this attribute in the SQL syntax, set NO.
    • isNull= TRUE

      Values with this attribute are ignored during construction of the WHERE condition. This attribute has the same effect as if the respective value did not exist.

Result

Response documents can only be evaluated by the Integration Server/PCK if the call is synchronous because the content of the response document is not accessible if the call is asynchronous.

The response is put in a separate element <StatementName_response> for each statement element.

The structure of the response documents is contained in the descriptions of the statements.