http://qs1969.pair.com?node_id=1153003


in reply to Re: Parsing Oracle PIVOT XML data
in thread Parsing Oracle PIVOT XML data

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.