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

Put your code in <code> and </code> tags

42 Responses to “csv with XML revisited…”

  1. vidya Says:

    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 Says:

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

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

    CLOBs anyone?

  4. Vadim Tropashko Says:

    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;

  5. Laurent Schneider Says:

    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)

  6. Vadim Tropashko Says:

    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 Says:

    ;-) 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 Says:

    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 Says:

    I had to do this to figure out what was going on.

  10. Michael Moore Says:

    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;

  11. Eriks Says:

    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)

  12. Eriks Says:

    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’))

  13. Eriks Says:

    PS

    Thanks to T.Kyte for DBMS_SQL.parse trick :-)

  14. Jon B. Says:

    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.

  15. Laurent Schneider Says:

    Jon,
    Yes, it has this. You can also do
    select /*CSV*/ * from EMP;
    in SQL Developer. Check Kris blog

  16. Logan McLeod Says:

    hahah….

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

  17. Dave Says:

    Thanks

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

  18. Parag J Patankar Says:

    Hi,

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

    Sorry for my limited knowledge about XMLDB.

    thanks & regards
    PJP

  19. Laurent Schneider Says:

    yes, just replace the , in the code by something different

  20. Laurent Schneider Says:

    :value-of select=”‘’~'’” for ~

  21. Badri Says:

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

  22. Laurent Schneider Says:

    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-)

  23. Badri Says:

    i am new to this,could Pls guide me

  24. Badri Says:

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

  25. Laurent Schneider Says:

    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)

  26. Badri Says:

    i am getting syntax error at awk -F
    could you Pls guide me

  27. Badri Says:

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

    Unrecognized file test: -F

  28. Laurent Schneider Says:

    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)

  29. Laurent Schneider Says:

    argg… copy paste error…sorry

  30. Badri Says:

    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

  31. Badri Says:

    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

  32. Badri Says:

    sorry
    pls ignore my previus mail

    Laurent
    1000
    Marc
    2000
    Jack
    1500

    i want like this

    Laurent
    1000
    Marc
    2000
    Jack
    1500

  33. Badri Says:

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

    Laurent
    1000
    Marc
    2000
    Jack
    1500

  34. Badri Says:

    i want out put like
    xml version=1
    table
    NAME>Laurent

  35. Badri Says:

    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

  36. Laurent Schneider Says:

    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…

  37. Badri Says:

    tanks for ur help

  38. Badri Says:

    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

  39. Laurent Schneider Says:

    well, you can use EXTERNAL TABLE to load the CSV file as relational data, than use the functions mentinoned above.

  40. Badri Says:

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

  41. Laurent Schneider Says:

    well, I have not used any tool yet, but if I were you, I would start by googling for perl xml

  42. Very Neat Trick Part II | El Mundo Con Otros Ojos Says:

    […] Thanks to Laurent Schneider - even cooler… Tags: Noticias […]

Leave a Reply

Use <code> and </code> to post code