After completing this snippet you should be able
to:
Load large XML documents containing more
than 4000 characters into XMLType tables or XMLType columns
in the database
Introduction
Oracle XML DB is a set of built-in
high-performance storage and retrieval technologies developed
especially for XML. Oracle XML DB fully absorbs the World Wide Web
Consortium (W3C) XML data model into Oracle9i database and
provides new standard access methods for navigating and querying
XML. You get all the advantages of relational database technology
and XML technology at the same time. Oracle XML DB can be
used to store, query, update, transform, or otherwise process
XML, and access the same XML data using the SQL queries.
In this snippet we are providing PL/SQL
approach and JDBC approach for loading large XML
documents into XMLType tables or XMLType columns.
Pre-requisites for running the snippet
Oracle9idatabase (version
9.2.0.1 and above).
Classes12.zip or Classes12.jar
available under the ORACLE_HOME\jdbc\lib
directory should be included in the CLASSPATH
environment variable for the JDBC approach.
SQL Script that will create the required
database table. Please execute the following SQL before running
the code examples.
CREATE TABLE poTable
(purchaseOrder XMLType) ;
Note: For demonstration purpose we have considered
loading the XML document into an XMLType column. The same
approach can be used for XMLType tables also.
The correct way of inserting large documents into
XMLType column is using a CLOB and not VARCHAR2 since VARCHAR2 can be
used to load maximum of 4000 characters only. Instead a CLOB can hold
maximum of 4GB characters and can be used to load XML containing more
than 4000 characters. If VARCHAR2 is used for inserting large XML document
(containing more than 4000 characters) into an XMLType column, a database
error "ORA-01704: string literal too long" is encountered. The
following example provides a way to solve this problem by using a CLOB
to hold the XML document.
The loading of a large XML document is demonstrated
using a PL/SQL procedure 'loadPurchaseOrder'. In this procedure a CLOB
object 'poXML' is declared to hold the XML content that needs to be
stored into the database. The XML will be stored into an XMLType column
'purchaseOrder' of the table 'poTable'. The XML that CLOB object holds
is a large XML document containing more than 4000 characters.
CREATE or REPLACE PROCEDURE loadPurchaseOrder IS
-- Declare a CLOB variable
poXML CLOB;
BEGIN
-- Store the Purchase Order XML in the CLOB variable
poXML := '<?xml version="1.0" encoding="UTF-8"
standalone="yes"?>
<PurchaseOrder>
<PONum>1001</PONum>
<Company>Oracle
Corp</Company>
.
.
.
</PurchaseOrder>';
-- Insert the Purchase Order XML into an XMLType column
INSERT INTO potable (purchaseOrder) VALUES (XMLTYPE(poXML));
--Handle the exceptions
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20101, 'Exception occurred
in loadPurchaseOrder procedure :'||SQLERRM);
END loadPurchaseOrder;
PL/SQL procedure similar to the above mentioned example
can be used for loading large XML documents into an XMLType column.
Limitation: In PL/SQL, a String
constant can take a maximum size of 64K. If an XML document > 64k
is to be loaded into the XML DB, the best approach is to load the document
from a file. For details on this approach, please refer to the "Oracle
XML DB Utilities Package" in the XML DB Sample Corner at /sample_code/tech/xml/xmldb/index.html.
JDBC approach
If a large XML document (typically
greater than 4000 characters) is inserted into an XMLType column
using a String object in JDBC, the run-time error
"java.sql.SQLException: Data size bigger than max size for this
type" is encountered. This problem can be solved by using
a CLOB object to hold the large XML documents.
The following code demonstrates the loading of large XML documents
using Java CLOB Object. The CLOB object is created using oracle.sql.CLOB
class on the client side to hold the XML. The oracle.sql.CLOB
class is the Oracle JDBC driver's implementation of standard
JDBC java.sql.Clob interface.
To load the XML, the first step is to create a
CLOB object at the client side that will hold the XML content.
Once the CLOB object holding the XML content is created, the
next step is to bind the CLOB object to the JDBC prepared
statement that is used for insertion of XML data into the
XMLType column.
Step 1: Create the CLOB
object
The following method getCLOB() creates
and returns a CLOB object that holds the specified XML data. This
method can be used to insert the XML string into the database
XMLType column.
Click here
to view the code for the getCLOB() method
Step 2: Loading an XML
document into the database using the CLOB object.
The CLOB object containing the XML needs to be
bound to the JDBC prepared statement that is used for the
insertion of the data into the XMLType column in the database.
Following method insertXML() demonstrates the insertion of
an XML data into the XMLType column. This method uses the
prepared statement to execute the database query. It makes
a call to the getCLOB() method described above to create and
return a CLOB object that holds the XML data.
Click here
to view the code for the insertXML() method.
Bookmark
