Home » Developer & Programmer » JDeveloper, Java & XML » To find the number of nodes under a parent node (XML) (merged) (Oracle)
To find the number of nodes under a parent node (XML) (merged) [message #481883] Mon, 08 November 2010 01:27 Go to next message
arunkumarsd
Messages: 40
Registered: June 2009
Location: India
Member
Hi

I have a column defined as XMLTYPE and it have a xml init.

sample XML used:
<row10>
<Col0>10001474</Col0 >
<Col1>ALL STICK LABEL LIMITED</Col1 >
<Col2>532250</Col2 >
<Col3>0</Col3 >
<Col4>0</Col4 >
<Col5>0</Col5 >
<Col6>0</Col6 >
</row10>

Solution Needed:

I need to find out the number of child nodes present under <row10> parent node. The number of child columns are set to vary. so i need to first get the number of child nodes present.

Is there any XMLTYPE function present to accomplish this ?


Re: To find the number of nodes under a parent node (XML) [message #481884 is a reply to message #481883] Mon, 08 November 2010 02:01 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> with 
  2    data as (
  3      select xmltype('<row10>
  4  <Col0>10001474</Col0 >
  5  <Col1>ALL STICK LABEL LIMITED</Col1 >
  6  <Col2>532250</Col2 >
  7  <Col3>0</Col3 >
  8  <Col4>0</Col4 >
  9  <Col5>0</Col5 >
 10  <Col6>0</Col6 >
 11  </row10>') val 
 12      from dual
 13    )
 14  select count(*) 
 15  from data, table(xmlsequence(extract(val, '/row10/*')))
 16  /
  COUNT(*)
----------
         7

Regards
Michel
Re: To find the number of nodes under a parent node (XML) [message #481889 is a reply to message #481884] Mon, 08 November 2010 02:20 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Another way:
SQL> declare
  2    val clob := '<row10>
  3  <Col0>10001474</Col0 >
  4  <Col1>ALL STICK LABEL LIMITED</Col1 >
  5  <Col2>532250</Col2 >
  6  <Col3>0</Col3 >
  7  <Col4>0</Col4 >
  8  <Col5>0</Col5 >
  9  <Col6>0</Col6 >
 10  </row10>';
 11    i pls_integer;
 12  begin
 13    i := 1;
 14    while xmltype(val).existsnode('/row10/*['||i||']') = 1 loop
 15      i := i + 1;
 16    end loop;
 17    dbms_output.put_line('Nb nodes = '||(i-1));
 18  end;
 19  /
Nb nodes = 7

Regards
Michel
icon6.gif  Re: To find the number of nodes under a parent node (XML) [message #481897 is a reply to message #481884] Mon, 08 November 2010 02:52 Go to previous messageGo to next message
arunkumarsd
Messages: 40
Registered: June 2009
Location: India
Member
Thank you Smile
selecting the number of second level parent node (XMLTYPE) [message #482170 is a reply to message #481883] Wed, 10 November 2010 04:11 Go to previous messageGo to next message
arunkumarsd
Messages: 40
Registered: June 2009
Location: India
Member
Prob desc:

consider the following xml:

'<document>
<row1>
<Col0>Division</Col0 >
<Col1>POWER</Col1 >
</row1>
<row2>
<Col0>Region</Col0 >
<Col1>NORTH AMERICA</Col1 >
</row2>
<row3>
<Col0>Plant</Col0 >
<Col1>PORTLAND</Col1 >
</row3>
<row4>
<Col0>Plant1</Col0 >
<Col1>PORTLAND11</Col1 >
</row4>'

I need to find how many sub parent nodes present in a xml data. for exp ... from the above xml i need how many first level nodes present under <document> ...

expected result : 4

How to get this ?

Re: selecting the number of second level parent node (XMLTYPE) [message #482172 is a reply to message #482170] Wed, 10 November 2010 04:13 Go to previous messageGo to next message
ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Its a FAQ
search before posting....

sriram
Re: selecting the number of second level parent node (XMLTYPE) [message #482229 is a reply to message #482172] Wed, 10 November 2010 08:31 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You posted this question 2 days ago, you have an answer, I remember your feedback:

arunkumarsd wrote on Mon, 08 November 2010 09:52
Thank you Smile

Thank you for it, what do you want more?

Regards
Michel

[Updated on: Wed, 10 November 2010 08:38]

Report message to a moderator

Re: selecting the number of second level parent node (XMLTYPE) [message #482256 is a reply to message #482229] Wed, 10 November 2010 11:06 Go to previous messageGo to next message
ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Thats why I posted this "Its a FAQ" Wink
Re: selecting the number of second level parent node (XMLTYPE) [message #482299 is a reply to message #482256] Wed, 10 November 2010 23:22 Go to previous messageGo to next message
arunkumarsd
Messages: 40
Registered: June 2009
Location: India
Member
Dear ram/Michel,

My previous and this qsn are slightly diff. Thats y i have posted it again. Not knowing that i have already posted similar one few days back.

Michel,
Your previous solution was really useful but with this one i am not able to get the count of second level parent nodes. I need the count of how many nodes like <row1>, <row2> (together) ... are there under <document> tag. I mean how many nodes with <row%> ( similar to LIKE 'value%' in oracle) . Is there a way for it ?

Right now i am only able to get individual count for <row1> and <row2> separately. But i need to get a collective count of all nodes having name LIKE <row%> .....


Re: selecting the number of second level parent node (XMLTYPE) [message #482309 is a reply to message #482299] Thu, 11 November 2010 00:32 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is exactly the same question than the previous one: "To find the number of nodes under a parent node".
In your previous question the paretn node was named "row10" in this one it is named "document" this is the only difference.

Besides I merge the two topics to clearly show the similarity.

Regards
Michel
Re: selecting the number of second level parent node (XMLTYPE) [message #482319 is a reply to message #482309] Thu, 11 November 2010 01:11 Go to previous messageGo to next message
arunkumarsd
Messages: 40
Registered: June 2009
Location: India
Member
I tried it now .. it worked Smile

Thanks for bearing me Smile

Thank you !
Re: selecting the number of second level parent node (XMLTYPE) [message #482341 is a reply to message #482319] Thu, 11 November 2010 02:00 Go to previous message
ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
So next time
Quote:
search before posting....

Sriram
Previous Topic: problem with xmldom package
Next Topic: XML Parsing
Goto Forum:
  


Current Time: Thu Mar 28 18:58:18 CDT 2024