How to load large XML file (> 100 MB) to an XMLType column in Oracle

Question

Dear All,

I try this command to upload a file (standard.xml) into table "book" the file is very large (>100MB).

insert into book values(1,'Complete Data', XMLType(bfilename('XMLDIR', 'standard.xml'), nls_charset_id('AL16UTF8')));

The problem is after I execute the query above, the XML file is not inserted 100% to the column. There is only a small chunk of the file loaded in the column while the rest is not.

Please help...

Thanks

Answer 1

If you query a large XMLType, the client may only show you the first 'bit' of the stored value. Assuming the column is XMLType, you can be sure that well-formed XML is being stored, so if the small chunk that is visible is not well-formed, it will be the client's fault.

You could use the various functions to count the nodes to see if it matches what you expect.

select count(*)
from xdb.path_view p, table(xmlsequence(extract(p.res,'/*/*'))) y
where p.path= '/sys';