7 Replies Latest reply: Jan 7, 2013 11:45 AM by Etbin RSS

    counting specific nodes

    Etbin
      Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
      PL/SQL Release 11.2.0.2.0 - Production
      CORE     11.2.0.2.0            Production
      TNS for Linux: Version 11.2.0.2.0 - Production
      NLSRTL Version 11.2.0.2.0 - Production
      In the only (clob type) column <tt><b>clob_column</b></tt> of a <tt><b>clob_gtt</b></tt> global temporary table (on commit delete rows) I have something (simplified for obvious reasons) like
      <?xml version="1.0" encoding="utf-8" ?>
      <Envelope xmlns="http://path_1.xsd"
                xmlns:xs="http://www.w3.org/2001/XMLSchema"
                        xmlns:kp="http://path_2.xsd"
                        xmlns:ep="http://path_3.xsd">
        <ep:Header>
          <ep:something> ... </ep:something>
          <ep:Workflow> ... </ep:Workflow>
        </ep:Header>
        <ep:Signatures></ep:Signatures>
        <body>
          <Data>
                         <Year>2012</Year>
                         <TransactionUserData Id="12345678" ... > ... </TransactionUserData>
                         ...
                         <TransactionUserData Id="87654321" ... > ... </TransactionUserData>
          </Data>
        </body>
      </Envelope>
      The requirement is simple: count <TransactionUserData> nodes
      Trying to cope with increasing xml oriented approach I tried
      select count(*) the_count
        from clob_gtt c,
             xmltable('/Envelope/body/Data/TransactionUserData'
                      passing xmltype(c.clob_column)
                     )  
      but I'm doing something wrong as I'm getting 0 in return while
      select regexp_count(clob_column,'<TransactionUserData ') the_count
        from clob_gtt
      returns a (hopefully correct as not yet verified) positive number
      Both queries select uncommitted data.

      Thanks in advance

      Etbin
        • 1. Re: counting specific nodes
          Stew Ashton
          Warning: I had to remove xmlns="http://path_1.xsd" to get this to work.
          with data as (
            SELECT XMLTYPE('<?xml version="1.0" encoding="utf-8" ?>
            <Envelope 
              xmlns:xs="http://www.w3.org/2001/XMLSchema"
              xmlns:kp="http://path_2.xsd"
              xmlns:ep="http://path_3.xsd">
              <ep:Header>
                <ep:something></ep:something>
                <ep:Workflow></ep:Workflow>
              </ep:Header>
              <ep:Signatures></ep:Signatures>
              <body>
                <Data>
                  <Year>2012</Year>
                  <TransactionUserData Id="12345678"  >  </TransactionUserData>
                  <TransactionUserData Id="87654321"  >  </TransactionUserData>
                </Data>
              </body>
            </Envelope>
            ') xml_text from dual
          )
          select column_value from data,
          xmltable('count(/Envelope/body/Data/TransactionUserData)' passing xml_text);
          
          COLUMN_VALUE
          ------------
          2
          • 2. Re: counting specific nodes
            odie_63
            You're missing the namespace mapping declaration.

            I also strongly suggest you use a GTT of binary XMLType instead (the default storage on your version) :
            SQL> create global temporary table binxml_gtt of xmltype;
             
            Table created
             
            SQL> 
            SQL> insert into binxml_gtt values (
              2  xmlparse(document '<?xml version="1.0" encoding="utf-8" ?>
              3  <Envelope xmlns="http://path_1.xsd"
              4            xmlns:xs="http://www.w3.org/2001/XMLSchema"
              5                    xmlns:kp="http://path_2.xsd"
              6                    xmlns:ep="http://path_3.xsd">
              7    <ep:Header>
              8      <ep:something> ... </ep:something>
              9      <ep:Workflow> ... </ep:Workflow>
             10    </ep:Header>
             11    <ep:Signatures></ep:Signatures>
             12    <body>
             13      <Data>
             14                     <Year>2012</Year>
             15                     <TransactionUserData Id="12345678" > ... </TransactionUserData>
             16                     ...
             17                     <TransactionUserData Id="87654321" > ... </TransactionUserData>
             18      </Data>
             19    </body>
             20  </Envelope>')
             21  ) ;
             
            1 row inserted
             
            SQL> 
            SQL> select count(*) the_count
              2  from binxml_gtt t
              3     , xmltable(
              4         xmlnamespaces(default 'http://path_1.xsd')
              5       , '/Envelope/body/Data/TransactionUserData'
              6         passing t.object_value
              7       ) x
              8  ;
             
             THE_COUNT
            ----------
                     2
             
            • 3. Re: counting specific nodes
              Stew Ashton
              Marc, it seems you prefer doing the count in SQL to doing it directly in XQUERY as I did. Why is that?
              • 4. Re: counting specific nodes
                odie_63
                IMO, the explain plan looks "cleaner" with the COUNT done in SQL in the first place (no inline view).

                The CBO rewrites both queries as follows :

                XQuery count :
                select x.the_count 
                from binxml_gtt
                   , xmltable(xmlnamespaces(default 'http://path_1.xsd')
                     , 'count(/Envelope/body/Data/TransactionUserData)' 
                       passing object_value 
                       columns the_count number path '.'
                     ) x;
                to
                SELECT CAST(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL(VALUE(KOKBF$0),0,0,54525952,0),50,1,2) AS number ) "THE_COUNT" 
                FROM "DEV"."BINXML_GTT" "BINXML_GTT"
                   , LATERAL( 
                     (
                       SELECT SYS_XQ_PKSQL2XML(COUNT("P"."C_00$"),2,33,0) "KOKBC$" 
                       FROM XPATHTABLE(
                            '/oraxq_defpfx:Envelope/oraxq_defpfx:body/oraxq_defpfx:Data/oraxq_defpfx:TransactionUserData' 
                            PASSING SYS_MAKEXML(0,"BINXML_GTT"."XMLDATA")
                            COLUMNS "C_00$" CHAR PATH '.'
                            )  "P"
                     )
                     ) "KOKBF$0"
                SQL COUNT :
                select count(*) the_count
                from binxml_gtt t
                   , xmltable(
                       xmlnamespaces(default 'http://path_1.xsd')
                     , '/Envelope/body/Data/TransactionUserData'
                       passing t.object_value
                     ) x ;
                to
                SELECT COUNT(*) "THE_COUNT" 
                FROM "DEV"."BINXML_GTT" "T"
                    ,XPATHTABLE(
                     '/oraxq_defpfx:Envelope/oraxq_defpfx:body/oraxq_defpfx:Data/oraxq_defpfx:TransactionUserData' 
                     PASSING SYS_MAKEXML(0,"T"."XMLDATA") 
                     COLUMNS "C_00$" XMLTYPE PATH '.'
                     )  "P"
                • 5. Re: counting specific nodes
                  Etbin
                  Thank you for pointing at another way of doing things - using XQuery directly - worth to be considered. I still have a lot to learn for sure.

                  Regards

                  Etbin

                  Edited by: Etbin on 7.1.2013 10:37
                  select column_value the_count
                    from clob_gtt c,
                         xmltable(xmlnamespaces(default 'http://path_1.xsd')
                                  'count(/Envelope/body/Data/TransactionUserData)'
                                  passing xml_type(clob_column)
                                 )
                  works with <tt>xmlnamespaces( ... )</tt> added
                  without <tt>xmlnamespaces( ... )</tt> zero is returned
                  • 6. Re: counting specific nodes
                    Etbin
                    Thanks.
                    So namespaces are not treated the same as attributes. I wasn't able to find an example and not having to deal with xml daily not enough knowledge to think about that myself.
                    The gtt of binary xmltype was planned as the next step after making it work (I used that approach some time ago as suggested by this forum).
                    The requirement is that a data file previously delievered as a fixed length text file has to be in xml format from now on (deadline end of january).
                    It was pretty straightforward to build the xml, now the problem is some crosschecking is applied on the xml delievered and the other side refuses it when they find inconsistencies but (as they say) they are not able to give us the paths to the nodes bearing the inconsistencies (and obviously we can not give them the real data for security reasons). Thus we must somehow guess the nodes to be worked on in order to eliminate the inconsistencies based on their messages like "nodes of this type must have all the fields filled" (the relative xsd schemas do not reveal the checks applied).
                    I must be able to count the <TransactionUserData> nodes to be sure no data has been left out. The query building the xml contains some conditions. Running the xml building query using the negation of the original conditions must not contain <TransactionUserData> nodes - that's the requirement. I can find out that using regexp_count or even instr on the clob but dealing with xml using xml seems most appropriate (it's something to be learned after all)
                    This is the main reason I (still as more familiar with) use clobs so I can easily copy/paste or export to the file to be delievered as for now the first goal is to make the xml file acceptable.

                    Regards

                    Etbin

                    Edited by: Etbin on 7.1.2013 10:42
                    select count(*) the_count
                      from clob_gtt c,
                           xmltable(xmlnamespaces(default 'http://path_1.xsd')
                                    '/Envelope/body/Data/TransactionUserData'
                                    passing xml_type(clob_column)
                                   )
                    • 7. Re: counting specific nodes
                      Etbin
                      Thanks both, Marc & Stew