thanks Tom,

the examples for case b are quite useful.

for case a) is it a bad idea to use dbms_xmlsave package to parse and load the data directly to the table in a stored procedure.

the reason why i am asking this, in our case, front end can update/add multiple records to a table.
so, in that case front end(ASP) sends the multiple records as an XML and pass it to the back end and at the back end, we use dbms_xmlsave to save update/add.

otherwise, to insert multiple records, front end needs to make multile DB calls

your thoughts please.

Tom,

how else can we pass multiple records from a front end like ASP.NET or ASP to Oracle stored procedure?

Drivers don't support pl/sql arrays to pass multiple records and we don't want to call a stored procedure multiple times..because we want to treate set of records as a one transaction(like master and detail data, master has one row and corresponding detail table has multiple records)..

is there any other way to pass multiple records to the stored proc other than XML?

John,

Instead of calling an Oracle stored proc to do the insert, create an ADO record set, and use the Add() method to add rows. You can also set the record set to 'batch' mode.

If you really want to go through an Oracle stored proc, create a view, and use INSTEAD OF triggers.

Takmeister

NOt wishing to get petty in "my OS is better than yours", it was refreshing to hear a major technologist like yourself say that.

Thank you

John,

Sorry, my previous post said you needed to use the 'Add()' method. Well, ADO record sets do not have an 'Add()' method - I'd meant to say 'AddNew()'. Shesh! You just cant trust anybody to give you correct information nowadays!

Heres a little something I prepared earlier - run it from the command line with 'cscript rs.vbs' or adapt it for ASP :-

Const adUseClient = 3
Const adOpenKeyset = 1
Const adLockBatchOptimistic = 4

Dim oCon_orcl ' ADO Connection to Oracle instance 'orcl'
Dim oRs_Dept ' ADO Record Set object on Oracle table 'Dept'

' open connection
Set oCon_orcl = WScript.CreateObject("ADODB.Connection")
oCon_orcl.Open "DSN=orcl;User Id=SCOTT;Password=tiger;"

' create and open a record set object
Set oRs_Dept = WScript.CreateObject("ADODB.Recordset")
oRs_Dept.CursorLocation = adUseClient
oRs_Dept.Source = "SELECT DEPTNO, DNAME, LOC FROM DEPT WHERE 1 = 0"
oRs_Dept.ActiveConnection = oCon_orcl
oRs_Dept.CursorType = adOpenKeyset
oRs_Dept.LockType = adLockBatchOptimistic
oRs_Dept.Open

oRs_Dept.AddNew Array("DEPTNO","DNAME","LOC"), Array(50, "PERSONEL","ORLEANS")

oRs_Dept.AddNew
oRs_Dept("DEPTNO") = 60
oRs_Dept("DNAME") = "SECURITY"
oRs_Dept("LOC") = "EVERYWHERE"

oRs_Dept.UpdateBatch
oRs_Dept.Close
oCon_orcl.Close


Tom,

You don't mind me filling your forum with lots of junk not related to oracle, do you?


Gj

Why thank you! I had no idea I was held in such high regard! ;-)

Hi Tom,
I'm facing problem regarding XML, too. We have developed nice interface: flat file <-> database for external systems. We load the file into database using external table, optionally (if file is big) into new partition.
Now we have to communicate with some external systems via XML. I do not want to load xml into table with clob or xml type, then parse and load into target table (XML file might be big!). I want to keep the same external table loading strategy as we have now.
Is it possible to use external table with XML file?
Any trick or whatever?

Thanks,

Dushan

Tak tang,

Thanks for your time.

But It is not a simple insert. i need to do various validations etc.. so, i have to call a stored procedure.

anyway thanks.

Tom,

what i asked is not ASP specific,
what i asked was how can we from any front end(other than Oracle forms) pass multiple records to the Oracle stored procedure?
we tried with ASP, ASP.NET.

we were not able to send multiple records to oracle.No reason to blame ASP here, because it is a driver issue. Even Oracle's ODBC driver is not supporting PL/SQL tables.

I am not sure, if we can do this FROM JSP..

John,

Oracle Objects For OleDB (OO4O) supports passing tables as parameters. Use it in place of ADO, although since the object model is a bit different, it is not a simple 'drop in' replacement. You probably already have it installed without knowing. %ORACLE_HOME%/OO4O/

Takmeister

