<PivotSet>
<item>
<column name = "SERIES_CODE">FY1</column>
<column name = "VAL">3.505331702434</column>
</item>
<item>
<column name = "SERIES_CODE">FY2</column>
<column name = "VAL">3.357474212727</column>
</item>
</PivotSet>

FY1       FY2
3.5053    3.3574

SQL> with data as (
  2    select xmltype('<PivotSet>
  3  <item>
  4  <column name = "SERIES_CODE">FY1</column>
  5  <column name = "VAL">3.505331702434</column>
  6  </item>
  7  <item>
  8  <column name = "SERIES_CODE">FY2</column>
  9  <column name = "VAL">3.357474212727</column>
 10  </item>
 11  </PivotSet>') data from dual
 12    ),
 13    extracted as (
 14      select rownb-1 rownb, name, val
 15      from data,
 16           xmltable ('/PivotSet/item/column' passing data
 17                     columns
 18                       rownb for ordinality,
 19                       name varchar2(50) path '@name',
 20                       val  varchar2(50) path '.'
 21                    )
 22    )
 23  select max(decode(trunc(rownb/2),0,val)) item1,
 24         max(decode(trunc(rownb/2),1,val)) item2
 25  from extracted
 26  group by mod(rownb,2)
 27  order by mod(rownb,2)
 28  /
ITEM1                                              ITEM2
-------------------------------------------------- --------------------------------------------------
FY1                                                FY2
3.505331702434                                     3.357474212727

2 rows selected.

SQL> drop table t;

Table dropped.

SQL> create table t (data)
  2  as select xmltype('<PivotSet>
  3  <item>
  4  <column name = "SERIES_CODE">FY1</column>
  5  <column name = "VAL">3.505331702434</column>
  6  </item>
  7  <item>
  8  <column name = "SERIES_CODE">FY2</column>
  9  <column name = "VAL">3.357474212727</column>
 10  </item>
 11  </PivotSet>') data from dual;

Table created.

SQL> commit;

Commit complete.

SQL> create or replace procedure getItems (rc out sys_refcursor)
  2  is
  3    data    xmltype;
  4    nbitems pls_integer;
  5    sqltext varchar2(32765);
  6  begin
  7    select regexp_count(data,'<item>') into nbitems from t;
  8    sqltext := q'[with
  9    extracted as (
 10      select rownb-1 rownb, name, val
 11      from t,
 12           xmltable ('/PivotSet/item/column' passing data
 13                     columns
 14                       rownb for ordinality,
 15                       name varchar2(50) path '@name',
 16                       val  varchar2(50) path '.'
 17                    )
 18    )
 19  select ]';
 20    for i in 1..nbitems loop
 21      sqltext := sqltext || '
 22         max(decode(trunc(rownb/2),'||(i-1)||',val)) item'||i||',';
 23    end loop;
 24    sqltext := rtrim(sqltext,',') || '
 25  from extracted
 26  group by mod(rownb,2)
 27  order by mod(rownb,2)';
 28    open rc for sqltext;
 29  end;
 30  /

Procedure created.

SQL> var rc refcursor
SQL> exec getItems(:rc)

PL/SQL procedure successfully completed.

SQL> col item1 format a30
SQL> col item2 format a30
SQL> col item3 format a30
SQL> print rc
ITEM1                          ITEM2
------------------------------ ------------------------------
FY1                            FY2
3.505331702434                 3.357474212727

2 rows selected.

SQL> update t set data = xmltype('<PivotSet>
  2  <item>
  3  <column name = "SERIES_CODE">FY1</column>
  4  <column name = "VAL">3.505331702434</column>
  5  </item>
  6  <item>
  7  <column name = "SERIES_CODE">FY2</column>
  8  <column name = "VAL">3.357474212727</column>
  9  </item>
 10  <item>
 11  <column name = "SERIES_CODE">FY3</column>
 12  <column name = "VAL">3.1415926535897932384626</column>
 13  </item>
 14  </PivotSet>');

1 row updated.

SQL> commit;

Commit complete.

SQL> exec getItems(:rc)

PL/SQL procedure successfully completed.

SQL> print rc
ITEM1                          ITEM2                          ITEM3
------------------------------ ------------------------------ ------------------------------
FY1                            FY2                            FY3
3.505331702434                 3.357474212727                 3.1415926535897932384626

2 rows selected.