Tom,

Thanks.

Can we precreate the table with XMLTYPE and then direct-path insert with APPEND hint and nologging.

Will this improve the performance of conversion process over CTAS method.

Our CLOB table size is currently 38G. Largest XML documents is 50M.
Applying 'xmltype()' member function before converting seems will take time. Any ways to speed up the process ?.
By the way, the data stored in our CLOB table are already in XML format.

Do we still need to specify xmltype(CLOB column) or is it optional ?

<< I mean, you can always cast your existing xml as an xmltype on the fly --
depends on what you plan on using here.

You mean, without really converting the datatype to xmltype, we can really take advantage of xmltype functionalities with CLOB datatype itself. Can you please explain.

The reasons for moving to xmltype :

1. As compared to CLOB datatype, xmltype does validate xml data which does not strictly allow any bad characters or control characters in the XML document which is being stored. We had problems in retrieving the clob xml when it had bad characters in it. Hope xmltype does not allow this to happen due to validation check.

2. As XMLTYPE is really for storing XMLdata, it does make sense to use the right datatype. But i am sure we have to compromise little bit on performance drop in comparing with CLOB datatype.

3. Developers would like to use new xmltype features like doing piece-wise extraction from the stored XML and utlizing the XSLT from xmltype, they are saying it would be much easier to use xmltype rather than CLOB datatype.

I am sure inserting/loading xml into xmltype will be much slower comparing with CLOB.

Thanks Tom.
>> 3) you can do that "on the fly" if you have a large legacy to deal with (new
stuff in new thing, old stuff converted as needed on the fly)

Sorry, i do not get this. Could you explain.

Finally, Whats your suggestion/recommendation in keeping the reasons opened. do you recommend to go with XMLTYPE (CLOB based) or stick with the existing CLOB datatype itself.

I have the following index on a CLOB that has XML stored in it.

CREATE TABLE MY_TABLE
(XML_MESSAGE CLOB NOT NULL);

CREATE INDEX XML_MESSAGE_IDX ON MY_TABLE
(XML_MESSAGE) INDEXTYPE IS CTXSYS.CONTEXT;

Is an index like this any more or less effecient than on an XMLType? What about some "bad" XML stored in my CLOB. About 1% of the XML isn't well formed.

This is your response to my earlier question one step up.
<< you have old data, you want to "xmltype it", just "select xmltype(old_data) from
table"

you have new data, store in new column (xmltype column), xmltype it on the way
in
>>

If i got it right, your suggestion is to maintain both CLOB type old column and XMLTYPE new column on the same table.

This will work for me. Pick up the required CLOB doc based on the status indicator whether ACTIVE/INACTIVE and insert into the new XMLTYPE column (only required). Clean up the succesfully inserted CLOB column data from the table.

"select XMLTYPE(old_data_in_clob_column) from <CLOBTABLE> where <REQUIRED_CLOB_DOC> based on status_indicator"

For all new xml data, just store it in XMLTYPE columns.


Wonderful idea !!

But where can we find the documentation for the XMLType function?

We have tried in SQL reference, XML DB reference and searching the net... but we cant seem to find the actual description of the function.

Someone says that it will not take arguments longer than 4000 chars? Is this true?

We need it for storing XML from string format into XMLTYPE column via JDBC

We are on oracle 9.2.0.6 on AIX

Regards from the blind Danes
Mette

We have tried it with a clob, but can you tell us id it will work with a:

xmltype('<tag>,,, <bbbb>bla bl etc etc etc etc a bla bla bla bla bla bla bla....</tag>') for more than 4000 bytes?

Or guide us to the documentation somwhere to read about it?

regards
Mette & Claus

Tom,
I am trying to Generate XML from Table data[to make an API Call for web service]. Here is my requirement.
Considering Emp and Dept tables, I will present an example and would like to see if there is any way getting this format from Oracle.
btw i'musing Oracle 10g R2.

Sample output: for department 10 [all employees]

  <Domain>
    <Departments>
       <department userid='xx' password='yy'>
       <deptno>10</deptno>
       <dname>Accounts</dname>
       <Employees>
       <Employee userid='xx' password='yy'>
       <employee>
       <empno>7234</empno>
       <ename>John</ename>
       <salary>2500</salary>
       </employee>
       <employee>
       <empno>7235</empno>
       <ename>Tom</ename>
       <salary>2600</salary>
       </employee>
       <employee>
       <empno>7236</empno>
       <ename>Dick</ename>
       <salary>3500</salary>
       </employee>
     </Departments>
   </Domain>

