Double click on the for icon

Then as your input use the xml from your query. Now create and xpath //item and assign it to a context of item.

Use the contex of item within the loop.

If u are using the database actor adapter then the result set will come back as xml. Take a look at the results and see if u can use it efficiently..if not transform it into something useful.

If you have a SQL SELECT that returns the following response, then in the For Loop you could apply a "BasicTransform" where you would specify one condition "Element Name Matches" with a value of "row".

<sqlAdapterResponse>

  <row>
    <column name="NAME" label="NAME">George W. Bush</column>
    <column name="YEARS_IN_OFFICE" label="YEARS_IN_OFFICE">8</column>
    <column name="FIRST_INAUGURATION" label="FIRST_INAUGURATION">2001</column>
    <column name="AGE_INAGURATION" label="AGE_INAGURATION">54</column>
    <column name="STATE_ELECTED_FROM" label="STATE_ELECTED_FROM">Texas</column>
    <column name="POLITICAL_PARTY" label="POLITICAL_PARTY">Republican</column>
    <column name="OCCUPATION" label="OCCUPATION">Businessman</column>
    <column name="PROCESSED" label="PROCESSED">false</column>
  </row>
  <row>
    <column name="NAME" label="NAME">Barack Obama</column>
    <column name="YEARS_IN_OFFICE" label="YEARS_IN_OFFICE">0</column>
    <column name="FIRST_INAUGURATION" label="FIRST_INAUGURATION">2009</column>
    <column name="AGE_INAGURATION" label="AGE_INAGURATION">47</column>
    <column name="STATE_ELECTED_FROM" label="STATE_ELECTED_FROM">Illinois</column>
    <column name="POLITICAL_PARTY" label="POLITICAL_PARTY">Democrat</column>
    <column name="OCCUPATION" label="OCCUPATION">Lawyer</column>
    <column name="PROCESSED" label="PROCESSED">false</column>
  </row>

This would setup the For Loop so that the output context item defined in the Opening For loop Icon, will have values similar to that shown below for each <row> returned by the query:

<row>
  <column name="NAME" label="NAME">Grover Cleveland</column>
  <column name="YEARS_IN_OFFICE" label="YEARS_IN_OFFICE">4</column>
  <column name="FIRST_INAUGURATION" label="FIRST_INAUGURATION">1885</column>
  <column name="AGE_INAGURATION" label="AGE_INAGURATION">47</column>
  <column name="STATE_ELECTED_FROM" label="STATE_ELECTED_FROM">New York</column>
  <column name="POLITICAL_PARTY" label="POLITICAL_PARTY">Democrat</column>
  <column name="OCCUPATION" label="OCCUPATION">Lawyer</column>
  <column name="PROCESSED" label="PROCESSED">false</column>
</row>

An alternative apporach here is to use the SQL Monitor Adapter with a configuration like:

<config>
  <targets>
    <target name="target1" default="true">
      <url>jdbc:oracle:thin:@127.0.0.1:1521:XE</url>
      <password>admin123</password>
      <user-name>BAO</user-name>
      <driver>oracle.jdbc.driver.OracleDriver</driver>
      <validate-connections-on-return>true</validate-connections-on-return>
      <validate-idle-connections>true</validate-idle-connections>
      <validate-query>select sysdate from dual</validate-query>
    </target>
  </targets>
  <event-statements>
    <event-statement>
      <query>SELECT * FROM US_PRESIDENTS WHERE PROCESSED = 'false'</query>
      <poll-interval>30</poll-interval>
      <single-monitor-event>false</single-monitor-event>
    </event-statement>
  </event-statements>
</config>

The example above runs every 30 Seconds and can create multiple single row or a single multi-row inputevent (set with the <single-monitor-event> value of true or false).  Below is an example of the inputevent value when the single-monitor-event is set to false:

<sql-monitor-event>
  <row>
    <column name="NAME">Barack Obama</column>
    <column name="YEARS_IN_OFFICE">0</column>
    <column name="FIRST_INAUGURATION">2009</column>
    <column name="AGE_INAGURATION">47</column>
    <column name="STATE_ELECTED_FROM">Illinois</column>
    <column name="POLITICAL_PARTY">Democrat</column>
    <column name="OCCUPATION">Lawyer</column>
    <column name="PROCESSED">false</column>
  </row>
  <metadata>
    <target-name>target1</target-name>
    <query-executed>SELECT * FROM US_PRESIDENTS WHERE PROCESSED = 'false'</query-executed>
    <event-timestamp-long>1268238933616</event-timestamp-long>
    <event-timestamp-string>2010-03-10 10:35:33.616</event-timestamp-string>
    <execution-milliseconds>94</execution-milliseconds>
    <row-number>21</row-number>
    <response-row-count>21</response-row-count>
    <response-column-count>8</response-column-count>
    <status>success</status>
  </metadata>
</sql-monitor-event>

Hello tonality,

From my personal experience I'd recommend you try to stick to "advanced" transformations as much as possible. Basic Transformations are nice, but may cause occasional problems.

Let's say you are passing the following list as a CTX input to the for-loop:

<sql-result>

  <row index="1">result1</row>

  <row index="2">result1</row>

  <row index="3">result1</row>

</sql-result>

To loop over the rows, you would apply the following xpath transformation:

//row

And assign that to an output CTX, for instance "Individual_Result".

Then, within your Loop, your "Individual_Result" CTX item would contain row1 in your first iterations. row2 in your second iteration etc.

Does this make any sense? If not, let me know.

  R.

Hold on, I will get you an example!

Let me know if the attached screenshots help:

Sample workflow:

Sample SQL Result:

Feeding into For-Loop:

Row within for-loop: