-- Generate experiment XML
select '<?xml version="1.0" encoding="UTF-8"?>'||
    xmlelement(name "EXPERIMENT_SET",
      xmlattributes(
        'http://www.w3.org/2001/XMLSchema-instance'
          as "xmlns:xsi",
        'http://www.ncbi.nlm.nih.gov/viewvc/v1/trunk/sra/doc/SRA/SRA.experiment.xsd?view=co'
          as "xsi:noNamespaceSchemaLocation"),
      xmlagg(ss.xml)) as xml
from (
  select e.title , st.alias , st.center_name , st.accession ,
    e.design_description , e.library_name , e.library_strategy ,
    e.library_source , e.library_selection , e.library_layout ,
    e.orientation , e.nominal_length , e.nominal_sdev , sa.alias ,
    sa.center_name , sa.accession , e.instrument_model ,
    e.cycle_sequence , e.cycles , e.base_caller, e.rna_id, e.bs_id,
    xmlelement(name "EXPERIMENT",
      xmlelement(name "TITLE", e.title),
      xmlelement(name "STUDY_REF",
        xmlattributes(
          st.alias as "refname",
          st.center_name as "refcenter",
          st.accession as "accession")),
      xmlelement(name "DESIGN",
        xmlelement(name "DESIGN_DESCRIPTION", e.design_description),
        xmlelement(name "SAMPLE_DESCRIPTOR",
          xmlattributes(
            sa.alias as "refname",
            sa.center_name as "refcenter",
            sa.accession as "accession")),
        xmlelement(name "LIBRARY_DESCRIPTOR",
          xmlelement(name "LIBRARY_NAME", e.library_name),
          xmlelement(name "LIBRARY_STRATEGY", e.library_strategy),
          xmlelement(name "LIBRARY_SOURCE", e.library_source),
          xmlelement(name "LIBRARY_SELECTION", e.library_selection),
          xmlelement(name "LIBRARY_LAYOUT",
            case when e.library_layout='single'
            then xmlelement(name "SINGLE")
            when e.library_layout='paired'
            then xmlelement(name "PAIRED",
              xmlattributes(
                e.orientation as "ORIENTATION",
                e.nominal_length as "NOMINAL_LENGTH",
                e.nominal_sdev as "NOMINAL_SDEV")) end)),
        xmlelement(name "SPOT_DESCRIPTOR",
          xmlelement(name "SPOT_DECODE_SPEC",
            xmlagg(
              xmlelement(name "READ_SPEC",
                xmlelement(name "READ_INDEX", rs2e.read_index),
                xmlelement(name "READ_LABEL", rs.read_label),
                xmlelement(name "READ_CLASS", rs.read_class),
                xmlelement(name "READ_TYPE", rs.read_type),
                xmlelement(name "BASE_COORD", rs.base_coord))
            order by rs2e.read_index)))),
      xmlelement(name "PLATFORM",
        xmlelement(name "ILLUMINA",
          xmlelement(name "INSTRUMENT_MODEL", e.instrument_model),
          xmlelement(name "CYCLE_SEQUENCE", e.cycle_sequence),
          xmlelement(name "CYCLE_COUNT", e.cycles))),
      xmlelement(name "PROCESSING",
        xmlelement(name "BASE_CALLS",
          xmlelement(name "SEQUENCE_SPACE", 'Base Space'),
          xmlelement(name "BASE_CALLER", e.base_caller)),
        xmlelement(name "QUALITY_SCORES",
          xmlattributes('phred' as "qtype"),
          xmlelement(name "QUALITY_SCORER", e.base_caller),
          xmlelement(name "NUMBER_OF_LEVELS", '80'),
          xmlelement(name "MULTIPLIER", '1'))),
      case when e.rna_id is not null or e.bs_id is not null then 
        xmlelement(name "EXPERIMENT_ATTRIBUTES",
          case when e.rna_id is not null then 
            xmlelement(name "EXPERIMENT_ATTRIBUTE",
              xmlelement(name "TAG", 'RNA ID'),
              xmlelement(name "VALUE", e.rna_id)) end,
          case when e.bs_id is not null then
            xmlelement(name "EXPERIMENT_ATTRIBUTE",
              xmlelement(name "TAG", 'Biological Sample ID'),
              xmlelement(name "VALUE", e.bs_id)) end) end) as xml
  from sra.submission_event se
  join sra.submission_data sd
    on se.id=sd.submission_event_id
  join sra.experiment e
    on e.id=sd.sra_id
  left join sra.study st
    on st.id=e.study_id
  left join sra.sample sa
    on sa.id=e.sample_id
  left join (read_spec2experiment rs2e
      join read_spec rs
        on rs2e.read_spec_id=rs.id)
    on rs2e.experiment_id=e.id
  where sd.sra_table='experiment'
    and se.id=:submission_event_id
  group by 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22) ss
\g |xmllint --format -|tee experiment.xml