Home » Developer & Programmer » JDeveloper, Java & XML » How to get XML node position (10.2.0.4)
How to get XML node position [message #478210] Thu, 07 October 2010 02:47 Go to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I have a xml data, I want to extract a node and retrieve the position of this node in the parent node.

For instance, I have the following XML
<table>
  <tr>
    <td>A</td>
  </tr>
  <tr>
    <td>B</td>
  </tr>
  <tr>
    <td>C</td>
  </tr>
</table>

and want to retrieve and get the position of TR node that contains the TD node with value B.

The following query retrieves the node:
SQL> with 
  2    data as (
  3      select xmltype('
  4  <table>
  5    <tr>
  6      <td>A</td>
  7    </tr>
  8    <tr>
  9      <td>B</td>
 10    </tr>
 11    <tr>
 12      <td>C</td>
 13    </tr>
 14  </table>
 15  ') val 
 16      from dual
 17    )
 18  select extract(value(x), '/tr') node
 19  from data, table(xmlsequence(extract(val, '/table/tr'))) x
 20  where extractvalue(value(x),'/tr/td') = 'B'
 21  /
NODE
----------------------------------------------------------------
<tr><td>B</td></tr>

Now how to get this is node 2 of TABLE node?

As far as I know (and I didn't find a way to use it elsewhere), "position" function can only be used as a select function:
SQL> with 
  2    data as (
  3      select xmltype('
  4  <table>
  5    <tr>
  6      <td>A</td>
  7    </tr>
  8    <tr>
  9      <td>B</td>
 10    </tr>
 11    <tr>
 12      <td>C</td>
 13    </tr>
 14  </table>
 15  ') val 
 16      from dual
 17    )
 18  select extract(val, '/table/tr[position()=2]') node
 19  from data
 20  /
NODE
---------------------------------------------------------
<tr><td>B</td></tr>

Regards
Michel
Re: How to get XML node position [message #478266 is a reply to message #478210] Thu, 07 October 2010 10:36 Go to previous messageGo to next message
_jum
Messages: 577
Registered: February 2008
Senior Member
Do You search for something like:
WITH xmldata 
  AS( SELECT xmltype('
   <table>
     <tr>
       <td>A</td>
     </tr>
     <tr>
       <td>B1</td>
       <td>B2</td>
     </tr>
     <tr>
       <td>C</td>
       <td>D</td>
       <td>A</td>
     </tr>
   </table>
   ') val FROM dual)
   SELECT 
     POSITION,
     extractvalue(column_value,'//td') p,
     existsNode  (column_value, '//*[text()="A"]') exnod
   FROM xmldata, xmltable('//tr/td' PASSING val COLUMNS POSITION FOR ORDINALITY)
   WHERE existsNode(column_value, '//*[text()="A"]')> 0;
positon p exnode
1	A	1
6	A	1
Re: How to get XML node position [message #478273 is a reply to message #478266] Thu, 07 October 2010 11:30 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Do You search for something like

No as I am only a basic user of XML and this is far from my knowledge.
Thanks I will try to use it in my program and will come back if I have any problem.
Have you any link about a tutorial on xml query?
I admit that in my searches on "xml node position" or the like I did not find anything like your "PASSING val COLUMNS POSITION FOR ORDINALITY" (which I currently don't understand but I will search for it).

Regards
Michel
Re: How to get XML node position [message #478339 is a reply to message #478273] Fri, 08 October 2010 00:50 Go to previous messageGo to next message
_jum
Messages: 577
Registered: February 2008
Senior Member
Didn't use this construct before Cool, found a clue in OTN and the syntax in XMLTABLE.
Re: How to get XML node position [message #478349 is a reply to message #478339] Fri, 08 October 2010 02:50 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Ah! "XPATH" is the keyword that was missing to get this thread.
I saw XMLTABLE synatx after I posted you.
I still now didn't have time to check if this fit my actual needs.

Thanks
Michel
Re: How to get XML node position [message #478511 is a reply to message #478349] Sun, 10 October 2010 01:38 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I succeed to make my program works and discover Oracle® XML DB Developer's Guide with your help. Smile

Thanks
Michel
Previous Topic: Network Adaptyer Error
Next Topic: Problem Query XML TABLE with attributes
Goto Forum:
  


Current Time: Thu Mar 28 12:51:45 CDT 2024