Laurent Schneider

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=”‘’
'’” /><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

This entry was posted on Monday, May 7th, 2007 at 12:33 and is filed under oracle, sql, xml. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.
Post to : del.icio.us - Digg it - Furl - reddit - Spurl

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

42 Responses to “csv with XML revisited…”

vidya Says:
May 7th, 2007 at 15:25
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
vidya Says:
May 7th, 2007 at 15:28
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.
nyarrow Says:
May 7th, 2007 at 18:29
Can’t use large tables here - this is limited to row widths of 4000 including commas (due to the type tv).

CLOBs anyone?
Vadim Tropashko Says:
May 7th, 2007 at 18:56
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;
Laurent Schneider Says:
May 7th, 2007 at 19:19
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)
Vadim Tropashko Says:
May 7th, 2007 at 19:38
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…
Laurent Schneider Says:
May 7th, 2007 at 20:37
;-) 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 !
Vadim Tropashko Says:
May 7th, 2007 at 21:37
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.
Michael Moore Says:
May 8th, 2007 at 00:39
I had to do this to figure out what was going on.
Michael Moore Says:
May 8th, 2007 at 00:43
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=”‘
’”&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;
Eriks Says:
May 8th, 2007 at 19:29
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)
Eriks Says:
May 8th, 2007 at 19:31
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’))
Eriks Says:
May 8th, 2007 at 19:32
PS

Thanks to T.Kyte for DBMS_SQL.parse trick
Jon B. Says:
May 8th, 2007 at 22:47
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.
Laurent Schneider Says:
May 9th, 2007 at 04:59
Jon,
Yes, it has this. You can also do
select /*CSV*/ * from EMP;
in SQL Developer. Check Kris blog
Logan McLeod Says:
May 9th, 2007 at 17:57
hahah….

Sometimes, it’s all about the journey, not the destination eh…
Dave Says:
May 11th, 2007 at 21:40
Thanks

Or if you want to create CSV of data without a tool thru sqlplus……
Parag J Patankar Says:
May 18th, 2007 at 07:41
Hi,

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

Sorry for my limited knowledge about XMLDB.

thanks & regards
PJP
Laurent Schneider Says:
May 18th, 2007 at 20:56
yes, just replace the , in the code by something different
Laurent Schneider Says:
May 18th, 2007 at 20:57
:value-of select=”‘’~'’” for ~
Badri Says:
July 4th, 2007 at 13:59
hi
I want to convert .csv file into xml format
could you pls help me, it is very urgent
Laurent Schneider Says:
July 4th, 2007 at 18:42
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
Badri Says:
July 5th, 2007 at 07:20
i am new to this,could Pls guide me
Badri Says:
July 5th, 2007 at 07:22
could you Pls send one perl or shell script that convert Csv into Xml format
Laurent Schneider Says:
July 5th, 2007 at 12:16
with pleasure

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)
Badri Says:
July 5th, 2007 at 13:57
i am getting syntax error at awk -F
could you Pls guide me
Badri Says:
July 5th, 2007 at 14:01
i am running this in Perl, i am getting below error

Unrecognized file test: -F
Laurent Schneider Says:
July 5th, 2007 at 15:15
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)
Laurent Schneider Says:
July 5th, 2007 at 15:16
argg… copy paste error…sorry
Badri Says:
July 5th, 2007 at 15:40
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
Badri Says:
July 5th, 2007 at 15:49
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
Badri Says:
July 5th, 2007 at 15:51
sorry
pls ignore my previus mail

Laurent
1000
Marc
2000
Jack
1500

i want like this

Laurent
1000
Marc
2000
Jack
1500
Badri Says:
July 5th, 2007 at 15:53
sorry i am not able sent ,
i will explain
i want output like

Laurent
1000
Marc
2000
Jack
1500
Badri Says:
July 5th, 2007 at 15:55
i want out put like
xml version=1
table
NAME>Laurent
Badri Says:
July 5th, 2007 at 16:02
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
Laurent Schneider Says:
July 6th, 2007 at 09:02
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…
Badri Says:
July 6th, 2007 at 15:42
tanks for ur help
Badri Says:
July 9th, 2007 at 08:28
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
Laurent Schneider Says:
July 9th, 2007 at 10:58
well, you can use EXTERNAL TABLE to load the CSV file as relational data, than use the functions mentinoned above.
Badri Says:
July 9th, 2007 at 13:05
Laurent,
we aren’t using oracle, so we can’t use as u mentioned,
so Pls advice me.
Laurent Schneider Says:
July 9th, 2007 at 13:55
well, I have not used any tool yet, but if I were you, I would start by googling for perl xml
Very Neat Trick Part II | El Mundo Con Otros Ojos Says:
June 25th, 2008 at 23:38
[…] Thanks to Laurent Schneider - even cooler… Tags: Noticias […]

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

csv with XML revisited…

42 Responses to “csv with XML revisited…”

Leave a Reply