If the XMLType column is created with the CLOB storage type, then the driver returns it without use of the special getClobVal function, that is, you can use:
SELECT XML_col FROM table_name...
instead of
SELECT XML_col.getClobVal()...
The following example illustrates using the CLOB storage type:
CREATE TABLE po_xml_tab(
poid NUMBER(10),
poDoc XMLTYPE
)
XMLType COLUMN poDoc
STORE AS CLOB (
TABLESPACE lob_seg_ts1 STORAGE (INITIAL 4096 NEXT 4096)
CHUNK 4096 NOCACHE LOGGING
)
The next example illustrates how to create a table, insert data, and retrieve data when not using the CLOB storage type:
The PURCHASEORDER table contains one column—PODOCUMENT—with a data type of XMLType (sys.XMLTYPE). The next step is to insert one purchase order, created by the static function sys.XMLTYPE.createXML:
INSERT INTO PURCHASEORDER (PODOCUMENT) values (
sys.XMLTYPE.createXML(
'<PurchaseOrder>
<Reference>BLAKE-2001062514034298PDT</Reference>
<Actions>
<Action>
<User>KING</User>
<Date/>
</Action>
</Actions>
<Reject/>
<Requester>David E. Blake</Requester>
<User>BLAKE</User>
<CostCenter>S30</CostCenter>
<ShippingInstructions>
<name>David E. Blake</name>
<address>400 Oracle Parkway Redwood Shores, CA, 94065 USA</address>
<telephone>650 999 9999</telephone>
</ShippingInstructions>
<SpecialInstructions>Air Mail</SpecialInstructions>
<LineItems>
<LineItem ItemNumber="1">
<Description>The Birth of a Nation</Description>
<Part Id="EE888" UnitPrice="65.39" Quantity="31"/>
</LineItem>
</LineItems>
</PurchaseOrder>
'));
Use the getClobVal function to retrieve the data:
SELECT p.podocument.getClobVal() FROM PURCHASEORDER p;
1 Note that the table space must be created before executing a statement similar to the one used in the example.
