Database Software

Hello,

Below query selects multiple rows of a node which is fine. But how to select all the columns of the path in the result? In the below example, I would like to select values of node "class", "lifedecision" and ,"policydecision".

WITH STAGE_XMLFILE_LOAD

     AS (SELECT 1 logfileid,

                XMLTYPE ('<autolog>

<decisions>

    <policydecisions>

      <policydecision key="1">

        <lifedecision key="1">

          <class name="A1">

            <frndcode><![CDATA[A]]></frndcode>

            <frndcode><![CDATA[B]]></frndcode>

            <frndcode><![CDATA[C]]></frndcode>

          </class>

        </lifedecision>

      </policydecision>

    </policydecisions>  

  </decisions>

</autolog>') xml

           FROM DUAL)

SELECT f.logfileid, frndcode

   FROM STAGE_XMLFILE_LOAD f,

        XMLTABLE (

           'for $i in /autolog/decisions/policydecisions/policydecision/lifedecision/class/frndcode return $i'

           PASSING f.xml

           COLUMNS  frndcode VARCHAR2 (100) PATH 'text()')

Result of the above query:

logfileid   FRNDCODE

1                  A

1                  B

1                  C

What modifications I need to make to get the result as shown below.

Also, I do not want to use one more XMLTABLE in the query. Is this possible? Thanks a lot for your help in advance.