csv with XML revisited…

Special thanks to Tom for pointing and Michaels for fixing the missing manager of King in my previous post : csv format with select

Ok, here is a my PL/SQL table function.


create or replace type tv as table of
varchar2(4000);
/

create or replace function CSV(sqlQuery varchar2)
return tv pipelined is
ctx dbms_xmlgen.ctxhandle;
begin
ctx:=dbms_xmlgen.newcontext(sqlquery);
dbms_xmlgen.setnullhandling(ctx,dbms_xmlgen.empty_tag);
for f in (select
cast(
xmltransform(column_value,
XMLTYPE(‘<xsl:stylesheet version=”1.0″ xmlns:xsl’||
‘=”http://www.w3.org/1999/XSL/Transform”>’||
‘<xsl:output method=”text”/><xsl:variable name’||
‘=”new_line” select=””&#xA;”” /><xsl:template’||
‘ match=”ROWSET”><xsl:apply-templates select=”‘||
‘ROW”/></xsl:template><xsl:template match=”ROW”‘||
‘><xsl:for-each select=”*”><xsl:value-of select’||
‘=”.”/><xsl:if test=”position() != last()”><xsl’||
‘:value-of select=””,””/></xsl:if></xsl:for-’||
‘each><xsl:value-of select=”$new_line” /></xsl:’||
‘template></xsl:stylesheet>’
)) as varchar2(4000))
c from table(xmlsequence(dbms_xmlgen.getxmltype(ctx)
.extract(‘ROWSET/ROW’))))
loop
pipe row(f.c);
end loop;
end;
/

select * from table(csv(‘select * from emp’));
COLUMN_VALUE
————————————————
7369,SMITH,CLERK,7902,17-DEC-80,800,,20
7499,ALLEN,SALESMAN,7698,20-FEB-81,1600,300,30
7521,WARD,SALESMAN,7698,22-FEB-81,1250,500,30
7566,JONES,MANAGER,7839,02-APR-81,2975,,20
7654,MARTIN,SALESMAN,7698,28-SEP-81,1250,1400,30
7698,BLAKE,MANAGER,7839,01-MAY-81,2850,,30
7782,CLARK,MANAGER,7839,09-JUN-81,2450,,10
7788,SCOTT,ANALYST,7566,19-APR-87,3000,,20
7839,KING,PRESIDENT,,17-NOV-81,5000,,10
7844,TURNER,SALESMAN,7698,08-SEP-81,1500,0,30
7876,ADAMS,CLERK,7788,23-MAY-87,1100,,20
7900,JAMES,CLERK,7698,03-DEC-81,950,,30
7902,FORD,ANALYST,7566,03-DEC-81,3000,,20
7934,MILLER,CLERK,7782,23-JAN-82,1300,,10

