OraFAQ Forum: JDeveloper, Java & XML » Doubt In Using Extract Value

Home » Developer & Programmer » JDeveloper, Java & XML » Doubt In Using Extract Value (Oracle 10 g ,windows)

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

Doubt In Using Extract Value [message #345829]

Thu, 04 September 2008 23:59

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?

Report message to a moderator

Re: Doubt In Using Extract Value [message #345842 is a reply to message #345829]

Fri, 05 September 2008 01:01

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

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.

Report message to a moderator

Re: Doubt In Using Extract Value [message #345849 is a reply to message #345842]

Fri, 05 September 2008 01:32

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?

Report message to a moderator

Re: Doubt In Using Extract Value [message #345883 is a reply to message #345849]

Fri, 05 September 2008 03:41

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

It seems you didn't use the "Preview Message" button.

Regards
Michel

Report message to a moderator

Re: Doubt In Using Extract Value [message #346169 is a reply to message #345849]

Sat, 06 September 2008 20:38

Barbara Boehmer
Messages: 9101
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>

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Dec 17 22:54:02 CST 2024