The following shows how to extract species records for Locations using a Common Table Expression (CTE) instead of the CURSOR and temporary table normally used.  CTE's were introduced in SQL server 2005 and this SQL will not work in SQL 2000.   
The query would need adapting for an XML report to pick up the key for the 'seed' location.

The final SELECT statement can be modified in many ways to  to pick up the other tables different columns etc.   

WITH subCTE
AS
(Select L.Location_key, 0 as lvl
from Location L
WHERE L.Location_key =   'LC00000010000001A'

UNION ALL

Select LP.Location_key, P.lvl + 1
from subCTE as P
JOIN Location LP ON
LP.Parent_key = P.Location_key
)
SELECT subcte.lvl,LN.Item_Name , ITN.ACTUAL_NAME, ITN.COMMON_NAME, ITN2.Actual_Name as Recommended_name, ITN2.Common_Name as Recommended_Common_Name FROM subCTE INNER JOIN Location_name LN
ON LN.Location_Key = subCTE.Location_Key AND LN.Preferred = 1
INNER JOIN Sample S ON S.Location_Key = LN.Location_Key
INNER JOIN Taxon_Occurrence TOCC
ON TOCC.Sample_key = S.Sample_key
INNER JOIN
Taxon_Determination TDET
ON TDET.taxon_Occurrence_key = TOCC.Taxon_Occurrence_Key
INNER JOIN
INDEX_Taxon_name ITN
ON ITN.Taxon_List_Item_Key =  TDET.Taxon_List_item_key
INNER JOIN Index_taxon_Name ITN2
ON ITN2.taxon_List_Item_Key= ITN.taxon_List_Item_Key
order by subcte.lvl,Ln.Item_name, ITN2.actual_name

Mike