This discussion is archived
4 Replies Latest reply: Aug 31, 2012 7:00 AM by 943494 RSS

XML - count / number of Nodes

943494 Newbie
Currently Being Moderated
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) Expert
    Currently Being Moderated
    This should help
    {thread:id=2431392}
  • 2. Re: XML - count / number of Nodes
    943494 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Thank You.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points