XML Flattening revisited : Java-based pipelined function

As a follow-up to How To : Flatten out an XML Hierarchical Structure, here’s a fourth approach using a pipelined function built over the Java InfosetReader interface.
The function only works on an XMLType column/table stored as binary XML. Since we directly decode the binary stream and pipe the rows to the SQL engine, this method is faster, much more scalable and less memory-intensive.

 

1. Set up

The ODCI set up is based on : Pipelined Table Functions Example: Java Implementation from the Data Cartridge Developer’s Guide.

We start by creating an object type and its collection. These are the structures that will be filled by the Java program at runtime and returned to the SQL engine via the pipelined function :

CREATE TYPE XMLEdgeTableRow AS OBJECT (
  node_id        integer
, node_name      varchar2(2000)
, node_type      varchar2(30)
, parent_node_id integer
, node_value     varchar2(4000)
, namespace_uri  varchar2(2000)
);

CREATE TYPE XMLEdgeTable AS TABLE OF XMLEdgeTableRow;

 

then the implementation type :