Export (0) Print
Expand All

Message Schemas for the Basic Insert, Update, Delete, and Select Operations on Tables and Views

The Microsoft BizTalk Adapter for Oracle Database surfaces basic Insert, Update, Delete, and Select operations for each table and view in the Oracle database. These operations perform the appropriate SQL statement qualified by a WHERE clause. The Oracle Database adapter uses strongly-typed records and record sets in these operations.

The following table shows the XML message structure for the basic table operations exposed by the Oracle Database adapter on Oracle database tables. The target table for an operation is specified in the message action and also appears in the target namespace.

Operation XML Message Description SQL Executed by the Adapter

Insert

Multiple Record Insert:

<Insert xmlns="[VERSION]/[SCHEMA]/Table/[TABLE_NAME]">
  <RECORDSET>
    <[TABLE_NAME]RECORDINSERT>
      <[FIELD1_NAME InlineValue="value"]>value1</[FIELD1_NAME]>
      <[FIELD2_NAME InlineValue="value"]>value2</[FIELD2_NAME]>
      …
    </[TABLE_NAME]RECORDINSERT>
 
    <[TABLE_NAME]RECORDINSERT >
      <[FIELD1_NAME InlineValue="value"]>value1</[FIELD1_NAME]>
      <[FIELD2_NAME InlineValue="value"]>value2</[FIELD2_NAME]>
      …
    </[TABLE_NAME]RECORDINSERT>
    …
  </RECORDSET>
</Insert>

Bulk Insert:

<Insert xmlns="[VERSION]/[SCHEMA]/Table/[TABLE_NAME]">
  <COLUMN_NAMES>[COLUMN_list]</COLUMN_NAMES>
  <QUERY>[SELECT_query]</QUERY>
</Insert>

There are two types of Insert operations:

  • Multiple Record Insert inserts the supplied record set of strongly-typed data into the target table.

    For each record in a multiple record insert, you can specify value for an optional attribute called InlineValue. If specified, it overrides the value of the element.

  • Bulk Insert inserts the record set returned by a SELECT query specified in the QUERY element into the target table. This is done by using the comma-separated list of columns specified in the COLUMN_NAMES element.

A message can contain only one kind of Insert operation.

  • Multiple Record Insert

    INSERT INTO TABLE_NAME (FIELD1_NAME, FIELD2_NAME, …)VALUES (value1, value2, …);
    
  • Bulk Insert

    INSERT INTO TABLE_NAME (COLUMN_list) SELECT_query;
    

Insert Response

<InsertResponse xmlns="[VERSION]/[SCHEMA]/Table/[TABLE_NAME]">
  <InsertResult>[rows inserted]</InsertResult>
</InsertResponse>

The number of rows inserted is returned in the InsertResult element.

--

Select

<Select xmlns="[VERSION]/[SCHEMA]/Table/[TABLE_NAME]">
  <COLUMN_NAMES>[COLUMN_list]</COLUMN_NAMES>
  <FILTER>WHERE_clause</FILTER>
</Select>

A SELECT query is performed on the target table using the WHERE clause specified in the FILTER element. The result set contains the columns in the comma-separated list of column names specified in the COLUMN_NAMES element.

SELECT COLUMN_list FROM TABLE_NAME WHERE WHERE_clause;

Select Response

<SelectResponse  xmlns="[VERSION]/[SCHEMA]/Table/[TABLE_NAME]">
  <SelectResult>
    <[TABLE_NAME]RECORDSELECT>
      <[FIELD1_NAME]>value1</[FIELD1_NAME]>
      <[FIELD2_NAME]>value2</[FIELD2_NAME]>
      …
    </[TABLE_NAME]RECORDSELECT>
  </SelectResult>
</SelectResponse>

The result set generated by the SELECT query.

--

Update

<Update xmlns="[VERSION]/[SCHEMA]/Table/[TABLE_NAME]">
  <RECORDSET>
    <[FIELD1_NAME]>value1</[FIELD1_NAME]>
    <[FIELD2_NAME]>value2</[FIELD2_NAME]>
      …
  </RECORDSET>
  <FILTER>WHERE_clause</FILTER>
</Update>

Rows that match the where clause specified in the FILTER element are updated to the values specified in the RECORDSET. Only the columns that are specified in the RECORDSET are updated in each matching row.

