5 Replies Latest reply: Aug 27, 2012 5:24 AM by odie_63 RSS

    how to count no of nodes under each parent in any given xml

    user7955917
      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
        • 1. Re: how to count no of nodes under each parent in any given xml
          odie_63
          SQL> select x.*
            2  from tmp_xml t
            3     , xmltable(
            4         '$d/descendant::*'
            5         passing t.object_value as "d"
            6         columns element_name varchar2(30) path 'name()'
            7               , child_count  number       path 'count(*)'
            8       ) x
            9  ;
           
          ELEMENT_NAME                   CHILD_COUNT
          ------------------------------ -----------
          books                                    3
          bookstore                                4
          book1                                    0
          book2                                    0
          book1                                    0
          book4                                    0
          bookstore                                6
          book1                                    0
          book2                                    0
          book3                                    0
          book3                                    0
          bookn_1                                  0
          bookn                                    0
          bookstore                                2
          book1                                    0
          book2                                    0
           
          16 rows selected
           
          • 2. Re: how to count no of nodes under each parent in any given xml
            user7955917
            is it possible to figure out , under any bookstore node if a tag got repeated . As stated in example book1 and book3 tags got repeated at bookstore level .
            bookstore[1]
            ---------------
            book1 2
            book2 1
            book3 1

            if we apply group by function for element it returns but how to know under which node the tag is repeated .
            Also please help if you can provide reference link on name() function , ans what are the other expression i can use after path

            Edited by: user7955917 on Aug 24, 2012 12:03 PM
            • 3. Re: how to count no of nodes under each parent in any given xml
              odie_63
              Depends on how generic you want the query to be.

              If the structure is known in advance, like in your example a root element "books" then some "bookstore", it is as simple as :
              SQL> select x1.parent_id, x2.child_name, count(*)
                2  from tmp_xml t
                3     , xmltable(
                4         '/books/bookstore'
                5         passing t.object_value
                6         columns parent_id   for ordinality
                7               , child_list  xmltype path '*'
                8       ) x1
                9     , xmltable(
               10         '/*'
               11         passing x1.child_list
               12         columns child_name varchar2(30) path 'name()'
               13       ) x2
               14  group by x1.parent_id, x2.child_name
               15  order by x1.parent_id, x2.child_name
               16  ;
               
               PARENT_ID CHILD_NAME                       COUNT(*)
              ---------- ------------------------------ ----------
                       1 book1                                   2
                       1 book2                                   1
                       1 book4                                   1
                       2 bookn                                   1
                       2 bookn_1                                 1
                       2 book1                                   1
                       2 book2                                   1
                       2 book3                                   2
                       3 book1                                   1
                       3 book2                                   1
               
              10 rows selected
               
              If you want a generic solution that works without knowing the structure, you'll need a recursive approach, and more importantly you have to know which nodes to distinct, I suppose only the leafs?

              Also please help if you can provide reference link on name() function , ans what are the other expression i can use after path
              You may want to start reading the documentation : http://docs.oracle.com/cd/E11882_01/appdev.112/e23094/xdb_xquery.htm#CBAGCBGJ

              The PATH clause expects an XQuery expression however, prior to 11.2, we can only put basic XPath expressions.
              • 4. Re: how to count no of nodes under each parent in any given xml
                user7955917
                i am using 10g so i have modified the above query as below . Thanks odie

                select x1.parent_id, x2.child_name.getrootelement() , count(1)
                from books t
                , xmltable(
                '/books/bookstore'
                passing t.bk_xml
                columns parent_id for ordinality
                , child_list xmltype path '.'
                ) x1
                , xmltable(
                '/bookstore/child::*'
                passing x1.child_list
                columns child_name xmltype path '.'
                ) x2
                where seq_id=10
                group by x1.parent_id, x2.child_name.getrootelement()
                having count(1) > 1

                I am planning to implement the generic approach using dom_api in pl/sql if i conquer i will post it here .
                other than oracle docs is there any link , which expain about object_relational storage and dom_api using plsql implemention examples.
                • 5. Re: how to count no of nodes under each parent in any given xml
                  odie_63
                  select x1.parent_id, x2.child_name.getrootelement() , count(1)
                  You're aware that "count(1)" will not get you any performance gain, aren't you?
                  It'll be rewritten as "count(*)" anyway.
                  i am using 10g so i have modified the above query as below .
                  And your exact version would be? (SELECT * FROM v$version)

                  Here's the modified query, not using getRootElement() :
                  select x1.parent_id, x2.child_name, count(*)
                  from tmp_xml t
                     , xmltable(
                         '/books/bookstore'
                         passing t.object_value
                         columns parent_id   for ordinality
                               , child_list  xmltype path '*'
                       ) x1
                     , xmltable(
                         'for $i in /*
                          return element child_name { name($i) }'
                         passing x1.child_list
                         columns child_name varchar2(30) path '.'
                       ) x2
                  group by x1.parent_id, x2.child_name
                  order by x1.parent_id, x2.child_name
                  ;
                  I am planning to implement the generic approach using dom_api in pl/sql if i conquer i will post it here .
                  Can you describe again what you need exactly, with another detailed example if possible?
                  Which level of nodes do you want to aggregate (distinct)? In my previous post, I asked about the leaf nodes, is that what you want?

                  other than oracle docs is there any link , which expain about object_relational storage and dom_api using plsql implemention examples.
                  The official Oracle online documentation is still the best place to look for Object Relational concepts.
                  See also the FAQ at the top of this forum, and of course, the many practical examples around here.