Posts Tagged ‘XQuery’

How To : Access Oracle Sequence from XQuery

December 11, 2016 1 comment

In this new post, we’ll see how to access an Oracle sequence from an XQuery expression.
I already wrote about a similar topic a few years ago, in How To : Access database tables from XSLT context.
Pretty much the same concepts presented back then for XSLT also apply here with XQuery.

The solution relies on the ability to query relational or object view data via the oradb URI scheme.
By wrapping the sequence call in a function and a view, we can fetch the sequence value using fn:collection() function.

In the following developments, I’ll distinguish two cases :

  • fetching sequence values from a statically declared sequence
  • fetching values from different sequences dynamically

Preliminary setup

Let’s first create a sequence :

create sequence my_seq;

And the function.
It takes the sequence name as a parameter and use native dynamic SQL to retrieve the value :

create or replace function get_seq_value (
  p_sequence_name in varchar2
return number
  l_result  number;
  execute immediate 'SELECT '||p_sequence_name||'.NEXTVAL FROM DUAL' into l_result;
  return l_result;


Read more…


XML Namespaces 101

June 7, 2016 1 comment

Back to basics with a focus on XML namespaces.
A lot of people still struggle to use and reference namespaces correctly in XML-related functions, and most often try random combinations until it works correctly.
Hopefully, this post will clear a few things up :)


1. What is a namespace?

A namespace is not some exotic object but just one out of the two parts that form a node name.
In the XML Object Model, the node name of an element or attribute is composed of :

  • a namespace URI, i.e. the namespace name
  • a local name

If the namespace uri is absent (null), the node is said to be in no namespace.


2. Default namespaces and prefixes

In an XML document or fragment, a namespace can be defined in two ways :

  • namespace binding (prefix) declaration : xmlns:prefix="my-namespace-1"

    The scope is the element where it appears and all its descendant elements and attributes, unless it is redefined using another declaration (e.g. xmlns:prefix="my-namespace-2").
    A binding declaration applies to all qualified (i.e. prefixed) in-scope elements and attributes.

  • default namespace declaration : xmlns="my-default-ns"

    The scope is the element where it appears and all its descendants, unless it is redefined using another declaration (e.g. xmlns="new-default-ns") or undefined using an empty declaration (xmlns="").
    A default namespace declaration applies to all unqualified in-scope elements, but it does not apply to attributes.

Let’s consider a simple example :
Read more…

How To : Read and Write Office 2003 Excel XML Files

February 12, 2012 8 comments

Office Open XML (OOX) has become the default format with the release of Office 2007, but back in the 2003’s days, Microsoft had already developed a format to store Excel workbooks as XML.
A comprehensive overview is available here :
Dive into SpreadsheetML (Part 1 of 2)
Dive into SpreadsheetML (Part 2 of 2)

Contrary to OOX where data and metadata are stored in a multipart archive, an Excel workbook file in SpreadsheetML 2003 format consists in a single XML instance, and therefore easily managed using built-in Oracle XML functions and XML DB features.
In this article, I’ll focus on how to create and read such files with the help of SQL/XML functions, XSLT and XQuery.
Read more…

Categories: HowTo Tags: , , ,

XML DB Events : Reading an Open Office XML document (.xlsx)

January 28, 2012 3 comments

This post is actually an offshoot of a recent article by Marco Gralike on his blog. Marco describes how to automatically read an Open Office XML (OOX) document, specifically docx format, and store the data in a database table for subsequent queries.
One of the key features is the definition of XML DB Events handlers that will “unzip” the archive for us whenever we drop it in the repository.

The funny thing is that, in the meantime, I had already begun to work on the xlsx version, including my own unzip utility. Marco’s example is using Anton Scheffer’s great zip/unzip package which, although based on the same extraction technique, is much more complete than mine.

Installation script and package for my demo are available here :
I’ll explain some of the steps below in detail.
Read more…

How To : Flatten out an XML Hierarchical Structure

December 13, 2011 7 comments

Besides the usual requirement to present XML data in a relational way, sometimes we may also need to flatten the whole XML structure and build parent-child relationships from it.
For instance, converting this :



---------- --------------- ---------------- ---------------
         1 root
         2 item            ABC                            1
         3 item                                           1
         4 item                                           1
         5 subitem         XYZ                            4

Read more…

Internal handling of XQuery functions

November 29, 2011 2 comments

… or more precisely user-defined recursive XQuery functions.
I recently stumble upon this while checking the explain plan of a query :
Read more…

Categories: Miscellaneous, XQuery Tags:

How To : Sort delimited values in a string using XQuery

November 28, 2011 2 comments

This follows a thread on OTN about a month ago. The question was : how to sort a string in ascending order?
I suggested an XQuery approach that turned out to be a lot more efficient than the traditional TABLE(SUBQUERY-FETCH) pattern.
Read more…

Categories: HowTo, XQuery Tags: , ,