Michael McLaughlin had written last week some nice small blog posts on XML DB. I couldn’t resist to see if I could make this one easier… 😉
The package DBMS_EPG is sole based on the xdbconfig.xml file, which is the configuration file for the XML DB Protocol Server, which is not, by the way, an Apache Server configured inside the database, but a custom build protocol server hook-in into the Oracle listener, that passes its data to the Shared Server SQL*Net Architecture.
So I made some attempts on my 11gR2 database, without using the nowadays “extract” deprecated functions… After fiddling around with the XMLTABLE function, using DBMS_XDB.CFG_GET() to get the content of the xdbconfig.xml XML document (its result is an xmltype datatype), the following simplified code that results the DAD’s in a varchar2(15) list.
SQL> SELECT u.dad as "PL/SQL DAD List"
2 FROM XMLTable(XMLNAMESPACES (
3 default 'http://xmlns.oracle.com/xdb/xdbconfig.xsd'
4 ),
5 '//servlet-list/*[servlet-language="PL/SQL"]'
6 PASSING DBMS_XDB.CFG_GET()
7 COLUMNS DAD varchar2(15) PATH '/servlet/servlet-name/text()'
8 ) u
9 ;
PL/SQL DAD List
---------------
APEX
1 row selected.
SQL> EXECUTE dbms_epg.create_dad('MARCO','/XFiles/*');
PL/SQL procedure successfully completed.
SQL> SELECT u.dad as "PL/SQL DAD List"
2 FROM XMLTable(XMLNAMESPACES (
3 default 'http://xmlns.oracle.com/xdb/xdbconfig.xsd'
4 ),
5 '//servlet-list/*[servlet-language="PL/SQL"]'
6 PASSING DBMS_XDB.CFG_GET()
7 COLUMNS DAD varchar2(15) PATH '/servlet/servlet-name/text()'
8 ) u
9 ;
PL/SQL DAD List
---------------
APEX
MARCO
2 rows selected.
In principle you should always use, as much as possible, fully defined XPATH path. Don’t use, avoid, the // path traversal. The XPath engine doesn’t have to address the whole XML Document, if you are precise, but only has to traverse the specified path PLUS you can avoid some DOM in memory overhead processing. Another small item here is using a namespace reference. There is some overhead addressing this but Oracle optimizes this by parsing an XML Schema only once, puts its in memory and uses it afterward to optimize all XML documents that reference it by building XOB (XML OBjects) reducing memory overhead and this will result in being more effective than DOM XML handling.
SQL> SELECT u.dad as "PL/SQL DAD List"
2 FROM XMLTable(XMLNAMESPACES ( default 'http://xmlns.oracle.com/xdb/xdbconfig.xsd' ),
3 '/xdbconfig/sysconfig/protocolconfig/httpconfig/webappconfig/servletconfig/servlet-list/servlet[servlet-language="PL/SQL"]'
4 PASSING DBMS_XDB.CFG_GET()
5 COLUMNS DAD varchar2(15) PATH '/servlet/servlet-name/text()'
6 ) u
7 ;
PL/SQL DAD List
---------------
APEX
MARCO
2 rows selected.
Disadvantage, in this case, is that the syntax becomes a bit more tedious to read…
HTH