userid and password attrbutes for each department and employee are required for our API Call

I tried using SQLX [XMLElement/XMLForest] method but after 5/6 tries i gave up..as i keep getting some errors all the time. I was hoping you can show me a better way of doing this. Also, with SQLX method i have to create same structure each andevery time i call API for different tables. Is there any better way of doing this using XSLStylesheet and XMLTransform methods?

Eagerly waiting for your response.
Thanks,
Maverick

Hi Tom,

I have an old application with supposedly XML's in a CLOB. But but but ... some of the CLOBS are not wellformed XML !

I would like to be able to extract some data from the valid ones (without chaning the old appl. data).

I have tried an sql like this:

select extractvalue(xmltype(body),'//FinancialActivityKey','xmlns=http://ACORD.org/Standards/Life/2') finkey, body 
from inboundmessage im
where not exists ( select '1' from txliferequest tl where im.id = tl.inmessageid) and 
header is not null and
rownum < 100;

(I know for sure that the XML is invalid when the header is null).

But is it possible just to extract the valid CLOB/XML?

And .... Can you lead us to the description of XMLTYPE function and methods in an Oracle manuel somewhere? We cant seem to find it

best regards
Mette

Tom,

Thanks in advance for some guidance with following:

We recently migrated our dbs from 9i to 10gR2 (10.2.0.4) and got an application table that stores xml data on a long data column.

We¿re required to encrypt content data of the column holding the xml_data_txt so to accomplish this I¿ve been exploring to covert the long data to clob with below CTAS statement then use dbms_crypto or TDE as discussed on:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:44742967463133
Is the CTAS method to reorganize the table a good approach?

Or should we consider converting to XMLTYPE? As discussed on:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:44892646023494

Is TDE even supported on CLOB or XMLTYPE?

table_with_long_data_type
CLIENT_ID NOT NULL VARCHAR2(3)
PRODUCT_ID NOT NULL VARCHAR2(3)
SITE_ID NOT NULL VARCHAR2(3)
SVI_USER_ID NOT NULL VARCHAR2(18)
LOGIN_ID NOT NULL VARCHAR2(22)
NAME_SEARCH_KEY NOT NULL VARCHAR2(80)
SSN_TAX_ID VARCHAR2(12)
EMAIL VARCHAR2(80)
XML_DATA_TXT LONG

CTAS to reorganized the table:

create table user. table_with_long_data_type_tmp1
INITRANS 35 MAXTRANS 255
storage (initial 1G next 512M freelists 35 freelist groups 2)
tablespace tv_long
as
select dbms_metadata_util.long2clob(50000,' user. table_with_long_data_type_tmp1','xml_data_txt','rowid') xml_data_txt
from tv.site_user_profile
order by client_id,site_id,product_id,svi_user_id

desc user. table_with_long_data_type_tmp1
Name Null? Type
----------------------------------------- -------- ----------------------------
XML_DATA_TXT CLOB

ops$tkyte%ORA10GR2> create table t ( x clob encrypt );
create table t ( x clob encrypt )
                                *
ERROR at line 1:
ORA-28330: encryption is not allowed for this data type

Tom,

Excellent tips the TO_LOB worked much better on the tests I performed for this few days ago. I¿m also getting familiarize with the ¿dbms_redefinition¿ It would be great if I can re-create this table without taking off-line

While reading on some other posts
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:865627341240

I came across the freelists and freelist group storage parameters discussion and on your follow ups you mentioned several times that these are not needed any more if ASSM is used.
On my create table statement below looks like I would need to create the table without the ¿freelists 35 freelist groups 2¿ portion, correct?

This table is heavevly concurrently accessed, updated, and inserted, so I thinking to set the INITRANS to 35, is that good value?

This table is about 4.5 Gbs, is the 1G for the initial and 512M for the next still valid?

create table user.test_tmp2
INITRANS 35 MAXTRANS 255
storage (initial 1G next 512M freelists 35 freelist groups 2)
tablespace tv_long
as
select CLIENT_ID,PRODUCT_ID,
SITE_ID,SVI_USER_ID,
LOGIN_ID,NAME_SEARCH_KEY,
SSN_TAX_ID,EMAIL,
TO_LOB(xml_data_txt) xml_data_txt
from user.orig_tbl
order by client_id,site_id,product_id,svi_user_id

Thanks in advance again for your guidance!