SQL и XML

[D-mon]

Привет всем. Такая задача с которой столкнулся? но ни разу не делал. Надо слепить несколько связных таблиц (Oracle), и записать их в CLOB поле в виде XML. Спасибо.

[edgy]

Посмотри здесь. Вроде как раз был пример по твоей тематике.

[D-mon]

Разобрался. В от как пример (большой получился).

DECLARE
  
CURSOR crs_Terrorist IS
       SELECT trs_id, trs_number, trs_ins_dt, trs_record_tp, trs_tp, trs_ju_text,
              trs_search_text, trs_add_notes, trs_rec_notes, trs_del_dt 
       FROM terrorist;
CURSOR crs_TRSName(TRSID TRSName.Trsn_Trs%TYPE) IS
       SELECT trsn_name1, trsn_name2, trsn_name3, trsn_name4, trsn_aka_tp, trsn_aka_ct, trsn_aka_ql 
       FROM TRSName
       where trsn_trs=TRSID;        
CURSOR crs_TRSDocument(TRSID TRSDocument.Trsd_Trs%TYPE) IS
       SELECT trsd_cn, trsd_number, trsd_issue_date, trsd_issue_country, trsd_issue_org, trsd_ident_code
       FROM TRSDocument
       where trsd_trs=TRSID;

CURSOR crs_TRSParam(TRSID TRSParam.Trsp_Trs%TYPE) IS
       select trsp_stringvalue, opt_extnum
       from ObjectParamType, TRSParam
       where opt_id=trsp_opt(+)
         and trsp_trs=TRSID;
                   
BEGIN
 DBMS_OUTPUT.enable;
 DBMS_OUTPUT.put_line('<?xml version="1.0" encoding="UTF-8"?>');
 DBMS_OUTPUT.put_line('<!DOCTYPE transport-file SYSTEM "tr_list10.dtd">');
 DBMS_OUTPUT.put_line('<transport-file>');
 DBMS_OUTPUT.put_line('  <file-meta-data>');
 DBMS_OUTPUT.put_line('    <file-name>XY0ILPAQ.601</file-name>');
 DBMS_OUTPUT.put_line('    <file-date>'||TO_CHAR(SYSDATE,  'YYYYMMDD')||'</file-date>');
 DBMS_OUTPUT.put_line('    <file-time>'||TO_CHAR(SYSDATE,  'MIHH24')||'</file-time>');
 DBMS_OUTPUT.put_line('    <id-key>0QMX00</id-key>');
 DBMS_OUTPUT.put_line('    <file-signature>2C8C8A5B6B89AA0F7BD963744D16045AED5E6793E2077E9CC24202CBEF4F5726D6D4FDCD9648FBD6FF89BE792E05B84AAF8782B62173FE93EC4598E414BEFDA0</file-signature>');
 DBMS_OUTPUT.put_line('  </file-meta-data>');
 DBMS_OUTPUT.put_line('  <list-terror>'); 
 DBMS_OUTPUT.put_line('    <count-record>'||'346'||'</count-record>');
 DBMS_OUTPUT.put_line('    <ver-list>1</ver-list>');
 DBMS_OUTPUT.put_line('    <date-ver-list>'||TO_CHAR(SYSDATE,  'YYYYMMDD')||'</date-ver-list>');
 DBMS_OUTPUT.put_line('    <type-list>1</type-list>');
 FOR TRS IN crs_terrorist LOOP
  DBMS_OUTPUT.put_line('     <Terrorist>');
  DBMS_OUTPUT.put_line('       <trs_number>'||TO_CHAR(TRS.trs_number)||'</trs_number>');
  DBMS_OUTPUT.put_line('       <trs_ins_dt>'||TRS.trs_ins_dt||'</trs_ins_dt>');
  DBMS_OUTPUT.put_line('       <trs_record_tp>'||TO_CHAR(TRS.trs_record_tp)||'</trs_record_tp>');
  DBMS_OUTPUT.put_line('       <trs_tp>'||TRS.trs_tp||'</trs_tp>');
  DBMS_OUTPUT.put_line('       <trs_search_text>'||TO_CHAR(TRS.trs_search_text)||'</trs_search_text>');
  DBMS_OUTPUT.put_line('       <trs_add_notes>'||TRS.trs_add_notes||'</trs_add_notes>');
  DBMS_OUTPUT.put_line('       <trs_rec_notes>'||TO_CHAR(TRS.trs_rec_notes)||'</trs_rec_notes>');
  DBMS_OUTPUT.put_line('       <trs_del_dt>'||TRS.trs_del_dt||'</trs_del_dt>');
   FOR TRSName IN crs_TRSName(TRS.trs_id) LOOP
    DBMS_OUTPUT.put_line('       <TRSName>');
    DBMS_OUTPUT.put_line('         <trs_name1>'||TO_CHAR(TRSName.trsn_name1)||'</trs_name1>');
    DBMS_OUTPUT.put_line('         <trs_name2>'||TO_CHAR(TRSName.trsn_name2)||'</trs_name2>');
    DBMS_OUTPUT.put_line('         <trs_name3>'||TO_CHAR(TRSName.trsn_name3)||'</trs_name3>');
    DBMS_OUTPUT.put_line('         <trs_name4>'||TO_CHAR(TRSName.trsn_name4)||'</trs_name4>');
    DBMS_OUTPUT.put_line('         <trsn_aka_tp>'||TO_CHAR(TRSName.trsn_aka_tp)||'</trsn_aka_tp>');
    DBMS_OUTPUT.put_line('         <trsn_aka_ct>'||TO_CHAR(TRSName.trsn_aka_ct)||'</trsn_aka_ct>');
    DBMS_OUTPUT.put_line('         <trsn_aka_ql>'||TO_CHAR(TRSName.trsn_aka_ql)||'</trsn_aka_ql>');
    DBMS_OUTPUT.put_line('       </TRSName>');
   END LOOP;
   FOR TRSDocument IN crs_TRSDocument(TRS.trs_id) LOOP
    DBMS_OUTPUT.put_line('       <TRSDocument>');
    DBMS_OUTPUT.put_line('         <trsd_cn>'||TRSDocument.trsd_cn||'</trsd_cn>'); 
    DBMS_OUTPUT.put_line('         <trsd_number>'||TRSDocument.trsd_number||'</trsd_number>'); 
    DBMS_OUTPUT.put_line('         <trsd_issue_date>'||TRSDocument.trsd_issue_date||'</trsd_issue_date>'); 
    DBMS_OUTPUT.put_line('         <trsd_issue_country>'||TRSDocument.trsd_issue_country||'</trsd_issue_country>'); 
    DBMS_OUTPUT.put_line('         <trsd_issue_org>'||TRSDocument.trsd_issue_org||'</trsd_issue_org>'); 
    DBMS_OUTPUT.put_line('         <trsd_ident_code>'||TRSDocument.trsd_ident_code||'</trsd_ident_code>');
    DBMS_OUTPUT.put_line('       </TRSDocument>');
   END LOOP;
   FOR TRSParam IN crs_TRSParam(TRS.trs_id) LOOP
    DBMS_OUTPUT.put_line('       <'||TRSParam.opt_extnum||'>'||TRSParam.trsp_stringvalue||'</'||TRSParam.opt_extnum||'>'); 
   END LOOP;
  DBMS_OUTPUT.put_line('     </Terrorist>');
 END LOOP;
 DBMS_OUTPUT.put_line('   </list-terror>');
 DBMS_OUTPUT.put_line('</transport-file>'); 
END;