Search code examples
oracle-databasexmltype

Changing Storage Option for XMLType column in Oracle 11g

I am using XMLType column in some of my oracle database table. Earlier(in 11.2.0.2) the default storage type considered is CLOB. So If you issue a query for the XMLType columns, I can see the content of the column as XML string. But when I drop and re-create all the tables and inserted some data, I could not get the content of the XMLType columns. It simpley display the XMLType in the cloumn value. I have a doubt that whether the storage type is chaged in BINARY XML? So I issue the following alter statement:

ALTER TABLE "MYSCHEMA"."SYSTEMPROP"
   MODIFY ("XMLCOL")
     XMLTYPE COLUMN "XMLCOL" STORE AS CLOB;

Please note that there are already some data present in the table. Event after when I delete and insert a row, the content is showing as XMLType. I am using SQL developer UI tool. Can anybody suggest a way to fix this issue?

Edit:

Ok, Now we have decided that we will store the XMLType column content as SECURE FILE BINARY XML. So we have table like this:

CREATE TABLE XMYTYPETEST
(
    ID       NUMBER(8) NOT NULL,
    VID      NUMBER(4) NOT NULL,
    UserName           VARCHAR2(50),
    DateModified       TIMESTAMP(6),
    Details  XMLType
)XMLTYPE COLUMN  Details STORE AS SECUREFILE BINARY XML; 

Insert into XMYTYPETEST values(10001,1,'XXXX',sysdate,'<test><node1>BLOBTest</node1></test>');

Select * from XMYTYPETEST;

The XMLType colum is displayed as "SYS.XMLType" in sql developer. So how to get the content of the binary XML?

Result of the Query

Edit:

SELECT x.ID,x.Vid, x.details.getCLOBVal() FROM XMYTYPETESTx where x.ID=100000;

The above query works out for me finally.

Solution

  • The underlying storage for xmldata inside oracle database is either CLOB or Binary.

    And it defaults to Binary storage in 11g.

    But irrespective of the storage, your queries on the xmltype column should yield you consistent results.

    >>>> So how to get the content of the binary XML?

    The way to get the content of an xmltype column using queries does not change.

    • select xmlquery(..)
    • select xmlcast(xmlquery(...))
    • select extract(), extractValue(), ...

    These are some of the ways data within xml is extracted.

    Hope this helps.