OraFAQ Forum: JDeveloper, Java & XML » Retrieve Element value

Home » Developer & Programmer » JDeveloper, Java & XML » Retrieve Element value

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

Retrieve Element value [message #246551]

Thu, 21 June 2007 05:17

vinayakawasthi
Messages: 4
Registered: July 2005

Junior Member

Hi All,

I have the following XML

<HEADER>
<DIMENSION_VALUES DIMENSION_ID="SOR" SELECTOR="EXCLUDE">
<DIMENSION_GROUP_ID>22</DIMENSION_GROUP_ID>
</DIMENSION_VALUES>
<DIMENSION_VALUES DIMENSION_ID="LOC" SELECTOR="ALL"/>
<DIMENSION_VALUES DIMENSION_ID="BG" SELECTOR="ALL"/>
<DIMENSION_VALUES DIMENSION_ID="PW" SELECTOR="ALL"/>
<DIMENSION_VALUES DIMENSION_ID="PC" SELECTOR="NONE"/>
<DIMENSION_VALUES DIMENSION_ID="PR" SELECTOR="EXCLUDE">
<DIMENSION_GROUP_ID>25</DIMENSION_GROUP_ID>
<DIMENSION_GROUP_ID>27</DIMENSION_GROUP_ID>
<DIMENSION_GROUP_ID>26</DIMENSION_GROUP_ID>
</DIMENSION_VALUES>
</HEADER>

I want the value of DIMENSION_GROUP_ID based on the value of DIMENSION_ID. So for DIMENSION_ID = PR, result should be 25, 26, 27 and for DIMENSION_ID = SOR only 22. For others it should be none.

I wrote the following query but not getting the answer:

select extract(SYS_NC_ROWINFO$,'/HEADER/DIMENSION_VALUES/DIMENSION_GROUP_ID') ac
from mytab
where existsnode(SYS_NC_ROWINFO$,'/HEADER/DIMENSION_VALUES[@DIMENSION_ID="LOC"]')=1 ;

Please let me know where am I doing wrong ?

Regards
Vinayak

Report message to a moderator

Re: Retrieve Element value [message #249249 is a reply to message #246551]

Tue, 03 July 2007 14:47

mchadder
Messages: 224
Registered: May 2005
Location: UK

Senior Member

Hi,

One approach is to utilise the "outer join" syntax, specified
here.

Here's an example for your case, in PL/SQL using an XMLTYPE variable :

SQL> L
  1  DECLARE
  2    x  XMLTYPE := XMLTYPE('<HEADER>
  3  <DIMENSION_VALUES DIMENSION_ID="SOR" SELECTOR="EXCLUDE">
  4  <DIMENSION_GROUP_ID>22</DIMENSION_GROUP_ID>
  5  </DIMENSION_VALUES>
  6  <DIMENSION_VALUES DIMENSION_ID="LOC" SELECTOR="ALL"/>
  7  <DIMENSION_VALUES DIMENSION_ID="BG" SELECTOR="ALL"/>
  8  <DIMENSION_VALUES DIMENSION_ID="PW" SELECTOR="ALL"/>
  9  <DIMENSION_VALUES DIMENSION_ID="PC" SELECTOR="NONE"/>
 10  <DIMENSION_VALUES DIMENSION_ID="PR" SELECTOR="EXCLUDE">
 11  <DIMENSION_GROUP_ID>25</DIMENSION_GROUP_ID>
 12  <DIMENSION_GROUP_ID>27</DIMENSION_GROUP_ID>
 13  <DIMENSION_GROUP_ID>26</DIMENSION_GROUP_ID>
 14  </DIMENSION_VALUES>
 15  </HEADER>');
 16  BEGIN
 17    FOR i IN ( SELECT
 18                 EXTRACTVALUE(VALUE(t), '/DIMENSION_VALUES/@DIMENSION_ID') dim_id,
 19                 EXTRACTVALUE(VALUE(tx), '/DIMENSION_GROUP_ID') dim_grp_id
 20               FROM
 21                 TABLE(XMLSEQUENCE(EXTRACT(x, '/HEADER/DIMENSION_VALUES'))) t,
 22                 TABLE(XMLSEQUENCE(EXTRACT(VALUE(t), '/DIMENSION_VALUES/DIMENSION_GROUP_ID'))) (+) tx  )
 23    LOOP
 24      dbms_output.put_line('DIM_ID : ' || i.dim_id || ' : ' || i.dim_grp_id);
 25    END LOOP;
 26* END;
SQL> /
DIM_ID : SOR : 22
DIM_ID : LOC :
DIM_ID : BG :
DIM_ID : PW :
DIM_ID : PC :
DIM_ID : PR : 25
DIM_ID : PR : 27
DIM_ID : PR : 26

PL/SQL procedure successfully completed.

Regards

Report message to a moderator

Previous Topic:	xml
Next Topic:	java.sql.SQLException: Failed to access a CLOB column

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Nov 24 22:15:18 CST 2024