how to count no of nodes under each parent in any given xml
how to count no of nodes under each parent in any given xml . for example the below xml has
books has 3 childern , bookstore[1] has 4 , bookstore[2] has 6 and bookstore[3] has 2 . is it possible to get count of duplicate tags in a childnode ie bookstore[1]
has book1 tage repeated twice .. vice versa . do we need to go plsql are we can achieve it via sql
<books>
<bookstore>
<book1>abc</book1>
<book2>bca</book2>
<book1>cab</book1>
<book4>bac</book4>
</bookstore>
<bookstore>
<book1>abc</book1>
<book2>bca</book2>
<book3>cab</book3>
<book3>cab</book3>
<bookn_1>bac</bookn_1>
<bookn>acb</bookn>
</bookstore>
<bookstore>
<book1>abc</book1>
<book2>bca</book2>
</bookstore>
</books>
i have tried this ... query .
select
xmlquery('count($doc/books/bookstore[1]/descendant::*)' passing xmltype('<books>
<bookstore>
<book1>abc</book1>
<book1>cab</book1>
<book4>bac</book4>
</bookstore>
<bookstore>
<book1>abc</book1>
<book2>bca</book2>
</bookstore>
</books>')
as "doc" returning content).getNumberVal() as node_count
from dual
select
xmlquery('count($doc/books/descendant::*)' passing xmltype('<books>
<bookstore>
<book1>abc</book1>
<book1>cab</book1>
<book4>bac</book4>
</bookstore>
<bookstore>
<book1>abc</book1>
<book2>bca</book2>
</bookstore>
</books>')
as "doc" returning content).getNumberVal() as node_count
from dual
how can i get the counts for each parent in one single query
Edited by: user7955917 on Aug 24, 2012 7:26 AM