4 Replies Latest reply: Aug 31, 2012 9:00 AM by 943494 RSS

    XML - count / number of Nodes

    943494
      Oracle Version : Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi

      Hi All,

      I have the below xml , i would like to get the count(nodes) under <ts:updatewr> . how many nodes under <ts:updatewr>
      In this case it will be 6. please let me know how can i achieve this, i tried several option with xmltable all failed.

      Thanks in advance
      Rubu.

      <?xml version="1.0"?>
      <ts:rowset xmlns:ts="http://msw.ss.com/xsd/Test">
      <ts:row>
      <ts:updatewr>
      <ts:wr_no>18379</ts:wr_no>     
      <ts:company_cd>2</ts:company_cd>
      <ts:crew_hq_cd>5L5G</ts:crew_hq_cd>
      <ts:entry_dt>2012-06-30T17:00:00-5:00</ts:entry_dt>
      <ts:entry_person_ntid>rdennis</ts:entry_person_ntid>
      <ts:local_office_cd>2080</ts:local_office_cd>
      </ts:updatewr>
      </ts:row>
      </ts:rowset>
        • 1. Re: XML - count / number of Nodes
          Jason_(A_Non)
          This should help
          {thread:id=2431392}
          • 2. Re: XML - count / number of Nodes
            943494
            Thank You Jason. I tried the same way as odie suggested but got the below error. Thanks

            Code:-
            select x.*
            from table_xml t
            , xmltable(
            '$d/descendant::*'
            passing t.xml_column as "d"
            columns element_name varchar2(30) path 'name()',
            child_count number path 'count(*)'
            ) x

            Error :-
            ORA-31011: XML parsing failed
            ORA-19202: Error occurred in XML processing
            LPX-00601: Invalid token in: '/*/name()'
            31011. 00000 - "XML parsing failed"
            *Cause:    XML parser returned an error while trying to parse the document.
            *Action:   Check if the document to be parsed is valid.

            Edited by: 940491 on Aug 31, 2012 4:42 AM
            • 3. Re: XML - count / number of Nodes
              AlexAnd
              you have error because your version < 11
              odie_63's example work for > 11

              btw

              you can try for 10g
              SQL> select * from v$version where rownum=1;
               
              BANNER
              ----------------------------------------------------------------
              Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
               
              SQL> 
              SQL> with t as
                2  ( select xmltype(
                3  '<?xml version="1.0"?>
                4  <ts:rowset xmlns:ts="http://msw.ss.com/xsd/Test">
                5  <ts:row>
                6  <ts:updatewr>
                7  <ts:wr_no>18379</ts:wr_no>
                8  <ts:company_cd>2</ts:company_cd>
                9  <ts:crew_hq_cd>5L5G</ts:crew_hq_cd>
               10  <ts:entry_dt>2012-06-30T17:00:00-5:00</ts:entry_dt>
               11  <ts:entry_person_ntid>rdennis</ts:entry_person_ntid>
               12  <ts:local_office_cd>2080</ts:local_office_cd>
               13  </ts:updatewr>
               14  </ts:row>
               15  </ts:rowset>') xml from dual)
               16  --
               17  select count(*)
               18  from t,
               19       table(xmlsequence(extract(t.xml, '/ts:rowset/ts:row/ts:updatewr/*', 'xmlns:ts="http://msw.ss.com/xsd/Test"')))
               20  /
               
                COUNT(*)
              ----------
                       6
               
              SQL>
              SQL> with t as
                2  ( select xmltype(
                3  '<?xml version="1.0"?>
                4  <ts:rowset xmlns:ts="http://msw.ss.com/xsd/Test">
                5  <ts:row>
                6  <ts:updatewr>
                7  <ts:wr_no>18379</ts:wr_no>
                8  <ts:company_cd>2</ts:company_cd>
                9  <ts:crew_hq_cd>5L5G</ts:crew_hq_cd>
               10  <ts:entry_dt>2012-06-30T17:00:00-5:00</ts:entry_dt>
               11  <ts:entry_person_ntid>rdennis</ts:entry_person_ntid>
               12  <ts:local_office_cd>2080</ts:local_office_cd>
               13  </ts:updatewr>
               14  </ts:row>
               15  </ts:rowset>') xml from dual)
               16  --
               17  select count(*)
               18  from t,
               19       xmltable(
               20             XMLNamespaces('http://msw.ss.com/xsd/Test' AS "ts"),
               21            '$d/ts:rowset/ts:row/ts:updatewr/*'
               22             passing t.xml as "d"
               23             columns el varchar2(30) path '.'
               24           ) x
               25  /
               
                COUNT(*)
              ----------
                       6
               
              SQL> 
              or
              SQL> with t as
                2  ( select xmltype(
                3  '<?xml version="1.0"?>
                4  <ts:rowset xmlns:ts="http://msw.ss.com/xsd/Test">
                5  <ts:row>
                6  <ts:updatewr>
                7  <ts:wr_no>18379</ts:wr_no>
                8  <ts:company_cd>2</ts:company_cd>
                9  <ts:crew_hq_cd>5L5G</ts:crew_hq_cd>
               10  <ts:entry_dt>2012-06-30T17:00:00-5:00</ts:entry_dt>
               11  <ts:entry_person_ntid>rdennis</ts:entry_person_ntid>
               12  <ts:local_office_cd>2080</ts:local_office_cd>
               13  </ts:updatewr>
               14  </ts:row>
               15  </ts:rowset>') xml from dual)
               16  --
               17  select x.*
               18  from t,
               19       xmltable(
               20       'declare namespace ts = "http://msw.ss.com/xsd/Test"; (: :)
               21        for $p in $d/descendant::*
               22        return element r {element tag_name {fn:name($p)}, element tag_cnt {count($p/descendant::*)}}'
               23        passing t.xml as "d"
               24        columns tag_name    varchar2(30) path 'tag_name'
               25               , tag_cnt varchar2(30) path 'tag_cnt'
               26       ) x
               27  /
               
              TAG_NAME                       TAG_CNT
              ------------------------------ ------------------------------
              ts:rowset                      8
              ts:row                         7
              ts:updatewr                    6
              ts:wr_no                       0
              ts:company_cd                  0
              ts:crew_hq_cd                  0
              ts:entry_dt                    0
              ts:entry_person_ntid           0
              ts:local_office_cd             0
               
              9 rows selected
               
              SQL> 
              or
              SQL> with t as
                2  ( select xmltype(
                3  '<?xml version="1.0"?>
                4  <ts:rowset xmlns:ts="http://msw.ss.com/xsd/Test">
                5  <ts:row>
                6  <ts:updatewr>
                7  <ts:wr_no>18379</ts:wr_no>
                8  <ts:company_cd>2</ts:company_cd>
                9  <ts:crew_hq_cd>5L5G</ts:crew_hq_cd>
               10  <ts:entry_dt>2012-06-30T17:00:00-5:00</ts:entry_dt>
               11  <ts:entry_person_ntid>rdennis</ts:entry_person_ntid>
               12  <ts:local_office_cd>2080</ts:local_office_cd>
               13  </ts:updatewr>
               14  </ts:row>
               15  </ts:rowset>') xml from dual)
               16  --
               17  select x.*
               18  from t,
               19       xmltable(
               20       'declare namespace ts = "http://msw.ss.com/xsd/Test"; (: :)
               21        for $p in $d/descendant::*
               22        return element r {element tag_name {fn:name($p)}, element tag_cnt {count($p/*)}}'
               23        passing t.xml as "d"
               24        columns tag_name    varchar2(30) path 'tag_name'
               25               , tag_cnt varchar2(30) path 'tag_cnt'
               26       ) x
               27  /
               
              TAG_NAME                       TAG_CNT
              ------------------------------ ------------------------------
              ts:rowset                      1
              ts:row                         1
              ts:updatewr                    6
              ts:wr_no                       0
              ts:company_cd                  0
              ts:crew_hq_cd                  0
              ts:entry_dt                    0
              ts:entry_person_ntid           0
              ts:local_office_cd             0
               
              9 rows selected
               
              SQL>
              • 4. Re: XML - count / number of Nodes
                943494
                Thank You.