UPDATE [TABLE_NAME] SET [FIELD1_NAME] = value1, [FIELD2_NAME] = value2, … WHERE WHERE_clause;

Update Response

<UpdateResponse xmlns="[VERSION]/[SCHEMA]/Table/[TABLE_NAME]">
  <UpdateResult>[rows inserted]</UpdateResult>
</UpdateResponse>

The number of rows updated is returned in the UpdateResult element.

--

Delete

<Delete xmlns="[VERSION]/[SCHEMA]/Table/[TABLE_NAME]">
  <FILTER>WHERE_clause</FILTER>
</Delete>

Rows matching the WHERE clause specified by the FILTER element are deleted.

DELETE FROM [TABLE_NAME] WHERE WHERE_clause;

Delete Response

<DeleteResponse xmlns="[VERSION]/[SCHEMA]/Table/[TABLE_NAME]">
  <DeleteResult>[rows inserted]</DeleteResult>
</DeleteResponse>

The number of rows deleted is returned in the DeleteResult element.

--

[VERSION] = The message version string; for example, http://Microsoft.LobServices.OracleDB/2007/03.

[SCHEMA] = Collection of Oracle artifacts; for example, SCOTT.

[TABLE_NAME] = Name of the table; for example, EMP.

[FIELD1_NAME] = Table field name; for example, EMPNAME.

[COLUMN_list] = Comma-separated list of columns; for example, NAME.

[SELECT_query] = A SQL SELECT statement specified in the QUERY element of a Bulk Insert operation; for example, "SELECT * from MyTable"

[WHERE_clause] = WHERE_clause for the SELECT statement used for the operation; for example, ID > 10.

Dd788156.Important(en-us,BTS.70).gifImportant
The message structure for the basic table operations on views is the same as that on tables, but the namespace for the operation specifies a view rather than a table: <Insert xmlns ="[VERSION]/[SCHEMA]/View/[VIEW_NAME]">.

The following table shows the message actions that are used by the Oracle Database adapter for the basic table operations on tables. The Oracle Database adapter uses the table name specified in the message action to determine the target table of the operation.

Operation Message Action Example

Insert

[VERSION]/[SCHEMA]/Table/[TABLE_NAME]/Insert

http://Microsoft.LobServices.OracleDB/2007/03/SCOTT/Table/EMP/Insert

Insert Response

[VERSION]/[SCHEMA]/Table/[TABLE_NAME]/Insert/response

http://Microsoft.LobServices.OracleDB/2007/03/SCOTT/Table/EMP/Insert/response

Select

[VERSION]/[SCHEMA]/Table/[TABLE_NAME]/Select

http://Microsoft.LobServices.OracleDB/2007/03/SCOTT/Table/EMP/Select

Select Response

[VERSION]/[SCHEMA]/Table/[TABLE_NAME]/Select/response

http://Microsoft.LobServices.OracleDB/2007/03/SCOTT/Table/EMP/Select/response

Update

[VERSION]/[SCHEMA]/Table/[TABLE_NAME]/Update

http://Microsoft.LobServices.OracleDB/2007/03/SCOTT/Table/EMP/Update

Update Response

[VERSION]/[SCHEMA]/Table/[TABLE_NAME]/Update/response

http://Microsoft.LobServices.OracleDB/2007/03/SCOTT/Table/EMP/Update/response

Delete

[VERSION]/[SCHEMA]/Table/[TABLE_NAME]/Delete

http://Microsoft.LobServices.OracleDB/2007/03/SCOTT/Table/EMP/Delete

Delete Response

[VERSION]/[SCHEMA]/Table/[TABLE_NAME]/Delete/response

http://Microsoft.LobServices.OracleDB/2007/03/SCOTT/Table/EMP/Delete/response

[VERSION] = The message version string; for example, http://Microsoft.LobServices.OracleDB/2007/03.

[SCHEMA] = Collection of Oracle artifacts; for example, SCOTT.

[TABLE_NAME] = Name of the table; for example, EMP.

Dd788156.Important(en-us,BTS.70).gifImportant
The message action for an operation on a view is the same as that for a table except that "View" replaces "Table"; for example, http://Microsoft.LobServices.OracleDB/2007/03/SCOTT/View/EMPVIEW/Insert.

Show:
© 2014 Microsoft