Parsing Oracle PIVOT XML data

[tweetiepooh]

Hi fellow monks

I am trying to execute the below query

select * from (
select f1,f2,t from table where date > sysdate - 2
)
pivot xml (
count(t) as al, sum(t) as ev
for f2 in (any)
)

using DBI. The query in SQLPlus runs fine and I get no errors in Perl. f1 returns fine but the xml portion seems to be an empty array. I am using fetchall_hashref.SQLPlus output

f1
--
f2_xml
--------------------------------------------
99999
<PivotSet><item><column name = "F2">0</column><column name = "AL">12</
+c
olumn><column name = "EV">169</column></item></PivotSet>

Using Data::Dumper I see data like

varnn = {
    'f1' = '99999'.
    'f2_xml' = []
    };

It's how I get to f2_xml I need a little help with. Thanks

[SimonPratt]

Where's your Perl code?

[tweetiepooh]

The Perl side of this is pretty simple

# connect to the database and set date format
# for these reports dates are likey truncated somehome
my $dbh = connectdb();
$dbh->do("alter session set nls_date_format='dd Month yyyy hh24:mi:ss'
+");

# uncomment the following for long running reports
$dbh->do("alter session disable parallel query");
$dbh->do(qq(alter session set "_b_tree_bitmap_plans"=false));
$dbh->do("alter session set db_file_multiblock_read_count=2048");

# compile the SQL
my $sth = $dbh->prepare(SQL);

# run the query
$sth->execute();

# retrieve the names of the columns returned and write header line
my @names = @{$sth->{NAME}};

# create a hash based on the first column
my %data = %{$sth->fetchall_hashref($names[0])};
print Dumper(%data);

What I'd essentially expect is 2 strings, one the key and the second the XML. I'd then parse the XML to get my columns and fill the data in. The intention is to have this as a simple template so anyone can copy the file, edit the SQL (just changing limits, and data items) and the report would generate what's needed. I already have this for basic list reports.

The issue seems that somewhere in the data line the XML shown as a string in SQLPlus is getting converted somehow.

[poj]

A work around might be to use a temporary table

my $sql = '
insert into TMP_XML value ( 
  select * from (
    select f1,f2,t from table
    where date > sysdate - 2
  )
  pivot xml (
    count(t) as al, sum(t) as ev
    for f2 in (any)
  ) 
)';
$dbh->do($sql);

my $sth = $dbh->prepare('SELECT * FROM TMP_XML');
$sth->execute();

poj

[Anonymous Monk]

select f1, xmltype.getclobval(f2_xml) as f2_xml from ( select * from ( select f1,f2,t from table where date > sysdate - 2 ) pivot xml ( count(t) as al, sum(t) as ev for f2 in (any) ) )