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
Messages: 35
Registered: March 2006
Location: Bangalore
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"?>

PL/SQL Extract:

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

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

CLOSE c_xml_record;

Please help
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
Messages: 25426
Registered: January 2009
Location: SoCal
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

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
Messages: 510
Registered: February 2008
Senior Member
extract(value(d), '//ACTIVATIONTS/text()').getStringVal() must fail because the node in your little XML is //REGTIMESTAMP.
WITH xdata AS
  </RECORD>') xd FROM dual)
SELECT extract(value(d), '//REGTIMESTAMP/text()').getStringVal() AS REGTIMESTAMP
  FROM xdata x,
 TABLE(xmlsequence(extract(x.xd, '/RECORD'))) d;

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: 8709
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 version="1.0"?>

1 row selected.

-- SQL with default date format:
  2  	      (extract(value(d), '//REGTIMESTAMP/text()').getStringVal(),
  4  FROM t_xml_data x,
  5  table(xmlsequence(extract(x.xml_message, '/RECORD'))) d
  6  /


1 row selected.

-- SQL using to_char to specify date format:
  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  /

Friday 01-Jun-2012 13:00:10

1 row selected.

-- PL/SQL showing first deafult, then explicit date format:
  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  /
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: Sat May 27 08:47:24 CDT 2017

Total time taken to generate the page: 0.11625 seconds