Home » Developer & Programmer » JDeveloper, Java & XML » Accessing XML element (oracle 10g-10.2.0.1.0)
Accessing XML element [message #352954] Fri, 10 October 2008 05:43 Go to next message
kanis
Messages: 61
Registered: November 2006
Member
I have a xml like below

<A>
<b>
<c>
<col>val1<col>
<col>val2<col>
<col>val3<col>
</c>
<c>
<col>val7<col>
<col>val8<col>
</c>
</b>
<b>
<c>
<col>val10<col>
</c>
<c>
<col>val11<col>
<col>val12<col>
</c>
</b>
</A>

Can any one let me know what code in pl/sql need to write if I want to access only "val10" from the above xml.

thanks for the help
Re: Accessing XML element [message #352959 is a reply to message #352954] Fri, 10 October 2008 05:50 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From your previous post:
Michel Cadot wrote on Fri, 10 October 2008 05:55
Post a test case: create table and insert statements along with the result you want with these data.

Before please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel


Re: Accessing XML element [message #353012 is a reply to message #352959] Fri, 10 October 2008 10:09 Go to previous messageGo to next message
kanis
Messages: 61
Registered: November 2006
Member
Soory Michel,

What I have posted is not a code it is ..a sample xml file .

Also I have put oracle version as 10.2.0.1.0 in product .

Also my requirement is quite clear to be posted in a forum .

[Updated on: Fri, 10 October 2008 10:44] by Moderator

Report message to a moderator

Re: Accessing XML element [message #353018 is a reply to message #353012] Fri, 10 October 2008 10:44 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Where is the test case?
And what did you try since your last questions?

Search here for xmlelement, extractvalue, xmlsequence, there are many examples.

Regards
Michel

[Updated on: Fri, 10 October 2008 10:49]

Report message to a moderator

Re: Accessing XML element [message #353153 is a reply to message #352954] Sun, 12 October 2008 01:10 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Can any one let me know what code in pl/sql need to write if I want to access only "val10" from the above xml.

Why "val10" and not "val1", "val2"...?

Regards
Michel
Re: Accessing XML element [message #353181 is a reply to message #353153] Sun, 12 October 2008 09:07 Go to previous messageGo to next message
kanis
Messages: 61
Registered: November 2006
Member
I want to access 2nd <b>'s 1st <c>.

ho to do that ?
Re: Accessing XML element [message #353616 is a reply to message #353181] Tue, 14 October 2008 05:57 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
After fixing your data:
SQL> with 
  2    data as (
  3      select 
  4  xmltype('<A>
  5  <b>
  6  <c>
  7  <col>val1</col>
  8  <col>val2</col>
  9  <col>val3</col>
 10  </c>
 11  <c>
 12  <col>val7</col>
 13  <col>val8</col>
 14  </c>
 15  </b>
 16  <b>
 17  <c>
 18  <col>val10</col>
 19  </c>
 20  <c>
 21  <col>val11</col>
 22  <col>val12</col>
 23  </c>
 24  </b>
 25  </A>') x
 26      from dual ),
 27    b as (
 28      select column_value b, rownum rn
 29      from data, table(xmlsequence(extract(x,'/A/b')))
 30    ),
 31    c as (
 32      select column_value c, rownum rn
 33      from (select b from b where rn=2), table(xmlsequence(extract(b,'/b/c'))) 
 34    )
 35  select extractvalue(value(c),'/col') val
 36  from (select c from c where rn=1), table(xmlsequence(extract(c, '/c/col'))) c
 37  /
VAL
------------
val10

1 row selected.

Regards
Michel

[Updated on: Tue, 14 October 2008 05:57]

Report message to a moderator

Previous Topic: How to download file on a single click of button in OAF
Next Topic: patch power objects
Goto Forum:
  


Current Time: Fri Mar 29 02:37:00 CDT 2024