How to get SQL data into XML format
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: How to get SQL data into XML format

  1. #1
    Join Date
    Jan 2003
    Posts
    141

    How to get SQL data into XML format

    Hi,
    I am trying to select data from the emp table and i want to display the data in XML format. I am having problems with the examples

    *** stroing the result into another table temp_clon_tab as CLOB
    *** How to extract the result from CLOB?

    CREATE TABLE temp_clob_tab(result CLOB)

    1 DECLARE
    2 qryCtx DBMS_XMLGEN.ctxHandle;
    3 result CLOB;
    4 BEGIN
    5 qryCtx := dbms_xmlgen.newContext
    6 ('SELECT * from emp;');
    7 DBMS_XMLGEN.setRowTag(qryCtx, 'EMPLOYEE');
    8 DBMS_XMLGEN.setMaxRows(qryCtx, 5);
    9 LOOP
    10 result := DBMS_XMLGEN.getXML(qryCtx);
    11 EXIT WHEN
    12 DBMS_XMLGEN.getNumRowsProcessed(qryCtx)=0;
    13 INSERT INTO temp_clob_tab VALUES(result);
    14 END LOOP;
    15* END;

    I got the following error!

    SQL> /
    DECLARE
    *
    ERROR at line 1:
    ORA-06502: PL/SQL: numeric or value error
    ORA-00911: invalid character



    How can i print the XML format CLOB output, instead of inserting into table.


    Many Thanks,

  2. #2
    Join Date
    Aug 2003
    Posts
    11
    try this

    DECLARE
    --qryCtx DBMS_XMLGEN.ctxHandle;

    type a is ref cursor;
    c a;
    qryCtx number;
    result CLOB;

    BEGIN

    open c for SELECT * from emp;

    qryCtx := dbms_xmlgen.newContext (c);

    DBMS_XMLGEN.setRowTag(qryCtx, 'EMPLOYEE');

    DBMS_XMLGEN.setMaxRows(qryCtx, 5);

    LOOP
    result := DBMS_XMLGEN.getXML(qryCtx);
    EXIT WHEN
    DBMS_XMLGEN.getNumRowsProcessed(qryCtx)=0;
    INSERT INTO temp_clob_tab VALUES(result);
    END LOOP;

    END;

    Cheers
    Slash

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width