Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2004
    Posts
    153

    Unanswered: Generating XML from Oracle

    Hi All,

    I am generating an XML file from Oracle, but facing one problem while inserting 'DateTime' of the sysdate.

    Using below query to insert datatime as 'ShowDateTime', but only date is inserting into the file not the time.

    SELECT XMLELEMENT("Movie"
    ,(XMLELEMENT("MovieDetails"
    ,XMLFOREST(to_date(to_char(SYSDATE,'DD-MON-RRRR HH24:MI'),'DD-MON-RRRR HH24:MI') AS "ShowDateTime"))))
    INTO l_xml_brk_cont2 -- xml type variable
    FROM dual;

    The current is : nls_date_format='YYYY-MM-DD HH24:MIS'

    It will be nice if any one can help to insert the time also along with date.


    Thanks with Regards,
    JD

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Code:
    SELECT XMLELEMENT("Movie"
                ,(XMLELEMENT("MovieDetails"
                ,XMLFOREST(to_char(SYSDATE,'DD-MON-RRRR HH24:MI') AS "ShowDateTime"))))
            INTO l_xml_brk_cont2 -- xml type variable
            FROM dual;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Jan 2004
    Posts
    153
    I forgot to mention one point that the XSD validation before generating the XML file. The 'ShowDateTime' value is validating with the XSD where it has mentioned as type="xs:dateTime", hence will not allow any character data. So i need to generate the XML file as dateTime format whic will display date along with time also.

    Thanks with Regards,
    JD

  4. #4
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253
    For a true xsateTime, you will need to use an Oracle date mask of 'YYYY-MM-DD"T"HH24:MIS'.
    In SQL/PLUS:
    Code:
    dayneo@RMSD> alter session set nls_date_format='YYYY-MM-DD"T"HH24:MI:SS';
    
    Session altered.
    
    dayneo@RMSD> select sysdate from dual;
    
    SYSDATE
    -------------------
    2012-08-27T14:37:38
    Or
    Code:
    SELECT XMLELEMENT("Movie"
                ,(XMLELEMENT("MovieDetails"
                ,XMLFOREST(to_char(SYSDATE, 'YYYY-MM-DD"T"HH24:MI:SS') AS "ShowDateTime"))))
            INTO l_xml_brk_cont2 -- xml type variable
            FROM dual;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •