4 Replies Latest reply: Mar 6, 2008 10:13 AM by Zabo RSS

    Position

    Zabo
      Hello,

      In the following exemple, is it possible to get the position ?

      select extractvalue(value(t),'/a')
      from table(xmlsequence(Extract(xmltype('<row><a>A</a><a>B</a><a>C</a></row>'),'/row/a'))) t

      So that I have the result
      A 1
      B 2
      C 3

      Message was edited by:
      Zabo
        • 1. Re: Position
          mdrake-Oracle
          SQL> select rownum, value
            2    from (
            3           select extractvalue(value(t),'/a') value
            4           from table(xmlsequence(Extract(xmltype('<row><a>A</a><a>B</a><a>C</a></row>'),'/row/a'))) t
            5         )
            6
          SQL> /
          
              ROWNUM VALUE
          ---------- ----------
                   1 A
                   2 B
                   3 C
          
          SQL>
          • 2. Re: Position
            mdrake-Oracle
            SQL>          select rownum, extractvalue(value(t),'/a') value
              2           from table(xmlsequence(Extract(xmltype('<row><a>A</a><a>B</a><a>C</a></row>'),'/row/a'))) t
              3  /
            
                ROWNUM VALUE
            ---------- ----------
                     1 A
                     2 B
                     3 C
            
            SQL>
            In 10gr2 and later XMLTable (which is preferred to talbe(xmlsequence) supports for the FOR ORDINALITY clause for enumerating the rows.
            SQL> select POSITION, VALUE
              2    from XMLTable
              3         (
              4           '/row/a'
              5           passing xmltype('<row><a>A</a><a>B</a><a>C</a></row>')
              6           COLUMNS
              7           POSITION FOR ORDINALITY ,
              8           value VARCHAR2(20) path '.'
              9         )
             10  /
            
              POSITION VALUE
            ---------- ----------
                     1 A
                     2 B
                     3 C
            
            SQL>
            Message was edited by:
            mdrake
            • 3. Re: Position
              AntsHindpere
              Hi,
              if your xml is <row>ABC</row>
              not  <row><a>A</a><a>B</a><a>C</a></row>
              then try this
              
              SQL> SELECT s.*
                2  FROM XMLTable
                3             (
                4               'for $i in /row return
                5                   (for $j in (1 to fn:string-length($i))
                6                        return <aa><a>{$j}</a><b>{fn:substring($i,$j,1)}</b></aa>)'
                7            PASSING XMLType('<row>ABC</row>')
                8            COLUMNS pos NUMBER PATH '/aa/a'
                9                   ,val VARCHAR2(1) path '/aa/b'
               10             ) s;
              
                     POS V
              ---------- -
                       1 A
                       2 B
                       3 C
              • 4. Re: Position
                Zabo
                Thanks