Home » Developer & Programmer » JDeveloper, Java & XML » how to get last record value from a xml file (oracle 11.1.0.0)
how to get last record value from a xml file [message #603989] Sun, 22 December 2013 02:45 Go to next message
mrpranab
Messages: 32
Registered: March 2005
Member
Hi,

I am generating xml data in a procedure and store it in a xmltype variable. I need to find last record from the xml file and retrieve the value of a leaf (ex: account_no column). Is there any xml function from where i can get my value? Looping the xmlfile and retrieve value will hit performance and so better if we get any in built in function which help to solve my query. My xml file generated in a single line using xmlagg.

Let me know if any query.

Thanks in Advance.

~Pranab
Re: how to get last record value from a xml file [message #603990 is a reply to message #603989] Sun, 22 December 2013 04:01 Go to previous messageGo to next message
Michel Cadot
Messages: 59414
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Can you give us an example?

Re: how to get last record value from a xml file [message #604019 is a reply to message #603990] Sun, 22 December 2013 23:49 Go to previous messageGo to next message
mrpranab
Messages: 32
Registered: March 2005
Member
For example my xml file like below:

<record><fname> Sachin </fname><lname> John </lname><account_no>456789</account_no><record_type> book </record_type></record><record> <fname> Jim </fname><lname> Anderson </lname><account_no>325897</account_no><record_type> magazine </record_type></record><record> <fname> Johnty </fname><lname> Anderson </lname><account_no>258934</account_no><record_type> book </record_type></record>

output expected:
account_no = 258934

Thanks.
Re: how to get last record value from a xml file [message #604020 is a reply to message #604019] Mon, 23 December 2013 00:23 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2444
Registered: May 2013
Location: World Wide on the Web
Senior Member
If I treat the xml as a string, then :

SQL> WITH DATA AS
  2   (SELECT '<record><fname> Sachin </fname><lname> John </lname><account_no>456789</account_no><record_type>
  3    book </record_type></record><record> <fname> Jim </fname><lname> Anderson </lname><account_no>325897</account_no>
  4    <record_type> magazine </record_type></record><record> <fname> Johnty </fname><lname> Anderson </lname><account_no>
  5    258934</account_no><record_type> book </record_type></record>' A
  6      FROM DUAL)
  7  SELECT 'account_no' || ' = ' || REGEXP_SUBSTR (SUBSTR(A,
  8                                                         INSTR(A,
  9                                                               '<account_no>',
 10                                                               -1,
 11                                                               1) + 12), '[[:digit:]]{1,6}') ACCOUNT_NO
 12    FROM DATA;
 
ACCOUNT_NO
-------------------
account_no = 258934
Re: how to get last record value from a xml file [message #604024 is a reply to message #604019] Mon, 23 December 2013 01:23 Go to previous messageGo to next message
Michel Cadot
Messages: 59414
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Your example is not a valid xml as it contains several items (here "record"), you have to add a top item to make a xml document (I added "records"):
SQL> with data as (
  2    select '<records>' || 
  3           '<record><fname> Sachin </fname><lname> John </lname><account_no>456789</account_no>
  4  <record_type> book </record_type></record><record> <fname> Jim </fname><lname> Anderson </lname>
  5  <account_no>325897</account_no><record_type> magazine </record_type></record><record> 
  6  <fname> Johnty </fname><lname> Anderson </lname><account_no>258934</account_no>
  7  <record_type> book </record_type></record>' || 
  8          '</records>' val
  9    from dual
 10  )
 11  select extractvalue(value(x),'//account_no') account_no
 12  from data, table(xmlsequence(extract(xmltype(val),'//record'))) x
 13  /
ACCOUNT_NO
-----------------------------------------------------------------------------------
456789
325897
258934

3 rows selected.

I let you find the last one fro this query.

[Updated on: Mon, 23 December 2013 01:24]

Report message to a moderator

Re: how to get last record value from a xml file [message #604074 is a reply to message #604020] Mon, 23 December 2013 11:55 Go to previous message
Barbara Boehmer
Messages: 7995
Registered: November 2002
Location: California, USA
Senior Member
SCOTT@orcl12c> -- test data:
SCOTT@orcl12c> WITH
  2    data AS
  3  	 (SELECT XMLTYPE
  4  		   ('<records>
  5  		       <record>
  6  			 <fname> Sachin </fname>
  7  			 <lname> John </lname>
  8  			 <account_no>456789</account_no>
  9  			 <record_type> book </record_type>
 10  		       </record>
 11  		       <record>
 12  			 <fname> Jim </fname>
 13  			 <lname> Anderson </lname>
 14  			 <account_no>325897</account_no>
 15  			 <record_type> magazine </record_type>
 16  		       </record>
 17  		       <record>
 18  			 <fname> Johnty </fname>
 19  			 <lname> Anderson </lname>
 20  			 <account_no>258934</account_no>
 21  			 <record_type> book </record_type>
 22  		       </record>
 23  		     </records>') val
 24  	  FROM	DUAL)
 25  -- query:
 26  SELECT account_no
 27  FROM   (SELECT *
 28  	     FROM   data,
 29  		    XMLTABLE
 30  		      ('//record'
 31  		       PASSING val
 32  		       COLUMNS
 33  			 seq FOR ORDINALITY,
 34  			 account_no NUMBER PATH 'account_no')
 35  	     ORDER  BY seq DESC)
 36  WHERE  ROWNUM = 1
 37  /

ACCOUNT_NO
----------
    258934

1 row selected.

Previous Topic: ORA-24324: service handle not initialized
Next Topic: oracle dbms_xmlquery.getxml error bind name identifier does not exist
Goto Forum:
  


Current Time: Wed Oct 22 03:20:58 CDT 2014

Total time taken to generate the page: 0.08258 seconds