How To : using OUTER JOIN with XMLTABLE or XQuery

May 20, 2016 Leave a comment

Here is a short reminder post about how to use or implement outer join in XMLTABLE or XQuery.

The typical scenario for which we would need to outer join is the flattening of a master-detail hierarchical structure with some logical records missing the detail part.
For instance, given this input XML document :

  <person id="1">
      <phone type="mobile">111-11-2222-22</phone>
      <phone type="home">555-11-2222-22</phone>
  <person id="2">
      <phone type="mobile">111-11-2222-33</phone>
  <person id="3">

We’d like to get the following result set :

---------- ----------- ---------- ---------------
         1 John        mobile     111-11-2222-22
         1 John        home       555-11-2222-22
         2 Jane        mobile     111-11-2222-33
         3 Sam                    

Read more…

Categories: HowTo, XML DB, XQuery

Simple CSV parsing using XMLTABLE or JSON_TABLE

April 22, 2016 Leave a comment

Oracle does not currently provide any built-in functionality to parse a flat file stored in the database as CLOB (or BLOB), like the External Table facility for external files.
Mike Kutz submitted the idea a couple of years ago : parse CLOBS/BLOBS using “external table” capability.

Parsing a simple CSV content, with no quoted field, is however possible with minimum effort using XMLTABLE or JSON_TABLE functions.
Both approaches are based on a little preprocessing to convert the data to the required format and pass it to the corresponding function.

The last part of this post will focus on the limitations.

Read more…

Categories: JSON, SQL, XML DB Tags: , ,

How To : Load XML from File with Encoding Detection

April 10, 2016 1 comment

This post was inspired by this recent OTN thread : LPX-00200: could not convert from encoding UTF-8 to UCS2, where it was asked if it were possible to load an XML file without actually knowing its character encoding.

We can already build an XMLType instance from a BFILE, using the corresponding constructor :

constructor function XMLType(
  xmlData    IN bfile
, csid       IN number
, schema     IN varchar2 := NULL
, validated  IN number   := 0
, wellformed IN number   := 0
return self as result 

However, the character encoding must be known upfront and passed to the constructor (csid argument).
For example :

SQL> select XMLSerialize(document
  2           xmltype(
  3             bfilename('TMP_DIR','test_utf-8_bom.xml')
  4           , nls_charset_id('AL32UTF8')
  5           )
  6         ) as xmldoc
  7  from dual ;
<?xml version="1.0" encoding="UTF-8"?>

Following is a little helper function that provides basic character encoding detection for XML files.
It should work for most 8-bit encodings (except EBCDIC), as well as UTF-16 (little and big-endian) and of course UTF-8.
The function uses this algorithm to detect the encoding :

  • If a byte order mark (BOM) is present then UTF-8, UTF-16LE or UTF-16BE is assumed, respectively :
    • 0xEFBBBF = UTF-8
    • 0xFFFE = UTF-16LE
    • 0xFEFF = UTF-16BE
  • If no BOM is found then it looks for an XML prolog :
    • If no prolog or encoding declaration is found then a default encoding is assumed.
    • Else, the specified encoding is used.

Read more…

Yet Another XML Flattening Technique

March 27, 2016 4 comments

As a follow-up to my previous post introducing XMLNest function, here’s now its “inverse” (to borrow from maths terminology) : XMLFlattenDOM, a PL/SQL DOM-based pipelined function.

We’ll see in the last part how this approach compares to the others described earlier :


Read more…

Hierarchical XML Aggregation : the XMLNest function

March 10, 2016 Leave a comment

I’ve been away a few months but here’s a new post :)

I’ll be presenting here another approach for generating a hierarchical XML structure, besides the three ones already explained in a previous post, which were, as a reminder :

  • DBMS_XMLGEN.newContextFromHierarchy()
  • XSLT
  • Recursive XQuery function

This new approach is based on a user-defined aggregate function – called XMLNest – and therefore requires a few additional objects to make it work.


Read more…

New JSON Path Expression Predicates

September 20, 2015 1 comment

With the release of ORDS 3.0 in May this year, Oracle also included a new set of API called SODA dedicated to schemaless application development based on JSON. That enables us to use the Oracle database as a NoSQL document store.

The purpose of this post is to focus on the filter specification – aka query-by-example (QBE) – used to select JSON documents out of a collection with SODA for REST (and SODA for Java).

As explained in the documentation, a QBE may use a set of relational and boolean operators to define complex filters. Those operators include : $in, $exists, $regex, $startsWith, $and, $or etc.

Now comes the interesting part…

Each collection is actually backed up by a database table storing the JSON documents. When a query on a collection is issued via the SODA API, and specifies a QBE, Oracle translates it to a SQL query with a JSON_EXISTS predicate.
In order to handle the different kind of operators used in a QBE, the JSON Path expression syntax has been extended to support predicates.

Although it is part of the ORDS 3.0 suite, we don’t actually need to install ORDS in the database in order to use the new syntax, the prerequisite patch is sufficient :

I’ll present below an overview of this extended syntax, with some examples.
(Note that at the time I’m writting this, predicate support is still not officially documented)


Read more…

Categories: JSON, SQL Tags: , ,

XQuery 3.0 : We’re getting closer…

August 27, 2015 Leave a comment

Stumbled upon this while developing a user-defined operator (for a future work related to JSON) :

SQL> select operator_name, function_name, return_type
  2  from dba_opbindings
  3  where operator_name = 'XQWINDOWSEQUENCE';
OPERATOR_NAME       FUNCTION_NAME                         RETURN_TYPE
------------------- ------------------------------------- ------------------

That is on
Yes, seems like Oracle has already been implementing some XQuery 3.0 features, namely Windowing functionalities.

This is the corresponding function definition (in SYS schema) :

create or replace function XQWindowSequenceFromXMLType(doc in sys.XMLType,
        flag in number, startexpr in varchar2, endexpr in varchar2,
        curItem in sys.XMLType, prevItem in sys.XMLType,
        nextItem in sys.XMLType, position in sys.XMLType,
        ecurItem in sys.XMLType, eprevItem in sys.XMLType,
        enextItem in sys.XMLType, eposition in sys.XMLType
        return sys.XMLSequenceType authid current_user
 pipelined using XQWindowSeq_Imp_t;

Out of curiosity, I also tried an XQuery with a window clause but as expected it has not made it into the core yet, or has it? ;)

SQL> select *
  2  from xmltable(
  3  'for tumbling window $w in (2, 4, 6, 8, 10, 12, 14)
  4      start at $s when fn:true()
  5      only end at $e when $e - $s eq 2
  6  return <window>{ $w }</window>'
  7  );
select *
ERROR at line 1:
ORA-19112: error raised during evaluation: XQuery Window Seq is not supported

Categories: XML DB, XQuery Tags: ,