Home » Developer & Programmer » JDeveloper, Java & XML » EXTRACTVALUE MULTIPLE NODES
EXTRACTVALUE MULTIPLE NODES [message #325311] Thu, 05 June 2008 12:24 Go to next message
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!
Re: EXTRACTVALUE MULTIPLE NODES [message #325333 is a reply to message #325311] Thu, 05 June 2008 16:13 Go to previous messageGo to next message
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> 

Re: EXTRACTVALUE MULTIPLE NODES [message #325337 is a reply to message #325311] Thu, 05 June 2008 16:45 Go to previous messageGo to next message
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!!!
Re: EXTRACTVALUE MULTIPLE NODES [message #338063 is a reply to message #325311] Sat, 02 August 2008 21:54 Go to previous messageGo to next message
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
Re: EXTRACTVALUE MULTIPLE NODES [message #338254 is a reply to message #338063] Mon, 04 August 2008 05:49 Go to previous messageGo to next message
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
Re: EXTRACTVALUE MULTIPLE NODES [message #338290 is a reply to message #325311] Mon, 04 August 2008 08:02 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
Sorry, I am not really a java kind of guy.

Kevin
Re: EXTRACTVALUE MULTIPLE NODES [message #380283 is a reply to message #325337] Sat, 10 January 2009 03:09 Go to previous message
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.

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


Current Time: Sat Dec 14 13:09:31 CST 2024