Dear Tom,
I need to update some XML Schema. Now some of my XML tags contains CDATA.
For example,

select a.xml_column.getStringval() result from accounts a where acc_id =
'AA001';

RESULT
------
<customer>
<city><![CDATA[HYDERABAD]]></city>
</customer>

NOW While Updating the city value to "CUTTACK" i am getting the error,

update t set xml_column = updatexml(xml_column,
'/customer/residenceAddress/city/text()', 'CUTTACK') where acc_id = 'AA001';

update accounts set profile = updatexml(profile,
'/customer/residenceAddress/city/text()', 'CUTTACK'
*
ERROR at line 1:
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00210: expected '<' instead of 'C'
Error at line 1

Please suggest what should i do to get rid of it.

I asked Sean Dillon, our XML technologist, to take a look at this and here's what he had to say:

--

Hi Sujit,

Two things.  First, you have an error in your xpath expression so even if this didn't throw an error (which it shouldn't, regardless of the xpath expr), nothing would be updated b/c there is no /customer/residenceAddress/city/text() value in your document sample.

That being said... you've happened upon bug #3028534.  It's fixed in 10.1, and there is no backport.  On my 10g db, it works just fine:

ops$sdillon@FUNKDB> create table xmldocs ( x xmltype );
Table created.
 
ops$sdillon@FUNKDB> insert into xmldocs values (xmltype('<customer>
  2  <city><![CDATA[HYDERABAD]]></city>
  3  </customer>'));
1 row created.
 
ops$sdillon@FUNKDB> select * from xmldocs;
X
-----------------------------------------------------------------------------------------------------------------------------------
<customer>
<city><![CDATA[HYDERABAD]]></city>
</customer>
 
-- updates regardless, but there is no node that satisfies that xpath expr so
-- nothing changes in the row
ops$sdillon@FUNKDB> update xmldocs set x = updatexml(x, '/customer/residenceAddress/city/text()', 'CUTTACK');
1 row updated.
 
ops$sdillon@FUNKDB> update xmldocs set x = updatexml(x, '/customer/city/text()', 'CUTTACK');
1 row updated.
 
ops$sdillon@FUNKDB> select * from xmldocs;
X
-----------------------------------------------------------------------------------------------------------------------------------
<customer>
  <city>CUTTACK</city>
</customer>

Maybe time for an upgrade?  :)

If you had to do this in 9i, I would probably extract the contents of the XML to a CLOB, use SUBSTR and INSTR to find the CDATA value, replace it w/ REPLACE and recreate the XMLType fm the resulting clob.  It's the long way around, but you can do it in 9i.

Hope that helps! 

what option in .xsd file created following(In your example 

https://asktom.oracle.com/Misc/oramag/on-procedures-flushes-and-writes.html

,when I create it has one field with xmltype as data type.Couldn't see your .xsd file.Will appreciate if you explain this.

SQL> describe XDBPO_TYPE
XDBPO_TYPE is NOT FINAL
Name                        Null?                       Type

----------------------      --------------------       ------------------
SYS_XDBPD$                                  
XDB.XDB$RAW_LIST_T
Reference                                           VARCHAR2(30)
ACTIONS                                     
XDBPO_ACTIONS_TYPE
REJECTION                                  

XDBPO_REJECTION_TYPE
Requestor                                           VARCHAR2(128)
User                                                VARCHAR2(10)
CostCenter                                          VARCHAR2(4)
SHIPPINGINSTRUCTIONS                       
XDBPO_SHIPINSTRUCTIONS_TYPE
SpecialInstructions                                 VARCHAR2(2048)
LINEITEMS                                   

XDBPO_LINEITEMS_TYPE

Cheers 

Dear Tom
The example was very instructive.

When i tried to give the following quering it is giving problem.

create table test(
filename varchar(200),
xlmdoc xmltype);

insert into test(filename, xmldoc)
values ('1992004', xmltype.reatexml(getdocument('XMLPATH','1992004.xml')));

I get the following exception:

PRA-31001: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00217: invalid character 8240 (\u2030)
Error at line 44
ORA-06512: at"SYS.XMLTYPE", line 5

the special character in the document are promille (&#x2030), alpha - theta.
My document encoding is ISO-8859-1.

Any trick or whatever?

Thanks,

Rene

Dear Tom
I use the NLS_RDBMS_VERSION 10.1.0.2.0 .

Thanks,

Rene

**********************1994002.xml***********************
<?xml version="1.0" encoding="ISO-8859-1"?>

<lgbl typ="lgbl" jahr="1994" nr="2">

<kopf>

<dbinfo

ausgabedatum="21. Januar 1994"

inkraftdatum="21. Januar 1994"

vomdatum="23. Dezember 1993"

lrnr="952.012"

erlasstyp="Verordnung"

register_abk_htit1="V"

register_abk_aend="V"/>

<kopf1>Liechtensteinisches Landesgesetzblatt</kopf1>

<kopf2><jahrgang>Jahrgang <jahr>1994</jahr></jahrgang>

<nummer>Nr. <nr>2</nr></nummer>

<ausgegeben>ausgegeben am <ausgabedatum>21. Januar 1994</ausgabedatum></ausgegeben>

</kopf2>

<kl/>

</kopf>

<einleitungsteil>

<erlasstitel>

<htit1 position="htit1:1">Verordnung</htit1>

<vom>vom 23. Dezember 1993</vom>

<htit2 position="htit2:1">über die Einhebung von Gebühren nach dem Bankengesetz</htit2>

</erlasstitel>

<einl position="einl:1">

<ab position="ab:1" style="margin-top:23pt;text-indent:0.5cm">Aufgrund von Art. 30 und 67 Bst. e des Gesetzes vom 21. Oktober 1992 über die Banken und Finanzgesellschaften (Bankengesetz), LGBl. 1992 Nr. 108, verordnet die Regierung:</ab></einl>

</einleitungsteil>

<hauptteil>

<art position="art:1"><artzahl>Art. 1</artzahl>

<sacht>Grundsatz</sacht>

<abs position="abs:1">1) Die dem Bankengesetz unterstellten Unternehmen haben dem Land eine jährliche Aufsichtsgebühr sowie weitere Gebühren zu entrichten.</abs>

<abs position="abs:2"><einleitungssatz>2) Die Gebühren gemäss Abs. 1 setzen sich zusammen aus:</einleitungssatz>

<bst1 position="bst1:a">a) Aufsichtsgebühren;</bst1>

