A colleague of mine asked about the several ways of saving XML content, I knew, from an Oracle database. Thinking about it, I came up with the following list, but to be honest, there are many more, if not only because it is not a XML specific topic. Anyway, I guess its a nice initial list and some methods you might not be so familiar with.
In all I wouldn’t save XML via a “XML method”. Using XMLType would imply XML (well formedness) parsing and if I could avoid it via saving it (compressed?) as CLOB (/BLOB), I would.
It will depend of the goal you like to achieve and the context within it should work, which option might work for you.
I can give you some guidelines though…
- Avoid unnecessary parsing using the XMLType datatype
- Don’t avoid XMLType altogether though
- You would be stupid to generate XML line for line and not making use of the XMLDB capabilities of your database
- (Don’t laugh) I have seen code building the whole content with UTL package statements – not maintainable, a debugging nightmare, etc.
- DO NOT CREATE pretty printed XML.
- Pretty printed XML is only useful for humans (debugging). Open the generated XML in a browser is a good alternative if you need to check it in a pretty printed form
- Use XMLSERIALIZEÂ (with “indent size=0” !) to serialize it to (C)LOB content without pretty print
- If you need to create XML content from relational data
- Use XMLELEMENT, XMLAGG, XMLFOREST, etc. (if you like performance) – I prefer this method because I think it is “clean” & maintainable.
- Use DBMS_XMLDOM (if you like performance)
- XQuery/XSLT – if you are into these languages…
- Do not use alternative (old) packages if there is not a good reason for it.
- Do NOT USE deprecated functionality (check the latest chapter on “new/deprecated features” in the XMLDB Developers Guide, at current 12.1.0.2)
- Compress (C/LOB) content before transferring it across the network (if possible)
- Follow (XML) standards / use the latest database version (if possible)
Overview
An overview of methods, I could think of currently:
- Saving XMLÂ content to disk
- Using UTL_FILE / DBMS_LOB (or alternative?) using PL/SQL
- Using DBMS_XMLDOM.writetofile()
- Using DBMS_XSLPROCESSOR.clob2file()
- Using an external table
- Making use of the XDB Repository functionality
- Using DBMS_XDB.createresource()
- Get the content via HTTP(s), FTP or WebDAV
- Using Native Database Web Service (Database as a SOA endpoint)
- Using the DBUriServlet-servlet
Every time I will use the output of “SELECT * FROM ALL_OBJECTS” as my data set. In my current (on Windows) Oracle 12.1.0.2 database, this is for the user account I used “JSON_WIKI”, with the DBA role and XDBADMIN role privileges granted in a pluggable database: a grant total of 89.323 objects.
The examples given are…an example of the mentioned method. Nothing more, nothing less. I hope you can use the one of the examples as a base for you solution. As mentioned: “It will depend of the goal you like to achieve and the context within it should work, which option might work for you”. There are no “best practices”, only good “rules of numb” 😉
The examples given are also very simple regarding “creating XML out-of-the-box”, that is in Oracle’s canonical default format (embedded in ROWSET/ROW elements and everything in upper-case). Be aware that XML is seldom “everything in upper-case” and also case-sensitive.
Saving XML content to disk
Let’s have a look at the options mentioned.
Using UTL_FILE / DBMS_LOB()
There is lots of stuff out there regarding programmatic-ally writing content to disk. For example using UTL_FILE in conjunction with DBMS_LOB.
Have a look at a nice and clean example from Jeff Hunter here:
Using DBMS_XMLDOM.writetofile()
Probably the lesser option, at least I think if you would pick this one instead of DBMS_XSLPROCESSOR. Have a look at my old post “Howto: Saving and storing XML Data – A Tuning Adventure” to see the differences between the two.
- Create an Oracle directory (and grant privileges to use it)
- Save the content to file via DBMS_XMLDOM
--
CREATE DIRECTORY xt_dir AS 'c:\temp';
--
DECLARE
rc sys_refcursor;
doc DBMS_XMLDOM.DOMDocument;
BEGIN
OPEN rc FOR SELECT * FROM all_objects;
doc := DBMS_XMLDOM.NewDOMDocument(xmltype( rc ));
DBMS_XMLDOM.WRITETOFILE(doc, 'XT_DIR/writetofile.xml');
END;
/
-- 39.279 seconds / 48.184.740 bytes
As a reference: the anonymous PL/SQL block will create a “writetofile.xml” file in 39.279 seconds using SQL*Developer and with a file size (on disk) of 48.184.740 bytes.
Using DBMS_XSLPROCESSOR.clob2file()
Using DBMS_XSLPROCESSOR is similar to the use of using DBMS_XMLDOM.
- Create an Oracle directory (and grant privileges to use it)
- Save the content to file via DBMS_XSLPROCESSOR
--
CREATE DIRECTORY xt_dir AS 'c:\temp';
--
DECLARE
rc sys_refcursor;
BEGIN
OPEN rc FOR SELECT * FROM all_objects;
dbms_xslprocessor.clob2file( xmltype( rc ).getclobval( ) , 'XT_DIR','clob2file.xml');
END;
/
-- 35.689 seconds / 45.391.562 bytes
As a reference: the anonymous PL/SQL block will create a “clob2file.xml” file in 35.689 seconds using SQL*Developer and with a file size (on disk) of 45.391.562 bytes. If you have read “Howto: Saving and storing XML Data – A Tuning Adventure” you understand now why there is a difference in file size, despite using the same base SQL statement.
Using an External Table
Not sure if saving something to Oracle Dumpfile might be a useful thing to do but, of course, this format saving the content to disk, can be used as a basis for another external table in a different database.
- Create an Oracle directory (and grant privileges to use it)
- Create a external table making use of “ORACLE_DATAPUMP” functionality
--
CREATE DIRECTORY xt_dir AS 'c:\temp';
--
DROP TABLE my_xml_objects_xt;
--
CREATE TABLE my_xml_objects_xt
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY xt_dir
LOCATION ( 'xml_content2.dmp' )
)
AS
SELECT XMLSERIALIZE
(DOCUMENT
(XMLTYPE(cursor(SELECT * FROM all_objects)))
AS CLOB INDENT SIZE=0) AS XMLCONTENT
FROM dual;
-- 12.922 seconds / 85.213.184 bytes
As a reference: the create external table statement will create a “xml_content.dmp” dump file in 12.922 seconds, using SQL*Developer, and with a file size (on disk) of 85.213.184 bytes.
By the way, if you use the GETCLOBVAL() alternative to XMLSERIALIZE, the XML content dumpfile “xml_content.dmp” will be bigger in size.
--
CREATE DIRECTORY xt_dir AS 'c:\temp';
--
DROP TABLE my_xml_objects_xt;
--
CREATE TABLE my_xml_objects_xt
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY xt_dir
LOCATION ( 'xml_content.dmp' )
)
AS
SELECT XMLTYPE(cursor(SELECT * FROM all_objects)).getclobval() AS XMLCONTENT
FROM dual;
-- 16.125 seconds / 90.800.128 bytes
As a reference: the create external table statement will create a “xml_content.dmp” dump file in 16.125 seconds, on my laptop using SQL*Developer, and with a file size (on disk) of 90.800.128 bytes.
Making use of the XDB Repository
Oracle XMLDB comes with more then only methods to create XML or consume XML. Just like the relational database, it also comes with a “dictionary” (situated XDB account), methods of securing content and moving it in and out the database (HTTP(s), FTP, WebDAV).
The XDB Repository is rather complex and has a lot of mechanisms in place.
The XDB protocol server, some might mistake this for the “local PL/SQL Gateway”, offers besides PL/SQL access, also other “servlet” methods like the Native Database Web Service, FTP, HTTP(s) and WebDAV access. Nowadays in Oracle 12, also the DB Express tool is depending on it.
The methods that follow, need the Listener section to be made aware of needed HTTP or FTP functionality. The following will show you how and because its foundation is based on the SHARED SERVER connectivity, you should also optimize this by setting database parameters SHARED_SERVERS (5+), DISPATCHER(1+) and LARGE_POOL_SIZE(size+)
To get hold of the content, you have to create a file, called “resource” in the XDB Repository or get the content from the database via a “servlet” method provided by Oracle (or build your own via the PL/SQL gateway or using APEX/ORDS).
I only describe here the “native” provided ones by the Oracle database, XMLDB environment. By the way, “files” and “folders” are both named “resources” accoording to the WebDAV standards
Using DBMS_XDB.createresource()
By creating a “resource”, you write your content to a “file” in the XDB Repository environment. This file can later on be accessed by FTP, WebDAV, HTTP (or DBFS or…) and saved locally or somewhere on the network.
- Enable FTP or HTTP, WebDAV access on a defined/specific port
- Create a folder in the XDB repository (to store your created content)
- Create content in the (newly) created directory
-- -------------------------------------
-- via SYS or... (XDBADMIN role needed)
-- -------------------------------------
--
-- HTTP/WebDAV access
--
SELECT dbms_xdb.gethttpport() FROM dual;
EXECUTE dbms_xdb.setHTTPport(8080);
ALTER SYSTEM REGISTER;
SELECT dbms_xdb.gethttpport() FROM dual;
--
-- FTP access
--
SELECT dbms_xdb.getFTPport() FROM dual;
EXECUTE dbms_xdb.setFTPport(2100);
ALTER SYSTEM REGISTER;
SELECT dbms_xdb.getFTPport() FROM dual;
--
-- -------------------------------------
-- As owner of the future content
-- -------------------------------------
--
-- Create XDB Repository "directories"
--
DECLARE
TYPE v_ntt IS TABLE OF VARCHAR2(1024);
v_res v_ntt := v_ntt( '/files/'
, '/files/upload/'
, '/files/res/'
, '/files/download/'
);
v_boolean BOOLEAN;
v_exist EXCEPTION;
PRAGMA EXCEPTION_INIT(v_exist, -31003);
BEGIN
FOR i IN v_res.FIRST .. v_res.LAST
LOOP
BEGIN
v_boolean := DBMS_XDB.CREATEFOLDER(v_res(i));
EXCEPTION
WHEN v_exist THEN NULL;
END;
END LOOP;
COMMIT;
END;
/
COMMIT;
--
-- Create file "xmlserialize.xml" in directory /files/upload
--
DECLARE
v_xmlcontent CLOB;
b BOOLEAN := FALSE;
BEGIN
SELECT XMLSERIALIZE (DOCUMENT (XMLTYPE(cursor(SELECT * FROM all_objects))) AS CLOB INDENT SIZE=0)
INTO v_xmlcontent
FROM dual;
--
b := DBMS_XDB.CREATERESOURCE( '/files/upload/xmlserialize.xml', v_xmlcontent);
END;
/
-- 13.223 seconds / 48.184.740 bytes
As reference: the create XDB resource statement will create a “xmlserialize.xml” file in 13.223 seconds (somewhere stored still in the database), using SQL*Developer, and with a file size of 48.184.740 bytes (according to the XDB environment).
Get the content via HTTP(s), FTP
There are several ways to now get access to the created XML file.
One would be via a FTP method, for example, via the Windows command prompt this could be done via:
The dir statement will show the current situation and files sizes produced.
Alternatively you could use a HTTP based method (like WebDAV is). For example, via an opensource “wget” executable for Windows:
Using Native Database Web Service
Native Database Web Services, introduced in Oracle database version 11.1, are described in detail here “Howto: Create a Native Database Web Service” and…of course…in the Oracle XMLDB Developers Guide. So I urge you to read up on it via those resources and other good material on the web. That said, it is not a full blown alternative for production heavy use processing. Use a decent web server alternative solution in such cases.
In short (and don’t forget shared server, large_pool_size, etc., for more performance)
- Install Oracle XMLDB
- Enable the Protocol Server for HTTP access
- Enable the orawsv entry points in xdbconfig.xml
- Create an example to test the NDWS service
- Test the NDWS service by calling the WSDL entry point
.
--
-- Enable the orawsv entry point
--
DECLARE
SERVLET_NAME VARCHAR2(32) := 'orawsv';
BEGIN
DBMS_XDB.deleteServletMapping(SERVLET_NAME);
DBMS_XDB.deleteServlet(SERVLET_NAME);
DBMS_XDB.addServlet(NAME => SERVLET_NAME,
LANGUAGE => 'C',
DISPNAME => 'Oracle Query Web Service',
DESCRIPT => 'Servlet for issuing queries as a Web Service',
SCHEMA => 'XDB');
DBMS_XDB.addServletSecRole(SERVNAME => SERVLET_NAME,
ROLENAME => 'XDB_WEBSERVICES',
ROLELINK => 'XDB_WEBSERVICES');
DBMS_XDB.addServletMapping(PATTERN => '/orawsv/*',
NAME => SERVLET_NAME);
END;
/
--
-- Register/make known, the new situation at the listener
--
ALTER system register;
--
BEGIN
dbms_network_acl_admin.create_acl('localhost.xml', 'ACL for 127.0.0.1', 'JSON_WIKI', TRUE, 'connect');
dbms_network_acl_admin.assign_acl('localhost.xml', '127.0.0.1');
END;
/
--
commit;
--
-- Grant the privileges needed to access via the orawsv servlet / new procedure
--
/
GRANT XDB_WEBSERVICES TO JSON_WIKI
/
GRANT XDB_WEBSERVICES_OVER_HTTP TO JSON_WIKI
/
GRANT XDB_WEBSERVICES_WITH_PUBLIC TO JSON_WIKI
/
-- reconnect
var url varchar2(700)
--
SET long 10000
SET DEFINE OFF
--
BEGIN
:url := 'http://JSON_WIKI:json@127.0.0.1:' || dbms_xdb.getHttpPort() || '/orawsv?wsdl';
END;
/
print url
SET long 10000 pages 0
--
SELECT httpuritype( :url ).getCLOB() FROM dual
/
--
-- Create the new procedure (to generate the needed XML content)
--
CREATE OR REPLACE function GET_XML (INPUT_VALUE IN NUMBER DEFAULT 1) return CLOB
AS
xml_content CLOB;
BEGIN
--
SELECT XMLSERIALIZE (DOCUMENT (XMLTYPE(cursor(SELECT * FROM all_objects where rownum < = INPUT_VALUE ))) AS CLOB INDENT SIZE=0)
INTO xml_content
FROM dual;
--
RETURN xml_content;
--
END;
/
--
-- Show the different URL input for access and WSDL definitions
--
BEGIN
:url := 'http://JSON_WIKI:json@localhost:' || dbms_xdb.getHttpPort() || '/orawsv/JSON_WIKI/GET_XML?wsdl';
end;
/
PRINT url
-- http://localhost:8080/orawsv/JSON_WIKI/GET_XML?wsdl
BEGIN
:url := 'http://JSON_WIKI:json@localhost:' || dbms_xdb.getHttpPort() || '/orawsv/JSON_WIKI/GET_XML';
end;
/
PRINT url
-- http://JSON_WIKI:json@localhost:8080/orawsv/JSON_WIKI/GET_XML
SET serveroutput ON
--
Access via, for example SoapUI would give you the following output (click the picture for more detailed view).
Using the DBUriServlet-servlet
Often forgotten functionality from the 11.1 era, the DBUriServlet XDB servlet is also a means to directly access relational or XML content from the database.
- Enable the Protocol Server for HTTP access
- Enable the oradb entry points in xdbconfig.xml (enabled by default...)
- Access to a (own/owned) object in the schema / account used
I demonstrate the oradb / DBUri Servlet, again, via a simple "wget" statement. In this case I only have to create a view for direct access from the servlet based on ALL_OBJECTS so...
--
create or replace view my_objects as
select * from all_objects;
--
After this I can issue the wget statement to get the content and save it locally.
I hope this post will give you a short / fast insight of some of the possibilities to save XML content outside the database. Some of the code used by me, can be downloaded here:
HTH/M
