Hi Armand,
That's some query! It's a bit hard to work through every line, but here's an illustration of how I would get the "branch" for a taxon. It only goes up 4 levels to make it clearer, but it can easily be extended. Perhaps you could create a view for this to make the report code much cleaner?
SELECT ITG1.Item_Level,
ISNULL(ITN4.Actual_Name + '\', '') +
ISNULL(ITN3.Actual_Name + '\', '') +
ISNULL(ITN2.Actual_Name + '\', '') +
ITN1.Actual_Name
FROM Index_Taxon_Name ITN1
LEFT JOIN (Index_Taxon_Group ITG1
INNER JOIN Index_Taxon_Name ITN2 ON ITN2.Taxon_List_Item_Key=ITG1.Taxon_List_Item_Key
) ON ITG1.Contained_List_Item_Key=ITN1.Taxon_List_Item_Key AND ITG1.Item_Level=1
LEFT JOIN (Index_Taxon_Group ITG2
INNER JOIN Index_Taxon_Name ITN3 ON ITN3.Taxon_List_Item_Key=ITG2.Taxon_List_Item_Key
) ON ITG2.Contained_List_Item_Key=ITN1.Taxon_List_Item_Key AND ITG2.Item_Level=2
LEFT JOIN (Index_Taxon_Group ITG3
INNER JOIN Index_Taxon_Name ITN4 ON ITN4.Taxon_List_Item_Key=ITG3.Taxon_List_Item_Key
) ON ITG3.Contained_List_Item_Key=ITN1.Taxon_List_Item_Key AND ITG3.Item_Level=3
WHERE ITN1.Taxon_List_Item_Key='***'
The sorting problem I think, unfortunately, is a problem in the way XML Reports are run in Recorder rather than within your XML file.
To export to KML, you need to include a spatial_ref, spatial_ref_system, lat and long column. Because your report renames the spatial_ref and spatial_ref_system column Recorder cannot detect that this is spatial data. Rather than renaming the columns in the SQL query, just use the caption of the <column> element to change the display caption.
Cheers
John van Breda
Biodiverse IT