<bst1 position="bst1:b">b) Konzessions- und Bewilligungsgebühren;</bst1>

<bst1 position="bst1:c">c) Gebühren für Entscheidungen, Verfügungen und besondere Dienstleistungen sowie für Bescheinigungen;</bst1>

<bst1 position="bst1:d">d) Gebühren für ausserordentliche Untersuchungen und Revisionen.</bst1></abs>

<abs position="abs:3">3) Die Regierung setzt diese Gebühren so fest, dass sie insgesamt die Kosten der Bankenaufsicht decken.</abs>

<abs position="abs:4">4) Die Gebühren sind an die Landeskasse zu entrichten.</abs></art>

<art position="art:2"><artzahl>Art. 2</artzahl>

<sacht>Aufsichtsgebühr</sacht>

<abs position="abs:1">1) Die Aufsichtsgebühr besteht aus einer festen Grundgebühr und einer vom Bruttoertrag abhängigen Zusatzgebühr.</abs>

<abs position="abs:2">2) Die Grundgebühr beträgt für Banken 10 000 Franken und für Finanzgesellschaften 5 000 Franken.</abs>

<abs position="abs:3">3) Die Zusatzgebühr beträgt für Banken 0.9 &#x2030; und für Finanzgesellschaften 0.6 &#x2030; des Bruttoertrages.</abs>

<abs position="abs:4">4) Die Aufsichtsgebühr wird fällig am Ende des der Zahlungsaufforderung folgenden Monats und im Falle der Anfechtung mit der Rechtskraft der Beschwerdeentscheidung.</abs>

<abs position="abs:5">5) Die Aufsichtsgebühr wird von der Dienststelle für Bankenaufsicht mit einem besonderen Formular erhoben.</abs></art>

<art position="art:3"><artzahl>Art. 3</artzahl>

<sacht>Konzessions- und Bewilligungsgebühr</sacht>

<abs position="abs:1"><einleitungssatz>1) Die Gebühr für die Erteilung einer Konzession oder Bewilligung beträgt:</einleitungssatz>

<bst1 position="bst1:a">a) für Banken 40 000 Franken;</bst1>

<bst1 position="bst1:b">b) für Finanzgesellschaften 20 000 Franken;</bst1>

<bst1 position="bst1:c">c) für Revisionsstellen 4 000 Franken.</bst1></abs>

