Overview

Skill Level: Any

Prerequisites

This interface inserts or updates relational table data. For each Row that is passed
in:

  • If a row is found having the same key as the passed in row, update the record.
  • If no matching row is found, insert a new row that sets the column values to those passed in the request.

When rows are inserted or updated in the relational table, all Column values are set based on the values that are passed in the COLUMN elements.

Note: Only 100 rows can be passed in a single InsertUpdateRelationalTable call.

Step-by-step

  1. Syntax of the request and response XML for InsertUpdateRelationalTable

    Operation <InsertUpdateRelationalTable>
    Elements TABLE_ID The ID of the Acoustic Campaign Table that is to be updated.
      ROWS XML node that contains ROW elements for each row that is being inserted or updated.
      ChildElements ROW XML nodes that define a relational table row and its columns.
        ChildElements COLUMN The value of the Relational Table Column being inserted or updated.
          Attributes name The name of the Relational Table Column being inserted or updated.
    Example
    <Envelope>
      <Body>
        <InsertUpdateRelationalTable>
          <TABLE_ID>86767</TABLE_ID>
          <ROWS>
            <ROW>
              <COLUMN name=‚ÄĚRecord Id‚ÄĚ>
                <![CDATA[GHbjh73643hsdiy]]>
              </COLUMN>
              <COLUMN name=‚ÄĚPurchase Date‚ÄĚ>
                <![CDATA[01/09/1975]]>
              </COLUMN>
              <COLUMN name=‚ÄĚProduct Id‚ÄĚ>
                <![CDATA[123454]]>
              </COLUMN>
            </ROW>
            <ROW>
              <COLUMN name=‚ÄĚRecord Id‚ÄĚ>
                <![CDATA[WStfh73643hsdgw]]>
              </COLUMN>
              <COLUMN name=‚ÄĚPurchase Date‚ÄĚ>
                <![CDATA[02/11/1980]]>
              </COLUMN>
              <COLUMN name=‚ÄĚProduct Id‚ÄĚ>
                <![CDATA[45789]]>
              </COLUMN>
            </ROW>
            <ROW>
              <COLUMN name=‚ÄĚRecord Id‚ÄĚ>
                <![CDATA[Yuhbh73643hsfgh]]>
              </COLUMN>
              <COLUMN name=‚ÄĚPurchase Date‚ÄĚ>
                <![CDATA[05/10/1980]]>
              </COLUMN>
              <COLUMN name=‚ÄĚProduct Id‚ÄĚ>
                <![CDATA[4766454]]>
              </COLUMN>
            </ROW>
          </ROWS>
        </InsertUpdateRelationalTable>
      </Body>
    </Envelope>
    Response <RESULT>
    Elements SUCCESS True if the request is successfully received and processed.
      FAILURES XML node that contains FAILURE elements for each row that cannot be inserted or updated. This element and its children are not included if no failures are encouraged.
      ChildElements FAILURE Provides a type and description of each failure.
      Attributes failure_type Identifies whether the row could not be inserted or updated for a reason that is temporary. Valid values are:

    • transient
    • permanent
        description The description of the error.
        ChildElements COLUMN The value of the Relational Table Column being inserted or updated. A COLUMN is returned for each field of the record that could not be inserted.
        Attributes name The name of the Relational Table Column being inserted or updated that was not successful.
    Example
    <Envelope>
      <Body>
        <RESULT>
          <SUCCESS>true</SUCCESS>
          <FAILURES>
            <FAILURE failure_type="permanent" description="Invalid Date Value in column:
              Purchase Date">
              <COLUMN name="Record Id">WStfh73643hsdgw</COLUMN>
            </FAILURE>
            <FAILURE failure_type="permanent" description="Invalid Number in column: Price">
              <COLUMN name="Record Id">GHbjh73643hsdiy</COLUMN>
            </FAILURE>
          </FAILURES>
        </RESULT>
      </Body>
    </Envelope>

1 comment on"Insert and update records in a Relational table"

  1. Tom Coppedge March 15, 2019

    You may want to clarify that it’s not required to submit values for the optional relational table fields.

Join The Discussion

Your email address will not be published. Required fields are marked *