| XML conversion in oracle 8.1.7 [message #63560] |
Fri, 15 October 2004 11:07  |
Govind Thangavelu
Messages: 2
Registered: April 2004
|
Junior Member |
|
|
Hi,
I am using oracle8i. Is there any way i can display
the date column in the XML file without time ?.
create table test(dt date);
insert into test values(sysdate);
set autoprint on
set long 100000
set linesize 100000
set longchunksize 100000
var g_clob clob
declare
l_ctx dbms_xmlquery.ctxType;
l_clob clob;
begin
l_ctx := dbms_xmlquery.newContext('select dt from test');
dbms_lob.createtemporary(:g_clob,true,dbms_lob.session);
:g_clob := dbms_xmlquery.getXml(l_ctx);
end;
/
The output is showing as below.
<?xml version = '1.0'?>
<ROWSET>
<ROW num="1">
<DT>10/15/2004 11:15:31</DT>
</ROW>
</ROWSET>
But i want my output should be as below.
<?xml version = '1.0'?>
<ROWSET>
<ROW num="1">
<DT>10/15/2004</DT>
</ROW>
</ROWSET>
Govind
|
|
|
|
| Re: XML conversion in oracle 8.1.7 [message #63561 is a reply to message #63560] |
Fri, 15 October 2004 14:53  |
 |
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
Use dbms_xmlquery.setdateformat.
There is a restriction. You need to use java style dates. Note all caps MM.
dbadmin@republic_lawd1 > get xml2
1 declare
2 l_ctx dbms_xmlquery.ctxType;
3 l_clob clob;
4 begin
5 l_ctx := dbms_xmlquery.newContext('select dt from test');
6 DBMS_XMLQuery.setDateFormat(l_ctx,'MM/dd/yyyy');
7 dbms_lob.createtemporary(:g_clob,true,dbms_lob.session);
8 :g_clob := dbms_xmlquery.getXml(l_ctx);
9* end;
dbadmin@republic_lawd1 > /
PL/SQL procedure successfully completed.
G_CLOB
----------------------------------------------------------------------------------------------------
<?xml version = '1.0'?>
<ROWSET>
<ROW num="1">
<DT>10/15/2004</DT>
</ROW>
</ROWSET>
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
