August 2

Storing your Scripts in the XMLDB Repository

The underlying structure of the XMLDB repository is a blob, but why not…? The following code was once demonstrated by Mark Drake in May on the OTN XMLDB Forum. It is a very useful alternative way to store some of our code / scripts…

declare
  res boolean;
begin
  res := dbms_xdb.createResource('/public/q1.xqy','My Xquery');
  commit;
end;
/ 
select xmlquery(xdburitype('/public/q1.xqy').getClob() 
       passing OBJECT_VALUE 
       returning content) 
from   MY_XML_TABLE
/

Fill in “My XQuery” or else, with your code or statements. Secure it via XMLDB’s ACL security options and “voila” your own script repository, amongst others, maintainable via WebDAV.

CategoriesHowto Oracle SQL, XML/SQL, XPath, XQuery XMLDB

Tagsxmldb repository XQuery

Marco Gralike Written by:

7 Comments

  1. Robbie Vanhove
    August 5

    Hi Marco,

    I tried to implement this solution, but can not execute the xmlquery function like this. My xquery is saved in the ‘/public’ folder in the XML DB repository as you describe. But when I execute the xlmquery with xdburitype(‘/public/q1.xqy’).getClob(), I get an ORA-19102 – XQuery string literal expected – error. When I execute ‘select xdburitype(‘/public/q1.xqy’).getClob() from dual’, it works fine.

    Do you have any idea what can cause this problem?

    Kind regards,

    Robbie

  2. Robert
    August 26

    hi Marco,
    Sorry this question is not related to this post.

    I was reading XMLDB doc and came to this spot: [the spot]

    About using GLOBAL TEMPORARY TABLE under XMLDB…..

    The Schema-Based XMLType Tables under “structured storage” mode (xdb:defaultTable), this table can be GTT ?

    Thanks

  3. Marco Gralike
    August 26

    The funny thing is that I “noticed” this a month ago myself (although I read the manual several times…)

    Now I just checked it…

    SQL*Plus: Release 11.1.0.6.0 - Production 

Copyright (c) 1982, 2007, Oracle.  
All rights reserved.

Enter user-name: system@homework.nl

Connected to:

Oracle Database 11g Enterprise Edition 
Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining 
and Real Application Testing options

SQL> create global temporary table GTTX of xmltype;

Table created.

.

    I can imagine that you could use this form of storage for temporarily storage of intermediate results of XML data or such…

    Regarding the xdb annotation “xdb:defaultTable”, I would have to look further into this although I can imagine that this can be done via also using the annotation “xdb:tableProps”. Although text specified in this clause is appended to the generated create table statement (using dbms_xmlschema).

  4. Robert
    August 27

    sigh…OTN forums still down….this is ridiculous

  5. Robert
    August 28

    Thanks Marco,

    In my previous post, I was thinking about
    * setting “GENTABLES => FALSE” in “DBMS_XMLSCHEMA.registerSchema’
    * *manually* create the xdb:defaultTable as GTT.

    what do you think ?

    PS: Reason for using GTT is because I’m doing “Truncate Table” on the xdb:defaultTable to speed up the cleanup
    (per Marks suggestion: http://forums.oracle.com/forums/thread.jspa?messageID=1119858&#1119858)
    (per Mark and your suggestion: http://forums.oracle.com/forums/thread.jspa?messageID=2214058)

    This works like a charm except of course the DDL creates concurrency problem. šŸ™

  6. Marco Gralike
    August 28

    Manually is probably the only way to do a GTT

    I noticed though that deleting the folder resource is really fast while trying to get rid of the xml document “resources”

Comments are closed.