XML templates

This page lists the XML Reports I have written for Recorder 6¹²

XML template

<-- http://server/XML template.xml -->
<?xml version='1.0'?>
<ROOTELEMENTNAME -xmlns:sql="urn:schemas-microsoft-com:xml-sql'>
  <sql:header>
     <-- parameters -->
  </sql:header>
  <sql:query>
     <-- SQL -->
  </sql:query>
</ROOTELEMENTNAME>

---

XML view

Recorder 6 XML template

<?xml version='1.0' ?>
<!-- Example of XML Report written for Recorder 6.13 -->
<CustomReport  title="TITLE"  
               menupath="FOLDER\FOLDER2"  
               description="Example report ">
<!-- External CSV file converted to temporaryTable -->
<CSVTables>
  <CSVTable name="#TemporaryTableName" />
  <CSVFile description="RequestMessage" />
  <CSVColumn name = "Fieldname1" datatype="VarChar" size="100" />
  <CSVColumn name = "FieldName2" datatype=VarChar" size="30"   />
</CSVTable>

<!-- External filterfile converted to temporaryTable -->
<externalfilterfiles>
    <externalfilterfile inputfile="mylastimport.ref"  tablename="#lTemporaryTableName" />
</externalfilterfiles>

<SQL>

declare @VariableName DATATYPE
set @VariableName ='VALUE'

SELECT KeyColumnName, fieldname1, fieldname2 FROM table1 

<!-- Conditions -->
    <Where keytype="Default">
           WHERE 
          <Condition field="table1.fieldname1" 
                     operator="{equal | not equal | like | less than | greater than |less than equal | greater than equal}"                      type="{Text | Number | Date | VagueDate | TrueFalse | Location | Name | Individual | Organisation | Taxon | BoundingBox | GridSquareRange | SpatialRef | LocationsinPolygon | SamplesinPolygon | OptionSet " 
                     SubstitituteFor = "{n}"
                     includepartialoverlap = "{yes | no}"
                     name="ConditionName" 
                     entrycount="{-1 | 1 | n}"
                     userucksack = "{yes | no | optional}"
                     useoneofgroup = "GROUP NAME"
                     datatype />
   </Where>
<!-- Quick Reports -->
   <Where keytype="Location">
      WHERE Location_Key = ' %s'       
   </Where>
<!-- Sort -->
   <Orderby name="by fieldname1 " SQL="Order By FieldName1" /> 
</SQL>
<!-- Columns -->
<Columns>  
    <Column name="fieldname1" width="100" position="2" caption="FieldName2" /> 
    <Column name="fieldname2" width="60" position="1" caption="FieldName1" /> 
    <Column name="KeyColumnName" visible="False" tablename="{Survey | Event | Sample | Taxon_Occurrence | Biotope_Occurrence | Location | Name | Location | Feature | Document | Taxon |  | Biotope | Admin}"
</Columns>
</CustomReport>

---

Code Examples

NBN_UserAddedTaxonVersionKeys

This XML report extracts information on non system supplied taxonversionkeys. Note there are currently a number of system supplied taxonversionkeys that are flagged as non system supplied in Recorder 6

<?xml version='1.0' ?>

 <!-- User added taxonversionkeys
     XML Report by Graham French, NBN Technical Liaison Officer
      Version 1 19/02/2009
-->
<CustomReport title="UserAddedTaxonVersionKeys" menupath="JNCC\Housekeeping Reports"  
description="This report extracts information on taxonversionkeys entered by user and not supplied by Recorder 6" >
<SQL>
    SELECT TV.taxon_version_key AS TaxonVersionKey, T.item_name AS TaxonName FROM TAXON_VERSION TV
     INNER JOIN TAXON T ON TV.taxon_key = T.taxon_key
       WHERE TV.system_supplied_data = 0
    <Where keytype="Default">
     </Where>
</SQL>
<Columns>  
    <Column name="TaxonVersionKey" width="150" position="1" caption="TaxonVersionKey" />
    <Column name="TaxonName" width="200" position="2" caption="TaxonName" />  
</Columns>
</CustomReport>

---

NBN_TaxonDetails

This XML Report shows the details for each recorded taxon, giving actual recorded name, preferred name, species list and number of records

<?xml version='1.0' ?>

 <!-- Taxon details
     XML Report by Graham French, NBN Technical Liaison Officer
      Version 1.1.2 27/08/2009
-->
<CustomReport title="Details for recorded taxa" menupath="NBN"  
description="This report gives details of which taxa have been used for all the records currently in Recorder 6" >
<SQL>
    SELECT t.item_name as TaxonName, tl.item_name AS ListName, tv.taxon_version_key as taxonversionkey, count(toc.taxon_occurrence_key) AS RecordNumber FROM taxon_occurrence toc
    INNER JOIN taxon_determination td ON toc.taxon_occurrence_key = td.taxon_occurrence_key
    INNER JOIN taxon_list_item tli ON td.taxon_list_item_key = tli.taxon_list_item_key
    INNER JOIN taxon_list_version tlv ON tli.taxon_list_version_key = tlv.taxon_list_version_key
    INNER JOIN taxon_list tl ON tlv.taxon_list_key = tl.taxon_list_key
    INNER JOIN taxon_version tv ON tli.taxon_version_key = tv.taxon_version_key
    INNER JOIN taxon t ON tv.taxon_key = t.taxon_key
    WHERE td.preferred = 1
    GROUP BY tl.item_name, t.item_name, tv.taxon_version_key
    ORDER BY TaxonName  
    <Where keytype="Default" />
</SQL>
<Columns>  
    <Column name="TaxonName" width="200" position="1" caption="Taxon Name" />
    <Column name="ListName" width="400" position="2" caption="List Name" />
    <Column name="TaxonVersionKey" width="150" position="3" caption="TaxonVersionKey" />
    <Column name="RecordNumber" width="150" position="4" caption="Number of Records" />
</Columns>
</CustomReport>

---

Footnotes

1. Reports need to be saved as .xml into the Reports folder of Recorder (typically C:\Program Files\Recorder 6\User Files\Reports)

2. Information on how to write this reports can be found in a XML report help file written by Mike Weideli