OraFAQ Forum: JDeveloper, Java & XML » how to get last record value from a xml file

Home » Developer & Programmer » JDeveloper, Java & XML » how to get last record value from a xml file (oracle 11.1.0.0)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

how to get last record value from a xml file [message #603989]

Sun, 22 December 2013 02:45

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

Report message to a moderator

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

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Can you give us an example?

Report message to a moderator

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

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.

Report message to a moderator

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

Lalit Kumar B
Messages: 3174
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

Report message to a moderator

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

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

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

Barbara Boehmer
Messages: 9104
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.

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jan 30 15:34:26 CST 2025