fm:query, fm:target-path

It is important to know whether your SQL statement will return:
A single result row - between 0 and 1 will only be returned, e.g. from DUAL
Multiple result rows – between 0 and N could be returned, e.g. from ALL_TABLES
Target path must be used when multiple rows are returned from a single run of a query.

SYNTAX

<fm:target-path match=”xpath”/>

CONCEPTS

<fm:target-path match="D/E/F"/> expression:

• In the above expression 'F' will represent one record (row) from the database
• Above, If 300 rows are returned, then 300 “F” elements will be created under “./D/E”
• This is a simple path, not an XPath, so no predicates or asterisk etc are allowed
• This simple path is relative to EACH Subject Element execution, here under “/A/B/C”- which is written in the run query match clause as seen below
• The target path match clause is appended to the match clause of the fm-query, providing the target path is not absolute
• Cannot use contexts in the target path. So simple path workaround, specify contexts on:
• <fm:run-query match=":{theme}"/>
• When we can guarantee a Single Result Row, can omit match as default match=”.” will deal with it
• If match=”.”, target-path is not specified and multiple rows are returned, then each result will overwrite the last, so this is disallowed
• An Absolute target path can be made from the root node which will ignore the match in the fm:run-query:

• E.G.  <fm:target-path match="/D/E/F"/> rather than <fm:target-path match="D/E/F"/> which means ./D/E/F

• Everything used in your fm:run-query must exist in the DOM
• Elements that are only for the DOM (don't need to be set-out) don't have to exist in your schema

 

EXAMPLES

1. Populate /*/TABLE_LIST/TABLE in the Data DOM:

1. fm:db-interface declaration

<fm:db-interface name="dbint-tab">
  <fm:query name="qry-tab">
  <fm:target-path match="/ROOT/TABLE_LIST/TABLE"/>
   <fm:select>
SELECT *
FROM   tab
   </fm:select>
  </fm:query>
</fm:db-interface>

2. fm:run-query command

<fm:run-query interface="dbint-tab" query="qry-tab"/>

2. Populate /*/TABLE_LIST/TABLE in the Theme DOM:

1. fm:db-interface declaration

<fm:db-interface name="dbint-tab">
   <fm:query name="qry-tab">
   <fm:target-path match="TABLE_LIST/TABLE"/>
    <fm:select>
SELECT *
FROM   tab
    </fm:select>
   </fm:query>
</fm:db-interface>

2. fm:run-query command

<fm:run-query interface="dbint-tab" query="qry-tab" match=":{theme}"/>

3. Populate :{theme}/ROLE_LIST using a key:

1. fm:db-interface declaration

<fm:db-interface name="dbint-web-roles">
   <fm:query name="qry-roles-key">
   <fm:target-path match="./ROLE_LIST/ROLE"/>
    <fm:select>
SELECT
id
, name
, description
FROM   appenv.web_roles
WHERE  app_id = :key
    </fm:select>
   <fm:using name="key">:{user}/WO_ID</fm:using>
   </fm:query>
</fm:db-interface>

2. fm:run-query command

<fm:run-query interface="dbint-web-roles" query="qry-roles-key" match=":{theme}"/>

EXERCISES

Please use your XX_EMPLOYEEMODULE (where XX are your initials) file for the following exercises.

Exercise 1

Populate the structure /*/EMPLOYEE_LIST/EMPLOYEE in the Data DOM with the ID, Forename, Surname and Hire_date from scott.employee_search where ID is less than 2,000. Run this query in action-change-date.

Exercise 2

Change the query to populate the Theme DOM instead.