Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2004
    Posts
    14

    Unanswered: Convert oracle recordset to xml file

    Hi there,
    I am running the following code from XSU but the .xml files I am getting are invalid:



    set serverout on
    spool C:\path\test_xml.xml


    Declare
    queryCtx DBMS_XMLquery.ctxType;
    result CLOB;
    procedure printClobOut(result IN OUT NOCOPY CLOB) is
    xmlstr varchar2(32767);
    line varchar2(2000);
    begin
    xmlstr := dbms_lob.SUBSTR(result,32767);
    loop
    exit when xmlstr is null;
    line := substr(xmlstr,1,instr(xmlstr,chr(10))-1);
    dbms_output.put_line('| '||line);
    xmlstr := substr(xmlstr,instr(xmlstr,chr(10))+1);
    end loop;
    end;
    begin
    -- set up the query context...!
    queryCtx := DBMS_XMLQuery.newContext('select sysdate from dual');
    -- get the result..!
    result := DBMS_XMLQuery.getXML(queryCtx);
    -- Now you can use the result to put it in tables/send as messages..
    printClobOut(result);
    DBMS_XMLQuery.closeContext(queryCtx); -- you must close the query handle..
    end;
    /

    spool off

    The recordset appears in the last rows of the file.
    how can I get the xml formatted correctly?!

    Thanks,
    Kostas.

  2. #2
    Join Date
    Apr 2004
    Posts
    246
    I run it and get the following:

    <?xml version = '1.0'?>
    <ROWSET>
    <ROW num="1">
    <SYSDATE>9/9/2004 12:8:6</SYSDATE>
    </ROW>
    </ROWSET>

    what's wrong with this output?
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

  3. #3
    Join Date
    Aug 2004
    Posts
    14
    Hello shoblock,
    The problem is that in the xml file I am also getting the code I am running before appears the xml contents of the file I am getting. I believe that I should change the code in some parts. Do you have any idea how should I do that?!
    The contents of the xml file are:

    set serverout on
    spool path\test.xml

    Declare
    queryCtx DBMS_XMLquery.ctxType;
    result CLOB;
    procedure printClobOut(result IN OUT NOCOPY CLOB) is
    xmlstr varchar2(32767);
    line varchar2(2000);
    begin
    xmlstr := dbms_lob.SUBSTR(result,32767);
    loop
    exit when xmlstr is null;
    line := substr(xmlstr,1,instr(xmlstr,chr(10))-1);
    dbms_output.put_line(line);
    xmlstr := substr(xmlstr,instr(xmlstr,chr(10))+1);
    end loop;
    end;
    begin
    -- set up the query context...!
    queryCtx := DBMS_XMLQuery.newContext('select sysdate from dual');
    -- get the result..!
    result := DBMS_XMLQuery.getXML(queryCtx);
    -- Now you can use the result to put it in tables/send as messages..
    printClobOut(result);
    DBMS_XMLQuery.closeContext(queryCtx); -- you must close the query handle..
    end;
    /

    spool off

    Thanks,
    Kostas.

  4. #4
    Join Date
    Aug 2004
    Posts
    14
    I am sending you again the contents of the xml file:

    Declare
    queryCtx DBMS_XMLquery.ctxType;
    result CLOB;
    procedure printClobOut(result IN OUT NOCOPY CLOB) is
    xmlstr varchar2(32767);
    line varchar2(2000);
    begin
    xmlstr := dbms_lob.SUBSTR(result,32767);
    loop
    exit when xmlstr is null;
    line := substr(xmlstr,1,instr(xmlstr,chr(10))-1);
    dbms_output.put_line(line);
    xmlstr := substr(xmlstr,instr(xmlstr,chr(10))+1);
    end loop;
    end;
    begin
    -- set up the query context...!
    queryCtx := DBMS_XMLQuery.newContext('select sysdate from dual');
    -- get the result..!
    result := DBMS_XMLQuery.getXML(queryCtx);
    -- Now you can use the result to put it in tables/send as messages..
    printClobOut(result);
    DBMS_XMLQuery.closeContext(queryCtx); -- you must close the query handle..
    end;
    /
    <?xml version = '1.0'?>
    <ROWSET>
    <ROW num="1">
    <SYSDATE>9/10/2004 11:40:54</SYSDATE>
    </ROW>
    </ROWSET>




    Thanks,

    Kostas.

  5. #5
    Join Date
    Apr 2004
    Posts
    246
    set echo off
    set feedback off
    set verify off
    set termout off
    set timing off
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

  6. #6
    Join Date
    Aug 2004
    Posts
    14

    It worked...

    Thanks for your help. It worked for me in the meanwhile using:

    set serveroutput on size 1000000
    set feedback off
    set heading off
    set pagesize 0
    set linesize 1024
    set verify off
    set echo off
    SET PAUSE OFF

    Regards,
    Kostas.

  7. #7
    Join Date
    Aug 2004
    Posts
    14

    XSU problem (oracle character set)

    Dear shoblock,
    I am now able to create xml files with Oracle's XSU tool. The problem is that sometimes the produced xml files can not be validated from IE since they contain characters like english pound, or `.
    Any ideas what to change in order to catch these errors and produce valiable xml files?!

    Regards,
    Kostas.

Posting Permissions

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