44 thoughts on “csv with XML revisited…”

  1. Laurent,

    This is great – I tried the funcation out on some of our little larger tables and got an End of Communication Channel error message. We are not 9.2.0.6. Is there something I am missing?

    Thanks,
    Vidya

  2. Laurent,

    sorry about the type – I meant “Function” and got an
    ERROR at line 1:
    ORA-03113: end-of-file on communication channel

    I can see the function being of immese help , for a lot of requests we get to generate data as coma delimited files.

    Thanks for sharing this with us.

  3. vadim, this is fine for tables, but what is if you have something like

    select * from emp join dept using (deptno); 

    or even
    select a,b from t,u where y=z

    I would like to know what would be your approach in such a case…

    about clob, yes, I see no problem to make it clob. but are you sure you want lines longer than 4000 character per line?

    vidya, try my not-pipelined function at http://forums.oracle.com/forums/thread.jspa?forumID=75&threadID=503433#1826117 to see if it performs better (but it will require more memory)

  4. Can’t use large tables here – this is limited to row widths of 4000 including commas (due to the type tv).

    CLOBs anyone?

  5. create or replace
    function CSV(tblName varchar2)
    return strings pipelined is
    tmp varchar2(1000);
    i integer;
    cursor c1 is select column_name from all_tab_columns where table_name = upper(tblName);
    TYPE any_curtype IS REF CURSOR;
    c2 any_curtype;
    begin
    i := 0;

    FOR col in c1
    LOOP
    if i = 0 then
    tmp := col.column_name;
    else
    tmp := tmp||’||’||col.column_name;
    end if;
    i := i+1;
    END LOOP;

    open c2 for ‘select ‘||tmp||’ from ‘||tblName;
    loop
    FETCH c2 INTO tmp;
    –EXIT WHEN c2%NOT_FOUND;
    pipe row(tmp);
    end loop;
    end;

  6. Create temporary view, read column info in dictionary, build dynamic SQL?

    Seriously, I can’t imagine any application that would need comma separated list of columns. Relational databases work conveniently with data structured into the table format…

  7. ;-) yes, possible, but it would require a DDL, which would commit the transactions.

    Exporting the data in csv format without having to write column one pipe pipe semi-column pipe pipe column two etc could be nice in some cases !

  8. OK, this problem is actually easy. What would you do in Java/JDBC? Open a cursor, extract the first row, iterate through all the fields while concatenating them — pretty straightforward.

    So why don’t we do the same in PL/SQL? All what is required is to be able to iterate through cursor fields, and

    DBMS_SQL.COLUMN_VALUE(curid, i, namevar);

    is what enables it.

  9. let me try again


    <xsl:stylesheet version=”1.0″ xmlns:xsl =”http://www.w3.org/1999/XSL/Transform”&rt;
    <xsl:output method=”text”&rt;</xsl:output&rt;
    <xsl:variable name=”new_line” select=”‘&#xA;’”&rt;</xsl:variable&rt;
    <xsl:template match=”ROWSET”&rt;
    <xsl:apply-templates select=”ROW”&rt;</xsl:apply-templates&rt;
    </xsl:template&rt;
    <xsl:template match=”ROW”&rt;
    <xsl:for-each select=”*”&rt;
    <xsl:value-of select =”.”&rt;</xsl:value-of&rt;
    <xsl:if test=”position() != last()”&rt;
    <xsl:value-of select=”‘,’”&rt;</xsl:value-of&rt;
    </xsl:if&rt;
    </xsl:for-each&rt;
    <xsl:value-of select=”$new_line”&rt;</xsl:value-of&rt;
    </xsl:template&rt;
    </xsl:stylesheet&rt;

  10. CREATE OR REPLACE PACKAGE cvs_export IS
    TYPE t_rec is record (
    c_value varchar2(4000)
    );
    TYPE t_cvs IS TABLE OF t_rec;
    FUNCTION cvs (p_stmt VARCHAR2)
    RETURN t_cvs pipelined;
    END;

    CREATE OR REPLACE PACKAGE BODY cvs_export IS
    FUNCTION cvs (p_stmt VARCHAR2)
    RETURN t_cvs PIPELINED IS
    l_cur INTEGER;
    l_col VARCHAR2 (2000);
    l_tmp NUMBER;
    l_col_cnt INTEGER;
    l_desc DBMS_SQL.desc_tab;
    l_rec cvs_export.t_rec;
    BEGIN
    l_cur := DBMS_SQL.open_cursor;
    DBMS_SQL.parse (l_cur, p_stmt, DBMS_SQL.native);
    l_col_cnt := 0;
    FOR i IN 1 .. 255 LOOP
    BEGIN
    DBMS_SQL.define_column (l_cur, i, l_col, 2000);
    l_col_cnt := i;
    EXCEPTION
    WHEN OTHERS THEN
    IF (SQLCODE = -1007) THEN
    EXIT;
    ELSE
    RAISE;
    END IF;
    END;
    END LOOP;
    DBMS_SQL.define_column (l_cur, 1, l_col, 2000);

    l_tmp := DBMS_SQL.EXECUTE (l_cur);
    DBMS_SQL.describe_columns (l_cur, l_col_cnt, l_desc);
    LOOP
    EXIT WHEN DBMS_SQL.fetch_rows (l_cur)

  11. FOR nn IN l_desc.FIRST .. l_desc.LAST LOOP
    DBMS_SQL.column_value (l_cur, nn, l_col);
    l_rec.c_value := l_rec.c_value || l_col || ‘;’;
    END LOOP;
    PIPE ROW (l_rec);
    l_rec.c_value := NULL;
    END LOOP;
    DBMS_SQL.close_cursor (l_cur);
    RETURN;
    END;
    END;

    select * from table(cvs_export.cvs(‘select * from all_objects’))

  12. Perhaps I’m missing something here but SQL Developer has this functionality built right in. Just right click on your result and choose Export > CSV.

  13. hahah….

    Sometimes, it’s all about the journey, not the destination eh… :)

  14. Hi,

    Is data can be downloaded using different charcter other than ‘,’ using this solution ?

    Sorry for my limited knowledge about XMLDB.

    thanks & regards
    PJP

  15. hi
    I want to convert .csv file into xml format
    could you pls help me, it is very urgent

  16. you could do this with an external table, but do you really need Oracle? there are surely plenty of perl and java tools …

    I would start with awk if I were you 8-)

  17. with pleasure :mrgreen:

    x.csv

    NAME;SAL
    Laurent;1000
    Marc;2000
    Jack;1500


    awk -F’;’ ‘NR==1{print “<?xml version=”1.0″><TABLE>”;F=NF;for(i=1;i<=F;i++)
    {h[i]=$i};getline}{printf “<ROW>”;for(i=1;i<=F;i++)printf “<”h[i]“>”$i”</”h[i]“>”;
    print “</ROW>”}END{print “</TABLE>”}’ x.csv


    <?xml version=1><TABLE>
    <ROW><NAME>Laurent</NAME><SAL>1000</SAL></ROW>
    <ROW><NAME>Marc</NAME><SAL>2000</SAL></ROW>
    <ROW><NAME>Jack</NAME><SAL>1500</SAL></ROW>
    </TABLE>

    (corrected)

  18. awk is a default command in Unix. What is your operating system?

    in perl you can try


    perl -e ‘print “<?xml version=1><TABLE>”;$FS=”;”;open(F,”x.csv”);
    @h=split(“;”);while(<F>){if($.==1){chomp;@h=split(“;”);$NF=$#h}
    else {chomp;@Fld=split(“;”);for ($i=0;$i<=$NF;$i++)
    {printf “<%s>%s</%s>\\n”,$h[$i],$Fld[$i],$h[$i];}}};
    print “</TABLE>\\n”‘
    <?xml version=1><TABLE><NAME>Laurent</NAME>
    <SAL>1000</SAL>
    <NAME>Marc</NAME>
    <SAL>2000</SAL>
    <NAME>Jack</NAME>
    <SAL>1500</SAL>
    </TABLE>

    (corrected)

  19. my o.s is unix , i copied your awk program into as Xml2Csv.sh and ran it
    but i am getting the below error like

    (this is the script)

    #!/usr/bin/ksh +x

    awk -F’;’ ‘NR==1{print “”;F=NF;for(i=1;i”;for(i=1;i”””;
    print “”}END{print “”}’ x.csv

    (these are error i am getting)

    awk: syntax error near line 2
    awk: illegal statement near line 2
    awk: syntax error near line 3
    awk: illegal statement near line 3

  20. excellent, perl scripting is working fine.
    your out put is
    Laurent
    1000
    Marc
    2000
    Jack
    1500

    but i want like this

    Laurent
    1000
    Marc
    2000
    Jack
    1500

    could you pls guide me

  21. sorry
    pls ignore my previus mail

    Laurent
    1000
    Marc
    2000
    Jack
    1500

    i want like this

    Laurent
    1000
    Marc
    2000
    Jack
    1500

  22. sorry i am not able sent ,
    i will explain
    i want output like

    Laurent
    1000
    Marc
    2000
    Jack
    1500

  23. excellent , both sh and perl is working fine but i want step by step ,
    not in single line, the first line is coming in single line .
    could pls guide me
    pls help

  24. first, I apologize for the wordpress annoyances, it is a pain to give comments with < and > :evil:

    the perl script should be quite easy to debug, just edit in in your favorite environment and make it multi lines…

  25. Laurent Schneider,
    could you Pls do a favor for me,
    we have lot of functions in oracle like attribute, element,xmlforest ..etc
    like this, is there any functions to convert csv to xml by using functions.
    Pls guide me with examples

  26. Laurent,
    we aren’t using oracle, so we can’t use as u mentioned,
    so Pls advice me.

  27. Pingback: Very Neat Trick Part II | El Mundo Con Otros Ojos

Leave a Reply

Your email address will not be published.


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>