使用LISTAGG，XMLAGG函数合并行，使用xmltable获取xmlType数据

select JIANGLIPUBLICSITUATION.get_area_name(o.area_no) as address,t.zh_title as zh_title,JIANGLIPUBLICSITUATION.get_main_person_info(t.prp_code) as main_person,t.main_org as main_org,extractvalue(pe.prp_xml, ''data/prp_extend/project_profile'') as project_profile,paper.paper_names as paper_names,patent.cnames as cnames,extractvalue(pe.prp_xml, ''data/proposal/extensionapplication'') as application_situation  from proposal t
                      left join proposal_extend pe on pe.prp_code=t.prp_code
                      left join
                      (SELECT prp_code,
                      LISTAGG(paper_name, ''；'') WITHIN GROUP(ORDER BY paper_name) AS paper_names
                      FROM (select pe.prp_code as prp_code,t.* from proposal_extend pe,xmltable (''data/represent_papers/represent_paper'' passing pe.prp_xml columns paper_name varchar2(500) path ''paper_name'') t
                      )
                      GROUP BY prp_code) paper on t.prp_code=paper.prp_code
                      left join
                      (SELECT prp_code,
                      LISTAGG(cname, ''；'') WITHIN GROUP(ORDER BY cname) AS cnames
                      FROM (select pe.prp_code as prp_code,t.* from proposal_extend pe,xmltable (''data/patents/patent'' passing pe.prp_xml columns cname varchar2(500) path ''cname'') t
                      )
                      GROUP BY prp_code) patent on t.prp_code=patent.prp_code
                      left join organization o on o.org_code=t.recommend_org_code
                      left join organization org on org.org_code=t.org_code
                      left join const_area c on c.area_no=o.area_no
                      left join person p on t.psn_code=p.psn_code
                      left join proposal_cached pc on t.pos_code=pc.pos_code
                      left join org_department od on t.dept_code=od.dept_code

                      where t.grant_code=30 and t.major_review_code is not null

如果listagg拼接字符串超过最大长度，可使用xmlagg处理

select xmlagg(xmlparse(content to_char(s.create_date,'yyyy-mm-dd hh24:mi:ss')||';' wellformed) order by s.create_date).getclobval()
from person s where s.log_type='login' and s.key_code=t1.psn_code

xmltable用法如下

select pe.prp_code as prp_code,t.* from proposal_extend pe,
      xmltable (
        'data/persons/person' passing pe.prp_xml columns
        ranking varchar2(50) path '@seq_no',
        cname varchar2(50) path 'cname',
        prof_title_name varchar2(50) path 'prof_title_name',
        org_name varchar2(100) path 'org_name',
        org_finish_name varchar2(100) path 'org_finish_name',
        work_result_one varchar2(1000) path 'work_result_one'
      ) t
where pe.prp_code=v_prp_code