Load Application Data During an Interview
The ExecuteQuery action can be broken down into the following steps:
- An ExecuteQuery request is sent to a web service connector during an interview to load data that matches the query parameters provided.
- An ExecuteQuery response returns the tables matching the query.
An ExecuteQuery request has:
- an
execute-query-requestelement that has the same format as the Load request action (that is, arootattribute, aregionattribute, alanguageattribute and atimezoneattribute), as well as an optionalresult-limitattribute that specifies the maximum number of rows in the root table that should be returned - an expression that specifies which rows in the root table should be returned. For more information on these parameters, see ExecuteQuery request.
- a
tableselement that has the same format as the Load request action and describes the data being requested
An ExecuteQuery response has:
- a
tableselement that has the same format as the Load response action and contains the list of tables matching the query.
Note that any data loaded using ExecuteQuery will never be returned in a Save response. That is, all the data is read-only and cannot be output-mapped.
An example of an ExecuteQuery request is:
<execute-query-request root="Track" region="en-AU" language="en-GB" timezone="Australia/Sydney" result-limit="5">
<and-expr>
<comparison-expr>
<field>Price</field>
<operator>LessThanEquals</operator>
<value>
<number-val>1.5</number-val>
</value>
</comparison-expr>
<comparison-expr>
<field>Name</field>
<operator>Contains</operator>
<value>
<text-val>Black</text-val>
</value>
</comparison-expr>
</and-expr>
<tables>
<table name="Track">
<field name="Name" field-type="text"/>
<field name="Artist" field-type="text"/>
<field name="Album" field-type="text"/>
<field name="Genre" field-type="text">
<field name="Price" field-type="number"/>
<link name="TrackRatings" target="Rating"/>
</table>
<table name="Rating">
<field name="UserName" field-type="text"/>
<field name="Rating" field-type="number"/>
</table>
</tables>
</execute-query-request>
The example above describes a query to load all rows from the 'Track' table where the track's title contains the word "Black" and whose price is less than or equal to $1.50. It also specifies that for each matching track, load their related ratings from the 'Rating' table. An example response to this request is shown below:
<execute-query-response>
<tables>
<table name="Track">
<row id="1">
<field name="Name">
<text-val>Paint It Black</text-val>
</field>
<field name="Artist">
<text-val>The Rolling Stones</text-val>
</field>
<field name="Album">
<text-val>Aftermath</text-val>
</field>
<field name="Genre">
<text-val>Rock</text-val>
</field>
<field name="Price">
<number-val>0.99</number-val>
</field>
<link name="TrackRatings" target="Rating">
<ref id="1"/>
<ref id="7"/>
</link>
</row>
<row id="2">
<field name="Name">
<text-val>Black</text-val>
</field>
<field name="Artist">
<text-val>Perl Jam</text-val>
</field>
<field name="Album">
<text-val>Ten</text-val>
</field>
<field name="Genre">
<text-val>Grunge</text-val>
</field>
<field name="Price">
<number-val>1.26</number-val>
</field>
<link name="TrackRatings" target="Rating">
<ref id="2"/>
<ref id="4"/>
</link>
</row>
<row id="3">
<field name="Name">
<text-val>Black Dog</text-val>
</field>
<field name="Artist">
<text-val>Led Zeppelin</text-val>
</field>
<field name="Album">
<text-val>IV</text-val>
</field>
<field name="Genre">
<text-val>Rock</text-val>
</field>
<field name="Price">
<number-val>1.49</number-val>
</field>
<link name="TrackRatings" target="Rating">
<ref id="5"/>
</link>
</row>
<row id="4">
<field name="Name">
<text-val>Black Hole Sun</text-val>
</field>
<field name="Artist">
<text-val>Soundgarden</text-val>
</field>
<field name="Album">
<text-val>Superunknown</text-val>
</field>
<field name="Genre">
<text-val>Grunge</text-val>
</field>
<field name="Price">
<number-val>0.75</number-val>
</field>
<link name="TrackRatings" target="Rating">
<ref id="3"/>
</link>
</row>
<row id="5">
<field name="Name">
<text-val>Back to Black</text-val>
</field>
<field name="Artist">
<text-val>Amy Winehouse</text-val>
</field>
<field name="Album">
<text-val>Back to Black</text-val>
</field>
<field name="Genre">
<text-val>Soul</text-val>
</field>
<field name="Price">
<number-val>0.99</number-val>
</field>
<link name="TrackRatings" target="Rating">
<ref id="6"/>
</link>
</row>
</table>
<table>
<row id="1">
<field name="UserName">
<text-val>MusicLover1</text-val>
</field>
<field name="Rating">
<number-val>5</number-val>
</field>
</row>
<row id="2">
<field name="UserName">
<text-val>90sKid</text-val>
</field>
<field name="Rating">
<number-val>5</number-val>
</field>
</row>
<row id="3">
<field name="UserName">
<text-val>90sKid</text-val>
</field>
<field name="Rating">
<number-val>5</number-val>
</field>
</row>
<row id="4">
<field name="UserName">
<text-val>MusicLover1</text-val>
</field>
<field name="Rating">
<number-val>5</number-val>
</field>
</row>
<row id="5">
<field name="UserName">
<text-val>90sKid</text-val>
</field>
<field name="Rating">
<number-val>4</number-val>
</field>
</row>
<row id="6">
<field name="UserName">
<text-val>SoulTrain</text-val>
</field>
<field name="Rating">
<number-val>5</number-val>
</field>
</row>
<row id="7">
<field name="UserName">
<text-val>SoulTrain</text-val>
</field>
<field name="Rating">
<number-val>1</number-val>
</field>
</row>
</table>
</tables>
</execute-query-response>