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

44 thoughts on “csv with XML revisited…”

vidya May 7, 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 May 7, 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.
Laurent Schneider Post authorMay 7, 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)
nyarrow May 7, 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 May 7, 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;
Vadim Tropashko May 7, 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 Post authorMay 7, 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 May 7, 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 May 8, 2007 at 00:39

I had to do this to figure out what was going on.
Michael Moore May 8, 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 May 8, 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 May 8, 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 May 8, 2007 at 19:32

PS

Thanks to T.Kyte for DBMS_SQL.parse trick 🙂
Jon B. May 8, 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 Post authorMay 9, 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 May 9, 2007 at 17:57

hahah….

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

Thanks

Or if you want to create CSV of data without a tool thru sqlplus……
Parag J Patankar May 18, 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 Post authorMay 18, 2007 at 20:56

yes, just replace the , in the code by something different
Laurent Schneider Post authorMay 18, 2007 at 20:57

:value-of select=””~”” for ~
Badri July 4, 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 Post authorJuly 4, 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 July 5, 2007 at 07:20

i am new to this,could Pls guide me
Badri July 5, 2007 at 07:22

could you Pls send one perl or shell script that convert Csv into Xml format
Laurent Schneider Post authorJuly 5, 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 July 5, 2007 at 13:57

i am getting syntax error at awk -F
could you Pls guide me
Badri July 5, 2007 at 14:01

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

Unrecognized file test: -F
Laurent Schneider Post authorJuly 5, 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 Post authorJuly 5, 2007 at 15:16

argg… copy paste error…sorry
Badri July 5, 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 July 5, 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 July 5, 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 July 5, 2007 at 15:53

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

Laurent
1000
Marc
2000
Jack
1500
Badri July 5, 2007 at 15:55

i want out put like
xml version=1
table
NAME>Laurent
Badri July 5, 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 Post authorJuly 6, 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 July 6, 2007 at 15:42

tanks for ur help
Badri July 9, 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 Post authorJuly 9, 2007 at 10:58

well, you can use EXTERNAL TABLE to load the CSV file as relational data, than use the functions mentinoned above.
Laurent Schneider Post authorJuly 9, 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
Badri July 9, 2007 at 13:05

Laurent,
we aren’t using oracle, so we can’t use as u mentioned,
so Pls advice me.
Pingback: Very Neat Trick Part II | El Mundo Con Otros Ojos
Stephen Bealer June 12, 2011 at 19:55

Now we can pass in sys_refcursors:

http://techblog.steveandyvonne.com/2011/06/return-any-table-as-csv-data_12.html
Laurent Schneider Post authorJune 14, 2011 at 09:50

Thank you Stephen 🙂

Comments are closed.