Start of Content Area

Procedure documentation Defining XML Documents for Message Protocol XML SQL Format  Locate the document in its SAP Library 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.

See: Message Mappings

Defining the XML Document Structure

       2.      Define a <root> tag. You can use a name of your choice for the tag.

       3.      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.

       4.      Enter the name of the database table in the element under the statement element (dbTableName) and the attribute action with the value UPDATE, INSERT, UPDATE_INSERT, DELETE,SELECT, EXECUTE, or SQL-QUERY.

       5.      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> must be the first element in the block within <dbTableName>.

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.

       6.      At the next level there is (except for in the DELETEaction) an element with the name accessand 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.

       7.      Define one or more statements for editing database tables:

       Defining an UPDATE Statement

       Defining an INSERT Statement

       Defining an UPDATE_INSERT Statement

       Defining a DELETE Statement

       Defining a SELECT Statement

       Defining an EXECUTE Statement

       Defining an SQL_QUERY Statement

Defining Attributes in the <key> Elements

       8.      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.

 

 

 

 

End of Content Area