XMLType field to small for large xml

FROM

Oracle9 i
XML Database Developer’s Guide - Oracle XML DB
Release 2 (9.2)
October 2002
Part No. A96620-02


The XMLType column can be created like any other user-defined type column:
CREATE TABLE warehouses(
warehouse_id NUMBER(4),
warehouse_spec XMLTYPE,
warehouse_name VARCHAR2(35),
location_id NUMBER(4));
Example 4–2 Creating XMLType: Creating XMLType Columns
As explained, you can create XMLType columns by simply using the XMLType as
the datatype. The following statement creates a purchase order document column,
poDoc, of XMLType:
CREATE TABLE po_xml_tab(
poid number,
poDoc XMLTYPE);
CREATE TABLE po_xtab of XMLType; -- this creates a table of XMLType. The default
-- is CLOB based storage.
Example 4–3 Adding XMLType Columns
You can alter tables to add XMLType columns as well. This is similar to any other
datatype. The following statement adds a new customer document column to the
table:
ALTER TABLE po_xml_tab add (custDoc XMLType);
Example 4–4 Dropping XMLType Columns
You can alter tables to drop XMLType columns, similar to any other datatype. The
following statement drops column custDoc:
ALTER TABLE po_xml_tab drop (custDoc);


Inserting Values into an XMLType Column


To insert values into the XMLType column, you need to bind an XMLType instance.
Example 4–5 Inserting into XMLTYpe Using the XMLType() Constructor
An XMLType instance can be easily created from a VARCHAR or a Character Large
Object (CLOB) by using the XMLType() constructor:
INSERT INTO warehouses VALUES
( 100, XMLType(
’<Warehouse whNo="100">
<Building>Owned</Building>
</Warehouse>’), ’Tower Records’, 1003);
This example creates an XMLType instance from a string literal. The input to
createXML() can be any expression that returns a VARCHAR2 or CLOB.
createXML() also checks that the input XML is well-formed.

Using XMLType in an SQL Statement
The following simple SELECT statement shows how you can use XMLType in an
SQL statement:
Example 4–6 Using XMLType and in a SELECT Statement
SELECT
w.warehouse_spec.extract('/Warehouse/Building/text()').getStringVal()
"Building"
FROM warehouses w;
where warehouse_spec is an XMLType column operated on by member function
extract(). The result of this simple query is a string (varchar2):
Building
-----------------
Owned

Updating an XMLType Column
An XML document in an XMLType can be stored packed in a CLOB. Then updates
have to replace the whole document in place.
Example 4–7 Updating XMLType
To update an XML document, you can execute a standard SQL UPDATE statement.
You need to bind an XMLType instance, as follows:
UPDATE warehouses SET warehouse_spec = XMLType
(’<Warehouse whono="200">
<Building>Leased</Building>
</Warehouse>’);
This example created an XMLType instance from a string literal and updates column
warehouse_spec with the new value.
Note: Any triggers would get fired on the UPDATE statement
You can see and modify the XML value inside the triggers.

Using XMLType 4-11
Guidelines for Using XMLType Tables and Columns
The following are guidelines for storing XML data in XMLType tables and columns:
Define table/column of XMLType
First, define a table/column of XMLType. You can include optional storage
characteristics with the table/column definition.
Create an XMLType Instance
Use the XMLType constructor to create the XMLType instance before inserting into
the column/table. You can also use a variety of other functions that return
XMLType.
Select or Extract a Particular XMLType Instance
You can select out the XMLType instance from the column. XMLType also offers a
choice of member functions, such as extract() and existsNode(), to extract a
particular node and to check to see if a node exists respectively. See the table of
XMLType member functions in Oracle9i XML API Reference - XDK and Oracle XML
DB.
Note: This release of Oracle supports creating tables of XMLType.
You can create object references (REFs) to these tables and use them
in the object cache.
See Also: "SYS_XMLGEN(): Converting an XMLType Instance"

To load internal LOBs (BLOBs, CLOBs, and NCLOBs) or XML columns from a
primary datafile, you can use the following standard SQL*Loader formats:
n Predetermined size fields
n Delimited fields
n Length-value pair fields
These formats is described in the following sections and in Oracle9i Database
Utilities.
LOB Data in Predetermined Size Fields
This is a very fast and conceptually simple format in which to load LOBs.
Note: Because the LOBs you are loading may not be of equal size, you can use
whitespace to pad the LOB data to make the LOBs all of equal length within a
particular data field.
LOB Data in Delimited Fields
This format handles LOBs of different sizes within the same column (datafile field)
without problem. However, this added flexibility can affect performance, because
SQL*Loader must scan through the data, looking for the delimiter string.
As with single-character delimiters, when you specify string delimiters, you should
consider the character set of the datafile. When the character set of the datafile is
different than that of the control file, you can specify the delimiters in hexadecimal
(that is, ’hexadecimal string’). If the delimiters are specified in hexadecimal
notation, the specification must consist of characters that are valid in the character
set of the input datafile. In contrast, if hexadecimal specification is not used, the
delimiter specification is considered to be in the client’s (that is, the control file’s)
character set. In this case, the delimiter is converted into the datafile’s character set
before SQL*Loader searches for the delimiter in the datafile.
Loading LOB Data from LOBFILEs
LOB data can be lengthy enough so that it makes sense to load it from a LOBFILE
instead of from a primary datafile. In LOBFILEs, LOB data instances are still
considered to be in fields (predetermined size, delimited, length-value), but these
fields are not organized into records (the concept of a record does not exist within
LOBFILEs). Therefore, the processing overhead of dealing with records is avoided.
This type of organization of data is ideal for LOB loading.
There is no requirement that a LOB from a LOBFILE fit in memory. SQL*Loader
reads LOBFILEs in 64 KB chunks.
In LOBFILEs the data can be in any of the following types of fields:
n A single LOB field into which the entire contents of a file can be read
n Predetermined size fields (fixed-length fields)
n Delimited fields (that is, TERMINATED BY or ENCLOSED BY)
n The clause PRESERVE BLANKS is not applicable to fields read from a
LOBFILE.
n Length-value pair fields (variable-length fields)--VARRAW, VARCHAR, or
VARCHARC loader datatypes--are used for loading from this type of field.
All of the previously mentioned field types can be used to load XML columns.