OraFAQ Forum: JDeveloper, Java & XML » EXTRACTVALUE MULTIPLE NODES

Home » Developer & Programmer » JDeveloper, Java & XML » EXTRACTVALUE MULTIPLE NODES

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

EXTRACTVALUE MULTIPLE NODES [message #325311]

Thu, 05 June 2008 12:24

rtorrejon
Messages: 2
Registered: June 2008
Location: santiago

Junior Member

Hi there, i've seen some of your answers so i think what i need have to be very easy for you, i hope you can help me. I'm just starting using XML with ORACLE and all i need to do is getting the child node, but EXTRACTVALUE works only when tehere's just one node, so i don't know how to do it, can somebody help me, let use a simple XMl as example:

<PRODUCT>
<PLAN>
<COVER>
<COV_ID>69</COV_ID>
</COVER>
<COVER>
<COV_ID>64</COV_ID>
</COVER>
<COVER>
<COV_ID>65</COV_ID>
</COVER>
</PLAN>
<PLAN>
<COVER>
<COV_ID>69</COV_ID>
</COVER>
<COVER>
<COV_ID>64</COV_ID>
</COVER>
<COVER>
<COV_ID>65</COV_ID>
</COVER>
</PLAN>
</PRODUCT>

How can i extract COV_ID?

Thank you!

Report message to a moderator

Re: EXTRACTVALUE MULTIPLE NODES [message #325333 is a reply to message #325311]

Thu, 05 June 2008 16:13

Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA

Senior Member

SCOTT@orcl_11g> SELECT EXTRACTVALUE
  2  	      (column_value,
  3  	       '/PRODUCT/PLAN[' || rn1 || ']/COVER[' || rn2 || ']/COV_ID')
  4  FROM   TABLE (XMLSEQUENCE
  5  		    (XMLTYPE
  6  		      ('<PRODUCT>
  7  			  <PLAN>
  8  			    <COVER>
  9  			      <COV_ID>69</COV_ID>
 10  			    </COVER>
 11  			    <COVER>
 12  			      <COV_ID>64</COV_ID>
 13  			    </COVER>
 14  			    <COVER>
 15  			      <COV_ID>65</COV_ID>
 16  			    </COVER>
 17  			  </PLAN>
 18  			  <PLAN>
 19  			    <COVER>
 20  			      <COV_ID>69</COV_ID>
 21  			    </COVER>
 22  			    <COVER>
 23  			      <COV_ID>64</COV_ID>
 24  			    </COVER>
 25  			    <COVER>
 26  			      <COV_ID>65</COV_ID>
 27  			    </COVER>
 28  			  </PLAN>
 29  			</PRODUCT>'
 30  		       ))),
 31  	    (SELECT ROWNUM rn1
 32  	     FROM   DUAL
 33  	     CONNECT BY LEVEL <= 10),
 34  	    (SELECT ROWNUM rn2
 35  	     FROM   DUAL
 36  	     CONNECT BY LEVEL <= 10)
 37  WHERE  EXTRACTVALUE
 38  	      (column_value,
 39  	       '/PRODUCT/PLAN[' || rn1 || ']/COVER[' || rn2 || ']/COV_ID') IS NOT NULL
 40  /

EXTRACTVALUE(COLUMN_VALUE,'/PRODUCT/PLAN['||RN1||']/COVER['||RN2||']/COV_ID')
--------------------------------------------------------------------------------
69
64
65
69
64
65

6 rows selected.

SCOTT@orcl_11g>

Report message to a moderator

Re: EXTRACTVALUE MULTIPLE NODES [message #325337 is a reply to message #325311]

Thu, 05 June 2008 16:45

rtorrejon
Messages: 2
Registered: June 2008
Location: santiago

Junior Member

If i have a daughter, her name will be Barbara Very Happy

...thank you very much!!!

Report message to a moderator

Re: EXTRACTVALUE MULTIPLE NODES [message #338063 is a reply to message #325311]

Sat, 02 August 2008 21:54

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

Easy XML - Let the Database do the Work

Easy XML - a Programming Oriented Approach

Kevin Meade's blog

Not worthy of a named son, but helpful perhaps.

Good luck, Kevin

Report message to a moderator

Re: EXTRACTVALUE MULTIPLE NODES [message #338254 is a reply to message #338063]

Mon, 04 August 2008 05:49

david.grant
Messages: 1
Registered: August 2008
Location: South Africa

Junior Member

Hi,

Do you by any chance know what the Java equivilant of the ExtractValue function is?

I have been looking at javax.xml.xpath but it doesn't seem to be seeded on the Oracle E-business suite R11 database.

Any help will be appreciated.

Thanks

Report message to a moderator

Re: EXTRACTVALUE MULTIPLE NODES [message #338290 is a reply to message #325311]

Mon, 04 August 2008 08:02

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

Sorry, I am not really a java kind of guy.

Kevin

Report message to a moderator

Re: EXTRACTVALUE MULTIPLE NODES [message #380283 is a reply to message #325337]

Sat, 10 January 2009 03:09

Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA

Senior Member

I know this is an old thread, but in case anybody finds this by searching, the following is more efficient:

SCOTT@orcl_11g> SELECT extractvalue
  2          (column_value,
  3           '/COVER/COV_ID')
  4  FROM   TABLE
  5           (XMLSEQUENCE
  6             (EXTRACT
  7               (XMLTYPE
  8                 ('<PRODUCT>
  9                     <PLAN>
 10                       <COVER>
 11                         <COV_ID>69</COV_ID>
 12                       </COVER>
 13                       <COVER>
 14                         <COV_ID>64</COV_ID>
 15                       </COVER>
 16                       <COVER>
 17                         <COV_ID>65</COV_ID>
 18                       </COVER>
 19                     </PLAN>
 20                     <PLAN>
 21                       <COVER>
 22                         <COV_ID>69</COV_ID>
 23                       </COVER>
 24                       <COVER>
 25                         <COV_ID>64</COV_ID>
 26                       </COVER>
 27                       <COVER>
 28                         <COV_ID>65</COV_ID>
 29                       </COVER>
 30                     </PLAN>
 31                   </PRODUCT>'),
 32               '/PRODUCT/PLAN/COVER')))
 33  /

EXTRACTVALUE(COLUMN_VALUE,'/COVER/COV_ID')
------------------------------------------------------------------
69
64
65
69
64
65

6 rows selected.

Report message to a moderator

Previous Topic:	SQL to do insert from XML file in external table
Next Topic:	getting data out of unconventional XML

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Dec 17 22:29:32 CST 2024