SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

XML(XMLTYPE) and date format

Gert PoelJul 13 2012 — edited Jul 16 2012

Hi all,

I try to generate some XML in PL/SQL.

select xmlelement("Date", sysdate).getClobVal() from dual;

XMLELEMENT("DATE",SYSDATE).GETCLOBVAL()
-------------------------------------------
<Date>2012-07-13</Date>

This is the date format I expected(standard XML date format).

And the Oracle Documentation describes it:
"The XML Schema standard specifies that dates and timestamps in XML data be in standard formats. XML generation functions in Oracle XML DB produce XML dates and timestamps according to this standard."

When I want to create some XML from a query, I tried this

DECLARE

   l_refcursor SYS_REFCURSOR;
   l_xmltype xmltype;
   l_ctx dbms_xmlgen.ctxHandle;
     
BEGIN

   OPEN l_refcursor FOR select sysdate "Date" from dual;

   l_ctx := dbms_xmlgen.newContext (l_refcursor);
   dbms_xmlgen.setRowTag (l_ctx, null);
   dbms_xmlgen.setRowSetTag (l_ctx, null);
   l_xmltype := dbms_xmlgen.getXMLType (l_ctx);
   dbms_xmlgen.closeContext (l_ctx);
   
   dbms_output.put_line(l_xmltype.getClobVal());
END;
/

<Date>13-JUL-12</Date>

I expected also the XML standard date format, but I get the nls_date_format.

Of course I can use to_char and specify the correct date format or do an alter session(alter session set nls_date_format='RRRR-MM-DD'; ), but I wonder why XMLElement uses the standard and dbms_xmlgen not.
Or is there another way to use the default XML standard?

Gert

This post has been answered by MichaelS on Jul 13 2012

Jump to Answer

Galbarad

just convert date to char
with to_char(sysdate, 'YOUR_DATE_FORMAT')

select xmlelement("Date", to_char(sysdate, 'YYYY-MM-DD')).getClobVal() from dual;

good luck

think you need change your cursor

OPEN l_refcursor FOR select to_char(sysdate, 'YYYY-MM-DD') "Date" from dual;

Edited by: Galbarad on Jul 13, 2012 7:21 AM

Gert Poel

I know this is a solution, like I stated in my question: "Of course I can use to_char and specify the correct date format or do an alter session(alter session set nls_date_format='RRRR-MM-DD'; )"

But it seems that XMLElement uses the XML standard and dbms_xmlgen.getXMLType doesn't.
Maybe I should just pass this to Oracle Support...

Hoek

I believe that the desired default date format only applies for two functions:
"27.1 XML Date Formatting

Dates and timestamps in generated XML are now in the formats specified by XML Schema. See Oracle XML DB Developer's Guide, Chapter "Generating XML Data from the Database", *section "XMLELEMENT and XMLATTRIBUTES SQL Functions"*, subsection "Formatting of XML Dates and Timestamps"."
http://docs.oracle.com/cd/B19306_01/readmes.102/b14233/toc.htm#sthref85

Your quote from the docs ( http://docs.oracle.com/cd/B19306_01/appdev.102/b14259/xdb13gen.htm#sthref1506 ) applies to the SQL/XML standard functions, and apparently not to DBMS_XMLGEN (one of the Oracle Database-provided functions).

"It explains the SQL/XML standard functions and Oracle Database-provided functions and packages for generating XML data from relational content."
http://docs.oracle.com/cd/B19306_01/appdev.102/b14259/xdb13gen.htm#ADXDB1600

"If you otherwise need to produce a non-standard XML date or timestamp, use SQL function to_char"
http://docs.oracle.com/cd/B19306_01/appdev.102/b14259/xdb13gen.htm#sthref1506

1 person found this helpful

MichaelS

Answer

Or is there another way to use the default XML standard?

you can revert to the java based dbms_xmlquery instead:

SQL> alter session set nls_date_format='rrrrhh24'  ---just to show the result is independent from nls_format
/
Session altered.

SQL> declare
  l_xmltype   xmltype;
  l_ctx       dbms_xmlquery.ctxhandle;
begin
  l_ctx := dbms_xmlquery.newcontext ('select sysdate "Date" from dual');
  dbms_xmlquery.setrowtag (l_ctx, null);
  dbms_xmlquery.setrowsettag (l_ctx, null);
  l_xmltype := xmltype (dbms_xmlquery.getxml (l_ctx));
  dbms_xmlquery.closecontext (l_ctx);

  dbms_output.put_line (l_xmltype.getclobval ());
end;
/
<?xml version = '1.0'?>
<Date>2012-07-13 22:45:25</Date>
PL/SQL procedure successfully completed.

Marked as Answer by Gert Poel · Sep 27 2020

Gert Poel

Thanks Michael.

Only this makes me still wonder why dbms_xmlgen does not follow the standard, while all other built-in packages do ;-)

odie_63

Only this makes me still wonder why dbms_xmlgen does not follow the standard, while all other built-in packages do ;-)

DBMS_XMLQUERY does not follow the standard either.

Does the previous output look like a W3C dateTime format?
--> No, it supports <tt>java.text.SimpleDateFormat</tt>, which can be set through SETDATEFORMAT procedure, which is not much different from doing an ALTER SESSION or an explicit TO_CHAR, IMO.

And DBMS_XMLQUERY is Java-based, Oracle recommends using DBMS_XMLGEN instead (C-based, builtin and faster).

1 person found this helpful

1 - 6

Locked Post

New comments cannot be posted to this locked post.

Locked on Aug 13 2012

Added on Jul 13 2012

#xml

6 comments

4,683 views

SQL & PL/SQL

XML(XMLTYPE) and date format

Comments

Post Details