Archive

Archive for the ‘XQuery’ Category

How To : Flatten out a Recursive XML Document

January 17, 2017 Leave a comment

I blogged a few times about XML flattening in the past :

Those posts were about converting any XML structure and data into a schema-less model called “Edge”.
What I’ll be presenting here applies to XML documents having a recursive structure,
e.g.

<emp id="1">
  <emp id="2"/>
  <emp id="3">
    <emp id="4"/>
    <emp id="5"/>
  </emp>
</emp>

which could be converted to an adjency list model :

ID  PARENT_ID
--- ---------
  1
  2         1
  3         1
  4         3
  5         3

or, denormalized into level-based columns, like this :

ID_1 ID_2 ID_3
---- ---- ----
   1    2
   1    3    4
   1    3    5

 

Read more…

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
is
  l_result  number;
begin
  execute immediate 'SELECT '||p_sequence_name||'.NEXTVAL FROM DUAL' into l_result;
  return l_result;
end;
/

 

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 : using OUTER JOIN with XMLTABLE or XQuery

May 20, 2016 8 comments

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 in the detail part.
For instance, given this input XML document :

<root>
  <person id="1">
    <name>John</name>
    <details>
      <phone type="mobile">111-11-2222-22</phone>
      <phone type="home">555-11-2222-22</phone>
    </details>
  </person>
  <person id="2">
    <name>Jane</name>
    <details>
      <phone type="mobile">111-11-2222-33</phone>
    </details>
  </person>
  <person id="3">
    <name>Sam</name>
    <details/>
  </person>
</root>

We’d like to get the following result set :

 PERSON_ID PERSON_NAME PHONE_TYPE PHONE_NUMBER
---------- ----------- ---------- ---------------
         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

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
------------------- ------------------------------------- ------------------
XQWINDOWSEQUENCE    "SYS"."XQWINDOWSEQUENCEFROMXMLTYPE"   XMLSEQUENCETYPE 

That is on 11.2.0.4.
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: ,

How To : Parse repeating groups correlated by position as relational data

June 19, 2015 Leave a comment

Here’s a new post in the category “How to deal with that unfriendly design?”
Again, since the title is not very explicit, an example will surely explain it all :

<root>
  <ids>
    <id>7369</id>
    <id>7499</id>
    <id>7521</id>
  </ids>
  <names>
    <name>SMITH</name>
    <name>ALLEN</name>
    <name>WARD</name>
  </names>
  <jobs>
    <job>CLERK</job>
    <job>SALESMAN</job>
    <job>SALESMAN</job>
  </jobs>
</root>

This is the result we want out of that data :

   ID NAME    JOB
----- ------- ----------  
 7369 SMITH   CLERK
 7499 ALLEN   SALESMAN
 7521 WARD    SALESMAN

 

Read more…

Categories: HowTo, SQL, XQuery Tags: ,

How To : Parse sibling repeating groups without wrapper element

November 9, 2014 Leave a comment

Long time no post, so here’s a new (short) article.
Behind the cryptic title lies a situation like this :

<root>
  <id>1</id>
  <name>SMITH</name>
  <id>2</id>
  <name>WILSON</name>
  <id>3</id>
  <name>GRANT</name>
</root>

where each logical record {id, name} is not enclosed by a row tag.
This is the result we want out of that data :

ID    NAME
----- ---------
    1 SMITH
    2 WILSON
    3 GRANT

 

Read more…