3 Replies Latest reply: Apr 1, 2014 6:29 AM by odie_63 RSS

    how to extract a list of tokens from an attribute?

    TPD-Opitz

      Hello.

       

      In my 11.2.0.3 datanbase I get an XML input following this XSD:

      <element name="root">
        <attribute name="name" type="token"/>
        <attribute name="tokenList" use="optional">
          <simpleType>
            <list itemType="token" />
          </simpleType>
        </attribute>
      </element>
      

       

      So the input looks like this:

      <root name="thaName" tokenList="token1 token2 token3"/>
      

       

      how do I extract the tokens in tokenList to single rows (eg. in a var array or alike)?

       

      procedure extract_token_list(input XmlTyp ) is
        type tokenlist_t is table of varchar2;
        my_tokenlist tokenlist_t;
      begin
        -- what tor write here to fill my_tokenlist?
       for i in my_tokenlist.first ..  my_tokenlist.last loop
          dbma_output.put_line(my_tokenlist(i));
       end loop;
      end;
      

       

      bye

      TPD

        • 1. Re: how to extract a list of tokens from an attribute?
          odie_63

          SQL> select *

            2  from xmltable(

            3       'let $tokens := /root/@tokenList

            4        return if (contains($tokens, " "))

            5               then ora:tokenize($tokens, " ")

            6               else $tokens'

            7       passing xmlparse(document '<root name="thaName" tokenList="token1 token2 token3"/>')

            8       columns token varchar2(30) path '.'

            9      ) ;

           

          TOKEN

          ------------------------------

          token1

          token2

          token3

           

           

          The if-then-else stuff is there to ensure there's at least one occurrence of the token separator, otherwise ora:tokenize errors out with "ORA-19176: FORX0003: regular expression matches zero-length string".

          If you know the list will always contains at least two items, then you can remove it and use a straightforward ora:tokenize call.

          • 2. Re: how to extract a list of tokens from an attribute?
            TPD-Opitz

            Thanks!

             

            but a quick check in Toad replacing the literal XML string with a bind variable did not work.

            select *

                from xmltable(

                     'let $tokens := /root/@tokenList

                      return if (contains($tokens, " "))

                             then ora:tokenize($tokens, " ")

                             else $tokens'

                     passing xmlparse(document :xmlstring)

                     columns token varchar2(30) path '.'

                    ) ;

             

            returned no rows when putting the xml in the bind..

            how do I solve this?

             

            bye

            TPD

            • 3. Re: how to extract a list of tokens from an attribute?
              odie_63

              A TOAD issue most likely. I don't use it so I cannot reproduce what you say.

              Try in SQL*Plus.

               

              SQL> var xmlstring varchar2(4000)

              SQL> exec :xmlstring := '<root name="thaName" tokenList="token1 token2 token3"/>'

               

              PL/SQL procedure successfully completed.

               

              SQL>

              SQL> select *

                2  from xmltable(

                3       'let $tokens := /root/@tokenList

                4        return if (contains($tokens, " "))

                5               then ora:tokenize($tokens, " ")

                6               else $tokens'

                7       passing xmlparse(document :xmlstring)

                8       columns token varchar2(30) path '.'

                9      ) ;

               

              TOKEN

              ------------------------------

              token1

              token2

              token3