XMLTABLE vs. External XSLT preprocessor

September 16, 2013 2 comments

We all know – I hope ;) – about XMLTABLE function to shred an XML document into relational rows and columns. In this article, I will describe an alternative approach to achieve the same result with an external table and the preprocessor feature.
And in case you wonder, no, it’s not one of this attempt to implement an XML parser using SQL*Loader syntax, something we can see from time to time as an answer to “How can I load my XML file using SQL*Loader?”.
This method relies on the external table preprocessor to transform the input XML file into CSV format and pipe the result to the loader.

 

Read more…

New 12c XMLTABLE’s “RETURNING SEQUENCE BY REF” clause

September 9, 2013 Leave a comment

This is a short article about the new 12c RETURNING SEQUENCE BY REF feature available with XMLTable function.
This clause allows us to access the complete tree of nodes produced by the main XQuery expression. In particular, we can now use reverse axes in the COLUMNS PATH expression.
Below is an example using this method, compared with prior releases’ techniques to achieve the same result (all queries tested on 12.1.0.1).

 

Read more…

PL/SQL RPN calculator

February 3, 2013 3 comments

Some non-XML stuff for a change :)
This is something I’ve developed recently to evaluate stored expressions (formulas) using variables.
The “calculator” is written in PL/SQL and implements an RPN evaluation technique, as well as a method to convert infix expressions to RPN using the shunting-yard algorithm.
The evaluation function does not involve any dynamic SQL.

Program and additional objects available here : rpn_util.zip

 

Read more…

XML Flattening revisited : Java-based pipelined function

November 18, 2012 1 comment

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 :
Read more…

Création d’une étiquette DPE depuis Oracle

September 2, 2012 Leave a comment

As the title announces, exceptionally, this article is written in French. It describes how to generate the french version of the Energy Performance Certificate (EPC) chart from Oracle database, both as a BLOB image (using Java) and SVG file (using SQL/XML functions).
Translation available upon request ;)

En France, selon la règlementation en vigueur, le Diagnostic de Performance Énergétique (DPE) inclut une représentation visuelle de la performance énergétique d’un bâtiment sous la forme d’une étiquette marquant sa consommation annuelle sur une échelle de valeurs.

J’ai retenu deux méthodes assez différentes pour créer cette représentation depuis Oracle :

  • Une procédure stockée Java générant une image (format PNG)
  • Une simple requête SQL utilisant les fonctions SQL/XML pour créer un fichier au format SVG

Read more…

Categories: Miscellaneous Tags: , , , , ,

How To : Access database tables from XSLT context

July 30, 2012 8 comments

In this post we’ll see how to access a database table (or view) in the context of an XSL transformation.

The key feature allowing that is the ability to query a table using a DBUri URI.
In SQL or PL/SQL, DBUris are managed via the DBUriType object and its methods (see DBUris: Pointers to Database Data), and over HTTP protocol via the DBUriServlet.
The URI address is an XPath-based string, just like the oradb scheme available in XQuery.

In XSLT, we can pass a DBUri URI to the document() function in order to retrieve data in XML format. There are however some restrictions.

Read more…

Categories: HowTo, XSLT Tags: , , ,

How To : Update XML nodes with values from the same document

June 19, 2012 Leave a comment

This post was inspired by a recent thread on OTN where I suggested a few methods to update one or multiple nodes with values from the same document.

Let’s consider this simple structure :

<workbook>
 <worksheet sheetName="MySheet" sheetId="1"/>
 <worksheet sheetName="MySheet" sheetId="2"/>
 <worksheet sheetName="MySheet" sheetId="3"/>
</workbook>

and that we want to update the sheetName of each worksheet using the value in sheetId.

Read more…