<abs position="abs:2"><einleitungssatz>2) Die Gebühr für den Entzug oder Widerruf einer Konzession oder Bewilligung beträgt je nach Aufwand:</einleitungssatz>

<bst1 position="bst1:a">a) für Banken 10 000 bis 20 000 Franken;</bst1>

<bst1 position="bst1:b">b) für Finanzgesellschaften 5 000 bis 10 000 Franken;</bst1>

<bst1 position="bst1:c">c) für Revisionsstellen 2 000 bis 4 000 Franken.</bst1></abs>

<abs position="abs:3">3) Notwendige ausserordentliche Aufwendungen können zusätzlich erhoben werden.</abs></art>

<art position="art:4"><artzahl>Art. 4</artzahl>

<sacht>Weitere Gebühren</sacht>

<abs position="abs:1">1) Die Gebühren für weitere Entscheidungen, Verfügungen und besondere Dienstleistungen werden im angemessenen Verhältnis zum Aufwand erhoben.</abs>

<abs position="abs:2">2) Die Gebühr für eine Bescheinigung beträgt bis zu 500 Franken.</abs></art>

<art position="art:5"><artzahl>Art. 5</artzahl>

<sacht>Ausserordentliche Untersuchungen und Revisionen</sacht>

<abs position="abs:1">Für ausserordentliche Untersuchungen und Revisionen mit zusätzlichem Revisions- und Kontrollaufwand können Gebühren im angemessenen Verhältnis zum Aufwand erhoben werden, sofern die Banken und Finanzgesellschaften hierzu Anlass gegeben haben.</abs></art>

<art position="art:6"><artzahl>Art. 6</artzahl>

<sacht>Vorschüsse</sacht>

<abs position="abs:1">In bankengesetzlichen Konzessions- und Bewilligungsverfahren sowie im Rahmen der Bankenaufsicht können Vorschüsse auf Kosten und Gebühren verlangt werden.</abs></art>

<art position="art:7"><artzahl>Art. 7</artzahl>

<sacht>Übrige Verfahrenskosten</sacht>

<abs position="abs:1">Für die übrigen Verfahrenskosten und Gebühren gelten die Bestimmungen des Landesverwaltungspflegegesetzes (LVG).</abs></art>

<art position="art:8"><artzahl>Art. 8</artzahl>

<sacht>Inkrafttreten</sacht>

<abs position="abs:1">Diese Verordnung tritt am Tage der Kundmachung in Kraft.

</abs></art>

</hauptteil>

<schlussteil position="schlussteil:1">

<gezr position="gezr:1"><nl position="nl:1">Fürstliche Regierung:</nl><nl position="nl:2">gez. <name>Dr. Mario Frick</name></nl><nl position="nl:3">Fürstlicher Regierungschef</nl></gezr>

</schlussteil>

<anhangteil>

</anhangteil>

<fussnotenteil>

</fussnotenteil>

</lgbl>

****************************************************

Hello Tom/Sean,

Im looking for some insight into two bugs that I have seen.

The error java.lang.ArrayIndexOutOfBoundsException seems to be a but when you try to parse more then 2048 XML files.
--Supposedly the fix for this was to install the
9I XDK into the 8.1.7.4 database.

but once you do the above you get the below error.

The java.lang.OutOfMemoryError error that occors when you parse a large number of XML files is an issue in an 8.1.7.4 XDK.
-- Now supposedly the fix for this was to upgrade to
the 9.2.0.2 database because its XDB does not use
java but uses PL/SQL.

also Ive seen to increase the java_session_memory parameter but that does not make a difference because it just reaches that setting and trys to go pass that also.

Has anybody had any success with this?
It seems as though the actually number of XML files are around 999 befoure I get a Java error. If you create a new session it seems the "memory leak" is gone.

I need help to resolve this WITHOUT going to 9.2.0.6. Going to the 9i XDK is a option im fighting for but dont really have the approval yet.

Tom do you feel from your experience that if you have MANY or LARGE XML files to do that processing outside of the DB or at "OFFPEAK" hours?

Thanks,
CG

I started off encountering bug#1533180 which stated the fix was to "Upgrade to XML Developer's Kit for PL/SQL 9"
Having stated that here are comments to your reply......

