Home » Developer & Programmer » JDeveloper, Java & XML » PL/SQL XML Date Time omits Time part (9.2)
PL/SQL XML Date Time omits Time part [message #556475] Mon, 04 June 2012 08:54 Go to next message
raghx
Messages: 28
Registered: March 2006
Location: Bangalore
Junior Member
Hi There,
When I try to extract the date tag value from XML data, the time stored in 20120602153021 format i.e., YYYYMMDD24HHMISS format.
The following statement extracts only date as 02-JUN-12 however do not extract the time part.
If I try the same in SQLplus with to_date it works however fails in PL/SQL.
XML data:
<?xml version="1.0"?>
<RECORD>
<REGTIMESTAMP>20120601130010</REGTIMESTAMP>
</RECORD>

PL/SQL Extract:

CURSOR c_xml_record
IS
SELECT extract(value(d), '//ACTIVATIONTS/text()').getStringVal() AS REGTIMESTAMP,
FROM t_xml_data x,
table(xmlsequence(extract(x.xml_message, '/RECORD'))) d;

BEGIN
OPEN c_xml_record;
FETCH c_xml_record INTO
p_omful_rec.activation_ts --- omits timepart

CLOSE c_xml_record;


Please help
Regs
Re: PL/SQL XML Date Time omits Time part [message #556476 is a reply to message #556475] Mon, 04 June 2012 08:57 Go to previous messageGo to next message
BlackSwan
Messages: 21938
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: PL/SQL XML Date Time omits Time part [message #556479 is a reply to message #556476] Mon, 04 June 2012 09:43 Go to previous messageGo to next message
_jum
Messages: 485
Registered: February 2008
Senior Member
extract(value(d), '//ACTIVATIONTS/text()').getStringVal() must fail because the node in your little XML is //REGTIMESTAMP.
Try:
WITH xdata AS
 (SELECT XMLType(
 '<RECORD>
    <REGTIMESTAMP>20120601130010</REGTIMESTAMP>
  </RECORD>') xd FROM dual)
SELECT extract(value(d), '//REGTIMESTAMP/text()').getStringVal() AS REGTIMESTAMP
  FROM xdata x,
 TABLE(xmlsequence(extract(x.xd, '/RECORD'))) d;

REGTIMESTAMP                                                                    
--------------------------------------------------------------------------------
20120601130010                                                                  
1 row selected.

[Updated on: Mon, 04 June 2012 09:44]

Report message to a moderator

Re: PL/SQL XML Date Time omits Time part [message #556844 is a reply to message #556475] Wed, 06 June 2012 23:32 Go to previous message
Barbara Boehmer
Messages: 7860
Registered: November 2002
Location: California, USA
Senior Member
You haven't shown where or how you are using to_date or demonstrated the failure. The 20120601130010 is a character string and must be either be fetched into a character data type like varchar2 or rely on implicit conversion, which can cause problems. Then you can apply to_date to it. Then you can use to_char to display the stored date and time in any format you want, with or without the time. When you do not specify a date format, you get whatever the default nls_date_format is currently set to. Please see the demonstration below, noting the different code that produces the different date formats.

-- test data:
SCOTT@orcl_11gR2> create table t_xml_data
  2    (xml_message  xmltype)
  3  /

Table created.

SCOTT@orcl_11gR2> insert into t_xml_data values (xmltype (
  2  '<?xml version="1.0"?>
  3  <RECORD>
  4  <REGTIMESTAMP>20120601130010</REGTIMESTAMP>
  5  </RECORD>'))
  6  /

1 row created.

SCOTT@orcl_11gR2> select * from t_xml_data
  2  /

XML_MESSAGE
--------------------------------------------------------------------------------
<?xml version="1.0"?>
<RECORD>
  <REGTIMESTAMP>20120601130010</REGTIMESTAMP>
</RECORD>


1 row selected.


-- SQL with default date format:
SCOTT@orcl_11gR2> SELECT TO_DATE
  2  	      (extract(value(d), '//REGTIMESTAMP/text()').getStringVal(),
  3  	       'YYYYMMDDHH24MISS') AS REGTIMESTAMP
  4  FROM t_xml_data x,
  5  table(xmlsequence(extract(x.xml_message, '/RECORD'))) d
  6  /

REGTIMEST
---------
01-JUN-12

1 row selected.


-- SQL using to_char to specify date format:
SCOTT@orcl_11gR2> SELECT TO_CHAR
  2  	      (TO_DATE
  3  		(extract(value(d), '//REGTIMESTAMP/text()').getStringVal(),
  4  		 'YYYYMMDDHH24MISS'),
  5  		 'fmDay fmDD-Mon-YYYY HH24:MI:SS') AS REGTIMESTAMP
  6  FROM t_xml_data x,
  7  table(xmlsequence(extract(x.xml_message, '/RECORD'))) d
  8  /

REGTIMESTAMP
------------------------------------------------------------------
Friday 01-Jun-2012 13:00:10

1 row selected.


-- PL/SQL showing first deafult, then explicit date format:
SCOTT@orcl_11gR2> DECLARE
  2    CURSOR c_xml_record
  3    IS
  4    SELECT extract(value(d), '//REGTIMESTAMP/text()').getStringVal() AS REGTIMESTAMP
  5    FROM t_xml_data x,
  6    table(xmlsequence(extract(x.xml_message, '/RECORD'))) d;
  7    regtimestamp   VARCHAR2(14);
  8    activation_ts  DATE;
  9  BEGIN
 10    OPEN c_xml_record;
 11    LOOP
 12  	 FETCH c_xml_record INTO regtimestamp;
 13  	 EXIT WHEN c_xml_record%NOTFOUND;
 14  	 activation_ts := TO_DATE (regtimestamp, 'YYYYMMDDHH24MISS');
 15  	 DBMS_OUTPUT.PUT_LINE (activation_ts);
 16  	 DBMS_OUTPUT.PUT_LINE (TO_CHAR (activation_ts, 'fmDay fmDD-Mon-YYYY HH24:MI:SS'));
 17    END LOOP;
 18    CLOSE c_xml_record;
 19  END;
 20  /
01-JUN-12
Friday 01-Jun-2012 13:00:10

PL/SQL procedure successfully completed.


Previous Topic: XMLTABLE with xmlnamespaces XPST0005 issue
Next Topic: How to Select xml node from oracle database.. Help !! Project Is Due Soon....T.T
Goto Forum:
  


Current Time: Wed Apr 16 09:58:45 CDT 2014

Total time taken to generate the page: 0.07351 seconds