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. vidya

    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. vidya

    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. Laurent Schneider Post author

    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. nyarrow

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

    CLOBs anyone?

  5. Vadim Tropashko

    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. Vadim Tropashko

    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. Laurent Schneider Post author

    😉 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. Vadim Tropashko

    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. Michael Moore

    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. Eriks

    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. Eriks

    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. Jon B.

    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. Logan McLeod

    hahah….

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

  14. Dave

    Thanks

    Or if you want to create CSV of data without a tool thru sqlplus……

  15. Parag J Patankar

    Hi,

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

    Sorry for my limited knowledge about XMLDB.

    thanks & regards
    PJP

  16. Badri

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

  17. Laurent Schneider Post author

    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 😎

  18. Badri

    could you Pls send one perl or shell script that convert Csv into Xml format

  19. Laurent Schneider Post author

    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)

  20. Badri

    i am running this in Perl, i am getting below error

    Unrecognized file test: -F

  21. Laurent Schneider Post author

    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)

  22. Badri

    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

  23. Badri

    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

  24. Badri

    sorry
    pls ignore my previus mail

    Laurent
    1000
    Marc
    2000
    Jack
    1500

    i want like this

    Laurent
    1000
    Marc
    2000
    Jack
    1500

  25. Badri

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

    Laurent
    1000
    Marc
    2000
    Jack
    1500

  26. Badri

    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

  27. Laurent Schneider Post author

    first, I apologize for the wordpress annoyances, it is a pain to give comments with < and > 👿

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

  28. Badri

    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

  29. Badri

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

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

Comments are closed.