Can you tell me how you're parsing your XML?
**** I am using XDK for PL/SQL in an 8.1.7.4.17 Server
I create my doc from calling getXML then apply XSL to
it. Also I use XMLDOM API.
If you upgraded to 9.2.0.2 to use
PL/SQL instead of Java, why would you be receiving a java.lang.OutOfMemoryError?
**** No, I didnt upgrade to 9i Server I INSTALLED the 9i XDK into my 8.1.7 Server (downloaded from otn).
AFTER doing this I received the OutOfMemoryError (looked it up on metalink and found Bug#2945939 )

Have you logged a TAR? If so, what's the TAR# so I can look at it?
*** Did not log a TAR, thought all they would tell me was the fix was to upgrade.

If you can't upgrade, and you can work around it by
creating a new session, I'd probably opt to do that.

***The "configuration control" heads are reluctant to upgrade the DB because they (and obviously dont have knowledgeable Oracle people to help resolve issues)
1. They MAY agree to load 9.2.0.6 XDK into 8.1.7 Server
2. Cant create a session in PL/SQL so I got around the error
by running the process through bat file and SQL*Plus.
3. They DEFINITELY are more reluctant to migrate to 9i Serv
4. All this "Fear" would be resolved by upgrading to 9i Server. I read the XDB is better because it it written in C instead of Java. And doing XML stuff is much easier.

On the other hand, if you
have an application server or better yet a farm of application servers, then you
may want to move the parsing out to the middle tier. This is the joy of the XDK
and 3-tier processing, you get to decide where you want the work to be done (for
the most part).
*** I am leaning this way but because of how the configuration is of the system I dont know if I can load the external JVM on the machine. (only want to do this IF the Oracle JVM has issues, I want to have confidence in Oracles JVM)

The application is creating HL7 messages and sending them to a table and the are removed using AQ (current config).
Next configuration XML will be written to the file system.

Hi Tom,
I am trying to update(insert/update/delete) the element of a xlm document stored in a xmltype column.
I would like to use Java (Jdbc) and the oracle.xdb.XMLType class.
I have found an example in the Oracle9i XML Database Developer's guide - Oracle Xml DB, release 2 (9.2.0.2).
Java DOM API for XMLType Example 9-6.
I don't Know What is the Jar for oracle.xdb.XMLType.
In the example is named the oraxdb.jar but I haven't found it.
Is oraxdb.jar the jar for oracle.xdb.XMLType class?
Where is oraxdb.jar?
Can I download it?
Thank you for your help.

Hi Tom,
Greetings!! I am facing similar problems that other users here had mentioned while loading the xml file into the Oracle 9ir2 Database.

Here are the steps that I did.

dba>create directory sampledir as '/home/utlfiledir/hsus/samplexml/';

dba>grant read on directory sampledir to public with grant option;
dba>alter session set query_rewrite_enabled=true;
dba>ALTER SESSION SET query_rewrite_integrity=trusted;

dba>create table samplexml (id number, name varchar2(500), doc xmltype);

create or replace function getsamplxml(filename varchar2)
2 return CLOB deterministic
3 is
4 file bfile := bfilename ('SAMPLEDIR',filename);
5 charContent CLOB := ' ';
6 targetFile bfile;
7 warning number;
8 begin
9 targetFile := file;
10 DBMS_LOB.fileopen(targetFile, DBMS_LOB.file_readonly);
11 DBMS_LOB.loadfromFile(charContent,targetFile,
12 DBMS_LOB.getLength(targetFile),1,1);
13 DBMS_LOB.fileclose(targetFile);
14 return charContent;
15* end;
dba>/

Function created.

dba>create index samplexml_indx on samplexml(doc) indextype is ctxsys.context parameters('section group ctxsys.auto_section_group');

Index created.

Insert into samplexml values(sample_seq.nextval, 'SCIENCE', xmltype(GETSAMPLXML('Sample_CAM.xml')));

ERROR at line 1:
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00210: expected '<' instead of '<'
Error at line 1
ORA-06512: at "SYS.XMLTYPE", line 0
ORA-06512: at line 1

I thought the problem might be with the indexes, so I rebuild the indexes.

dba>alter index samplexml_indx rebuild online parameters('sync');

Index altered.

Even after that, I am not able to load the xmlfile into the database.

The schema in which i am loading has the Connect, Resource and CTXAPP roles granted.

The worse case is, I am not able to load even a small simple xml file with just few tags in it.

Please need ur guidance in this.

Thanks and regards,
David.

ops$tkyte-ORA9IR2> create table samplexml (id number, name varchar2(500), doc xmltype);

Table created.

ops$tkyte-ORA9IR2>
ops$tkyte-ORA9IR2> create or replace function getsamplxml(filename varchar2)
  2  return CLOB deterministic
  3  is
  4  begin
  5          return '<tag>Hello</tag>';
  6  end;
  7  /

Function created.

ops$tkyte-ORA9IR2>
ops$tkyte-ORA9IR2> create index samplexml_indx on samplexml(doc) indextype is ctxsys.context
  2  parameters('section group ctxsys.auto_section_group');

Index created.

ops$tkyte-ORA9IR2>
ops$tkyte-ORA9IR2> insert into samplexml values( 1, 'x', xmltype(GETSAMPLXML('Sample_CAM.xml')));

1 row created.


try something simple first, get it as small (yet 100% complete, 100% complete) so anyone can run it. 

Hi,

Thank u very much for ur quick response. The funtion that u'd created works fine also the insert statements are going well.

BUT, when u query against the table, we are not getting the desired result.

I loaded a XML file which actually DOES NOT exits in the file Location.

INSERT INTO DOC_TABLE VALUES ('x','y',xmltype(GETSAMPLXML('David.xml')));

David.xml doesnt exist in the system anywhere, so the functin has to return "Hello" right?

here is the query;

select
2 a.doc_id,
3 extractvalue(a.doc_content,'tag')
4 from
5 DOC_TABLE a where
6* contains(doc_content,'Hello')>0
dba>/

no rows selected


I tried with another insertion of Valid xml file and when i queried against it..I get ALL the rows as output.

1 SELECT doc_id, doc_type
2 FROM DOC_TABLE
3* WHERE CONTAINS(doc_CONTENT, 'population') >= 0
4 /

DOC_ID D
-------------------- -
x y
1 3
x y


Can u please suggest also, why my procedure that I created earlier is not working ??

Thanks for ur time and help.
David.

Hi Sean/Tom,

For my system I need good query performance, so after reading Sean’s article (excellent by the way), I think Objects is the way to.

I have a couple of questions though, when using this storage, do we get a table whose content is objects? Meaning each entry (row) on that table is and XML object type? And every time we read a new XML file it will create a new record on the table? Or do get a new structure (table) for every new file? I have not done much work with objects in Oracle, as you can tell, but I do understand the main concept.

Also, if the XML schema changes (for this system this could happen 3-4 times a year), I assume the process would take care of creating the right object, but when querying the table, or object, I assume we need to change our queries accordingly, can we do that somehow dynamically?

Finally, if all the XML objects go into the same XML table, and the XML schema changes, would they be treated just like any other table where records might have different length, (due to diff schemas)?

Please let me know your comments/ideas and point me to any extra documentation you can think of, besides the 998 page XML DB manual (very good by the way).

Thanks,

Orlando

I was afraid of that

Does it mean that if the XML Schema ever changes, then using this approach should not be consider at all? Also, is the supporting structure (the one generated by Oracle) recreated every time we load a new XML file if the schema have changed?

Could you please elaborate a little bit more on how the loading process behaves if the XML schema changes?

As usual, thanks a lot and have a great day.

Orlando

We are running a 10.2.0.3.0 database however the Pro*C version is 9.2.0.7

Can you please tell me if the datatype XMLTYPE is available to the Pro*C compiler?

Thanks in advance
Mike

Hi Tom,

We want to use XML for printing xml and reprinting them. What would be the best way of implementing this ? Whether we should generate XML in oracle or front end application like Java, C#, etc. Should we store this XML in the database or store just the data ? If yes then what would be the recommended type of storage... xmltype, blob, clob ? Should there be any performance hindrance if a process prints 1000 of receipts converts the xml into pdf and then prints..Any reference material and tutorial would be of great help to me.

Regards,
Piyush

Hi Tom,
Thanks for prompt reply.

We are using XSL stylesheet with XML. This means that xml generation should be handled by Java and not Oracle. Also there is no need to store the XML as it can be regenerated.
Have I understood correctly ?

Moreover, please tell me the way I can access the elements stored in an xmltype / clob having xml values in a table column... How to manipulate and access them. I read something about XQuery ? Would this help ? If yes, then can you please provide me some useful links which has got some examples in it.

Regards,
Piyush