Loading a large XML Document into the Database
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
IntroductionOracle 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 snippetOracle9i database (version 9.2.0.1 and above).
Classes12.zip or Classes12.jar available under the ORACLE_HOMEjdbclib 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.
Code SnippetPL/SQL approach
JDBC approach
PL/SQL approachThe 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 approachIf 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.
ResourcesOracle9i XML Database Developer's Guide - Oracle XML DB
Oracle9i Application Developer's Guide - Large Objects (LOBs)
***** 아름다운프로님에 의해서 게시물 복사 + 카테고리변경되었습니다 (2003-12-18 16:49)
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
IntroductionOracle 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 snippetOracle9i database (version 9.2.0.1 and above).
Classes12.zip or Classes12.jar available under the ORACLE_HOMEjdbclib 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.
Code SnippetPL/SQL approach
JDBC approach
PL/SQL approachThe 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 approachIf 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.
ResourcesOracle9i XML Database Developer's Guide - Oracle XML DB
Oracle9i Application Developer's Guide - Large Objects (LOBs)
***** 아름다운프로님에 의해서 게시물 복사 + 카테고리변경되었습니다 (2003-12-18 16:49)
