fm:query, fm:into

The query syntax is enhanced to allow virtually anything to be placed into the Data DOM anywhere.

CONCEPTS

The Resultant Row’s Data Target Items (as defined in SELECT part of an SQL statement):

• Selected column alias has to be the same as the default Item Target Path (E.G. for SELECT emp.empno “ID” for XPath EMPLOYEE/ID)
• Item Target Path is a simple path relative to each Target Record Element (“F” in structure as in Chapter 28)
• Item Target Path points to a single leaf element (item), and path is created when missing
• Oracle complex column aliases are allowed (E.G. SELECT emp.id “STAFF/NO”, emp.deptno “STAFF/DEPT”)
• Simple nested collections (cardinality one) can be created as above
• Duplicate Item Target Paths would overwrite data. Senseless but FOX allows this
• No limit on columns that can be selected
• Default data type conversion is to string (item text context). Date to xs:date/xs:datetime

SYNTAX

<fm:into datadom-location="XPath" datadom-type="dom" name="column_alias" sql-type="xmltype"/>

Target Items can be defined more rigorously using the fm:into tag and its attributes:

• name is the SELECT clause column alias – Remember Oracle implicit aliases are uppercased
• datadom-location allows longer Target Item Paths  as Oracle alias is 30 characters max
• sql-type pre-declares the resultant SQL data type – not always required as Oracle knows this
• datadom-type defined desired format in data DOM

NB:

• Selecting XML, use sql-type=”xmltype” datadom-type=”dom” – Important otherwise the data will be returned as an xml string
• Selecting Large Objects, use sql-type=”clob” datadom-type=”xs:string” (or dom)

EXAMPLES

• An fm:query to return a whole XML structure:

<fm:query name="qry-get-business-stage">
  <fm:select>
SELECT bs.xml_data
FROM   appenv.business_stages bs
WHERE  bs.end_datetime IS NULL
AND    bs.ba_id = (SELECT MAX(id)
FROM business_stages)            
  </fm:select>
  <fm:into datadom-location=":{theme}/BUSINESS_STAGE" datadom-type="dom"   name="XML_DATA" sql-type="xmltype"/>
</fm:query>

• An fm:query to pre-populate some of the data structure when starting a new PON15B:

<fm:db-interface name="dbint-fields">
  <fm:query name="qry-fields">
  <fm:select>
SELECT
WFS.quadrant_no "QUADRANT_NO"
, WFS.block_no "BLOCK_NO"
, To_Char(IDA.Prop_Spud_Date, 'YYYY-MM-DD') "SPUD_DATE1"
, To_Char(IDA.Prop_Spud_Date, 'YYYY-MM-DD') "SPUD_DATE2"
, To_Char(IDA.Prop_Spud_Date+30, 'YYYY-MM-DD') "END_DATE"
FROM appenv.wells WL, appenv.well_files WF, appenv.well_file_sections WFS, appenv.well_file_entries WFE, appenv.W_Ida_page2 IDA, appenv.web_user_accounts WUA, appenv.addresses ADDR, appenv.web_organisations WO
WHERE WFS.id = WFE.wfs_id
AND   WFS.wf_id = WF.ID
AND   WFS.wl_codasyl_key = wl.CODASYL_KEY(+)
AND   WF.wo_id = WO.id
AND   WFE.id = IDA.wfe_id
AND   WO.id = WUA.wo_id
AND   WUA.addr_id = ADDR.id
AND   WFS.section_no = 1
AND   WUA.id =  100
AND   WF.id = 520
  </fm:select>
  <fm:into name="QUADRANT_NO" datadom-type="xs:string" datadom-location="/PON15B/GENERAL_INFO/QUADRANT_NO"/>
  <fm:into name="BLOCK_NO" datadom-type="xs:string" datadom-location="/PON15B/GENERAL_INFO/BLOCK_NO"/>
  <fm:into name="SPUD_DATE1" datadom-type="xs:string" datadom-location="/PON15B/WELL_INFO/SPUD_DATE"/>
  <fm:into      name="SPUD_DATE2" datadom-type="xs:string" datadom-location="/PON15B/CHEMICAL_USAGE/CHARM_VERSiON/START_DATE"/>
  <fm:into      name="END_DATE" datadom-type="xs:string" datadom-location="/PON15B/CHEMICAL_USAGE/CHARM_VERSION/END_DATE"/>
  </fm:query>
</fm:db-interface>         

• Query to recreate a map-set/rec repeating structure:

<fm:db-interface name="dbint-devukrefcodes">
  <fm:query name="qry-piptyp">
  <fm:target-path match="map-set/rec"/>
  <fm:select>
Select Drc.Rv_Low_Value "data", Drc.Rv_Meaning "key"
From   Appenv.Devuk_Ref_Codes Drc
Where  Drc.Rv_Domain = 'PIPTYP'
Order By Drc.Rv_Meaning
  </fm:select>
  </fm:query>
</fm:db-interface>

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 Theme DOM with the ID, Forename, Surname and Hire_date from scott.employee_search where ID is between 3,000 and 7,000 using Oracle XML functions to aggregate the data into one XML Type using fm:into. Run this query in action-change-date.