UPDATE Execution

When an UPDATE statement is executed, the component executes the MERGE method of the schema, where you can build the HTTP update request.

To see this method in a complete example, refer to Customizing Schemas.

Execute Updates to XML

In the MERGE method, the _input item, one of the Items in API Script, contains the columns to update. For example, consider the following statement:

UPDATE Persons 
SET Name='Ana Trujilo'
WHERE Id = '7'

You can use the _input item's attributes to set these column values in the HTTP update request. In the OData API, this can be an HTTP PUT or PATCH. The PUT data to make the preceding update in the OData API is below:

<entry xmlns:d="http://docs.oasis-open.org/odata/ns/data" xmlns:m="http://docs.oasis-open.org/odata/ns/metadata" xmlns="http://www.w3.org/2005/Atom">
  <id>http://services.odata.org/V4/OData/%28S%28tjlj1hwyun3kt2iv0ef21c2d%29%29/OData.svc//Persons(ID=4)</id>
  <category scheme="http://docs.oasis-open.org/odata/ns/scheme" term="ODataDemo.Person" />
  <content type="application/xml">
    <m:properties>
      <d:ID m:type="Edm.Int32">4</d:ID>
        <d:Name m:type="Edm.String">Ana Trujilo</d:Name>
    </m:properties>
  </content>
</entry>

In the example schema's corresponding MERGE method, the required column, the primary key, is validated and then used in the PUT data and the URL. You can check that an input was provided and alert the user otherwise with the api:validate keyword.

After validating that the needed attributes exist, you can set the column values in the PUT data. Set the "data" attribute to the PUT data -- the body of the api:set keyword is convenient for setting long or multiline values like this.

The api:call keyword invokes the operation that will make the HTTP request. Before invoking the operation, use api:set to set the method attribute to the HTTP method you want within the scope of the api:script keyword.

<api:script method="MERGE">
  <api:set attr="method" value="PUT"/>
  <api:validate attr="_input.Id" desc="An Id is required to update." />
  <api:set attr="data">
    <entry xmlns:d="http://docs.oasis-open.org/odata/ns/data" xmlns:m="http://docs.oasis-open.org/odata/ns/metadata" xmlns="http://www.w3.org/2005/Atom">
      <api:set attr="uri" value="[uri]([_input.Id])" />
      <id>[uri]</id>
      <category scheme="http://docs.oasis-open.org/odata/ns/scheme" term="ODataDemo.Person" />
      <content type="application/xml">
        <m:properties>
          <d:ID m:type="Edm.Int32">[_input.Id]</d:ID>
          <api:check attr="_input.Name">
            <d:Name m:type="Edm.String">[_input.Name]</d:Name>
          </api:check>
        </m:properties>
      </content>
    </entry>
  </api:set>
  <api:call op="xmlproviderGet"/>
</api:script>

Access the Components of UPDATE Statements

In the MERGE method, the _query item contains the following attributes:


query	The SQL statement, as in the following statement: UPDATE Account SET Name='John' WHERE Id = @myId
table	The table in the SQL statement. For example, Account in the preceding query.
criteria	The WHERE clause of the statement. For example, the following WHERE clause in the example: Id = @myId
nullupdates	The columns of an UPDATE statement, separated by commas, that contain a null value or a null parameter value.

Keyword Reference

See the API Script Reference for more information on the keywords used in this section:

SSIS Components for XML

UPDATE Execution

Execute Updates to XML

Access the Components of UPDATE Statements

Keyword Reference