Generate XML fragments alone and display

20 Views Asked by At

I want to display complexe XML Output from different set of tables/Joins and condtions. Hence I planned to use PLSQL Block for each piece of XML data to display result in DBMS_OUT to produce final XML.

Below is throwing error as retrived data type and storing data type mismtach.

Please advise. WHat is the default datatype from the result of XMLSERIALIZE Using As CLOB also throwing error.

     DECLARE
      CURSOR C1 is SELECT XMLSERIALIZE( CONTENT XMLAGG(XMLFOREST(first_name, salary)) INDENT) FROM employees ;--WHERE employee_id IN (100,101);
      l_xt_var2 XMLTYPE;
    BEGIN
      OPEN C1 ;
      LOOP 
      FETCH  C1 INTO  l_xt_var2;
      EXIT WHEN C1%NOTFOUND;
      dbms_output.put_line(l_xt_var2.getstringVal()); 
      END LOOP;
      CLOSE C1;
    END;
    /

      FETCH  C1 INTO  l_xt_var2;
                  *
ERROR at line 7:
ORA-06550: line 7, column 19:
PLS-00386: type mismatch found at 'L_XT_VAR2' between FETCH cursor and INTO variables
ORA-06550: line 7, column 3:
PL/SQL: SQL Statement ignored

I tried using below code and it works.. Please let me know if any better approach than this. I do not want to use XMLTYPE(SYS REF CUROSOR) as it produces XML VERSION and Parent tags default (ROWSET/ROWS)

DECLARE
  CURSOR C1 is SELECT XMLSERIALIZE( CONTENT XMLAGG(XMLFOREST(first_name, salary)) as CLOB INDENT) FROM employees;-- WHERE employee_id IN (100,101);
  l_xt_var2 clob;
BEGIN
  OPEN C1 ;
  LOOP 
  FETCH  C1 INTO  l_xt_var2;
  EXIT WHEN C1%NOTFOUND;
  dbms_output.put_line(l_xt_var2); 
  END LOOP;
  CLOSE C1;
END;
/

Even I used loop, its displaying the result in one loop. How to retrive XML output tags one per line so that buffer issue will not occur when using DBMS_OUT.

Regards, Veera V

0

There are 0 best solutions below