-- DNA_mtDNA_locationslist.sql
-- a crude report of the mtDNA Test fact with locations in list format
-- 2011-06-18 by Tom Holden
-- copy and paste results into spreadsheet for sorting
 SELECT Person,
  REPLACE(
  REPLACE(
  REPLACE(
   REPLACE( 
    REPLACE(
     REPLACE(
      REPLACE(DNAtest,'<hvr', CAST(X'0D' AS TEXT) || '<hvr')
      ,'<location>',CAST(X'0D' AS TEXT))
     ,'</location>','')
    ,'</locations>','')
   ,'</hvr1>','')
   ,'</hvr2>','')
   ,'</hvr3>','')
  AS DNAtest
 FROM 
 (
 SELECT Surname||", "||Given AS Person, details AS DNAtest
 FROM EventTable LEFT JOIN NameTable USING(OwnerID) 
 WHERE NameType=0 AND EventType=901 AND Details LIKE '%mtDNA%'
 );