Apex, XMLDB and connect by.....

I've been doing a lot of work recently with xmldb and have been frustrated by the lack of a GUI that did exactly what i wanted - i.e. give me a treeview of the structure and allow me to easily see the properties of each document (acl,created date etc) as well as the actual content of those documents (in our case just xml).

Cloud control can do all of the above but it requires a lot of clicks and it's not all located just in one simple to use screen.

SQL Developer seems to allow you to browse xml schemas but not the actual xmldb repo itself

There may be other tools that can do this - but I couldn't find one. So i decided to try and do my own in Apex........

Now I'm by no means an Apex expert but i thought i knew enough to produce something basic - here is the story so far.....

The first bit to tackle was to get a tree view of the xmldb folders and files - Apex has a nice built in tree view display page but I needed to build an appropriate query with connect by to be able to use that.

Apex helpfully gives you a sample code block of the format it wants - which is shown below

select case when connect_by_isleaf = 1 then 0 when level = 1 then 1 else -1 end as status,
       level,
       ENAME as title
       NULL  as icon,
       EMPNO as value,
       ENAME as tooltip,
       NULL  as link
from EMP
start with MGR is null
connect by prior EMPNO = MGR
order siblings by ENAME

So some of those attributes are used internally by the tree viewer code others are used to define icons,tooltips and links

So i just had to build something similar for the xmldb folders - i couldn;t find anyone who had done this when googling so i had to write my own - this was a little fiddly and uses a couple of trciks - but the resulting end code is shown below

with xmldata as
 (select '/' as any_path, null as parentpath
    from dual
  union all
  select any_path,
         nvl(substr(any_path, 1, (instr(any_path, '/', -1) - 1)), '/') as parentpath
    from resource_view)
SELECT case
         when connect_by_isleaf = 1 then
          0
         when level = 1 then
          1
         else
          -1
       end as status,
       level,
       any_path as title,
       null as icon,
       parentpath as value,
       null as tooltip,
       'javascript:$s(''HIDDEN_XMLRES'',''' || any_path || ''')' As link
  FROM xmldata
 start with any_path = '/'
CONNECT BY PRIOR any_path = parentpath;

Some points to help explain this:

1) The with clause made it easier to code

2) The initial select in the with clause - the first part of the union - is necessary as this data does not exist in xmldb - the initial 'root' entry for connect by.

3) I use substr and instr to manipulate the path to get me the parent path

4) The column with an alias link will be used later as part of the 'app' - clicking the link it creates will populate a hidden page item HIDDEN_XMLRES with the value of the currently clicked item.

Running this in plsql developer shows the following output

So now i have the treeview query i need to make an Apex page to display it.

Here is a quick whizz through the screens to just create an Apex shell - then i'll come back and explain the tree view bit.

So that's the shell created now lets add the tree view magic.....most of this speaks for itself so i won't add any extended commentary. I make use of the EMP table just to allow me to quickly go through the screens and create a dummy tree which i later change - if you created the schema through the 'create workspace' wizard then this table will exist already - if not you can just create the EMP table based on the normal definition which can be found from many different sources.

So now we have a dummy tree created we just have to update the SQL to what we actually want it to run - which we do as follows:

Now run the page

And there we have it a working tree! Performance is OK (a few seconds to load the screen) - but very dependent i guess on the size and depth of the tree being built.....

Now i just need to build the other bits to display what i want it to - so far this is proving very difficult - the onchange dynamic actions are not working and i think it's because the treeview runs some code that wipes them out......

Will post some more details as the screens develop - so far so good though - and surprisingly easy.