Home » Developer & Programmer » JDeveloper, Java & XML » Doubt In Using Extract Value (Oracle 10 g ,windows)
Doubt In Using Extract Value [message #345829] Thu, 04 September 2008 23:59 Go to next message
manickam
Messages: 9
Registered: June 2005
Location: chennai
Junior Member

Dear Experts,


Declare

X xmltype;
Cur_Out refcursor;

begin

X := xmltype( '
<ArrayOfCostBO>

<CostBO XmlNodeCount="1">
<PeriodList Period="a" />
</CostBO>

<CostBO XmlNodeCount="2">
<PeriodList Period="b" />
<PeriodList Period="c" />
</CostBO>

</ArrayOfCostBO>'
);

open Cur_Out for
SELECT
EXTRACTVALUE(VALUE(t), '/PeriodList/@Period') COL1, EXTRACTVALUE(VALUE(t), 'CostBO/@XmlNodeCount') COL2

FROM TABLE(XMLSEQUENCE(EXTRACT(X, '/ArrayOfCostBO/CostBO/PeriodList'))) t,
TABLE(XMLSEQUENCE(EXTRACT(VALUE(t), '/ArrayOfCostBO/CostBO/PeriodList')))(+) tx ;


end;







The above query returns me


COl1(Period) Col2 (XMLNodeCount)
a
b
c







But the expected result is ,


COl1(Period)----------Col2 (XMLNodeCount)
a-------------------1
b-------------------2
c-------------------2




How to achieve it?






Re: Doubt In Using Extract Value [message #345842 is a reply to message #345829] Fri, 05 September 2008 01:01 Go to previous messageGo to next message
Michel Cadot
Messages: 58545
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From your previous topic:
Michel Cadot wrote on Tue, 02 September 2008 10:13
Post a test case that we can reproduce.
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).
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel

And remove all useless empty lines.
Re: Doubt In Using Extract Value [message #345849 is a reply to message #345842] Fri, 05 September 2008 01:32 Go to previous messageGo to next message
manickam
Messages: 9
Registered: June 2005
Location: chennai
Junior Member

DECLARE
x XMLTYPE;
Cur_Out REFCURSOR;
BEGIN
x := xmlType('
<ArrayOfCostBO>

<CostBO XmlNodeCount="1">
<PeriodList Period="a" />
</CostBO>

<CostBO XmlNodeCount="2">
<PeriodList Period="b" />
<PeriodList Period="c" />
</CostBO>

</ArrayOfCostBO>');


open Cur_Out for

SELECT
EXTRACTVALUE(VALUE(t), '/PeriodList/@Period')COL1,
EXTRACTVALUE(VALUE(t), 'CostBO/@XmlNodeCount') COL2
FROM
TABLE
(XMLSEQUENCE(EXTRACT(X, '/ArrayOfCostBO/CostBO/PeriodList'))) t,
TABLE
(XMLSEQUENCE(EXTRACT(VALUE
(t), '/ArrayOfCostBO/CostBO/PeriodList')))(+) tx ;


END;




Execution of the above query returns me

COl1(Period) Col2 (XMLNodeCount)
a
b
c


But the expected result is ,

COl1(Period)----------Col2 (XMLNodeCount)
a-------------------1
b-------------------2
c-------------------2




How to achieve the expected result set?

Re: Doubt In Using Extract Value [message #345883 is a reply to message #345849] Fri, 05 September 2008 03:41 Go to previous messageGo to next message
Michel Cadot
Messages: 58545
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It seems you didn't use the "Preview Message" button.

Regards
Michel
Re: Doubt In Using Extract Value [message #346169 is a reply to message #345849] Sat, 06 September 2008 20:38 Go to previous message
Barbara Boehmer
Messages: 7934
Registered: November 2002
Location: California, USA
Senior Member
SCOTT@orcl_11g> COLUMN col1 FORMAT A20
SCOTT@orcl_11g> COLUMN col2 FORMAT A20
SCOTT@orcl_11g> SET AUTOPRINT ON
SCOTT@orcl_11g> VARIABLE cur_out REFCURSOR
SCOTT@orcl_11g> DECLARE
  2    x	XMLTYPE;
  3    -- Cur_Out  REFCURSOR;
  4  BEGIN
  5    x := xmlType('
  6  <ArrayOfCostBO>
  7  <CostBO XmlNodeCount="1">
  8  <PeriodList Period="a" />
  9  </CostBO>
 10  <CostBO XmlNodeCount="2">
 11  <PeriodList Period="b" />
 12  <PeriodList Period="c" />
 13  </CostBO>
 14  </ArrayOfCostBO>');
 15    open :Cur_Out for
 16    SELECT EXTRACTVALUE(VALUE(tx), '/PeriodList/@Period') COL1,
 17  	      EXTRACTVALUE(VALUE(t), '/CostBO/@XmlNodeCount') COL2
 18    FROM   TABLE (XMLSEQUENCE(EXTRACT(X, '/ArrayOfCostBO/CostBO'))) t,
 19  	      TABLE (XMLSEQUENCE(EXTRACT(VALUE(t),'CostBO/PeriodList')))(+) tx;
 20  END;
 21  /

PL/SQL procedure successfully completed.


COL1                 COL2
-------------------- --------------------
a                    1
b                    2
c                    2

SCOTT@orcl_11g>


Previous Topic: Template not selected in XML
Next Topic: Oracle Exception during Select
Goto Forum:
  


Current Time: Thu Jul 24 22:54:19 CDT 2014

Total time taken to generate the page: 0.06564 seconds