Skip to Main Content
  • Questions

  • Storage XMLs in Oracle 12cR2 database

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Geraldo.

Asked: August 31, 2018 - 8:58 pm UTC

Last updated: July 08, 2020 - 6:40 am UTC

Version: 12.2.0

Viewed 1000+ times

You Asked

Hello!

I created a database in order to store XMLs. I'm going to storage a large amount of data. I was told that Oracle 12cR2 allows to storage XMLs and handle them very good.

I created a table with a xmltype column.

CREATE TABLE test.invoicexml
(
   id     NUMBER,
   data   xmltype
);


Then I tried to storage a XML in that table (about 4,443 lines, I did not paste it here) through Toad for Oracle and got the following error: 
ORA-01704: string literal too long


Please, I need help with this.

Regards,

and Connor said...

This is more likely to do with how your are presenting the XML to the database and what data types you are using. For large XML, you can use a clob to house the content and then insert into your database, eg

SQL> create table t ( id int, x xmltype);

Table created.

SQL>
SQL> set serverout on
SQL> declare
  2    c clob;
  3  begin
  4    dbms_lob.createtemporary(c,true);
  5
  6    c := '<xml>';
  7    for i in 1 .. 100
  8    loop
  9      c := c || '<tag>'||rpad('blah',1000,'x')||'</tag>';
 10    end loop;
 11    c := c || '</xml>';
 12    dbms_output.put_line('length = '||dbms_lob.getlength(c));
 13
 14    insert into t values (1, xmltype(c));
 15    dbms_lob.freetemporary(c);
 16  end;
 17  /
length = 101111

PL/SQL procedure successfully completed.


Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Excellent

Geraldo Peralta, September 03, 2018 - 1:07 pm UTC

Excellent!

Thanks for the answer.

Having said that, what would be the best approach to insert XMLs into the database from a web application?
Connor McDonald
September 04, 2018 - 5:42 am UTC

A web application must is talking to *something*, ie a program on an application server, or making REST calls, or something. There is support for LOBs in *all* of our interfaces. I was just using a PLSQL block as an example.

eg JDBC

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/jjdbc/LOBs-and-BFiles.html

.Net

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/odpnt/featLOBsupp.html

node

https://blogs.oracle.com/opal/node-oracledb-112:-working-with-lobs-as-string-and-buffer-connection-pinging

and so forth

Thanks!

Geraldo Peralta, September 05, 2018 - 1:28 pm UTC

Thanks for your response. I'm going to take a look on the links you sent me.

Regards,

Another question about storing XMLs

Geraldo Peralta, September 05, 2018 - 7:28 pm UTC

I took a look into the link you sent me:

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/odpnt/featLOBsupp.html#GUID-FDACA2A9-8E29-458B-9EA7-5019587123DF

Must I create my xml column as a CLOB or xmltype. I will handle long XML data. What is the difference between these datatypes?

Thanks in advanced.


Connor McDonald
September 06, 2018 - 1:18 am UTC

A clob is just that - large object of characters. It could contain XML, it could contain anything. All processing on it becomes your responsbility.

Xmltype contains a number of functionality extensions for the manipulation of XML. Under the covers we might *store* it with a clob (or otherwise) but we expose lots of XML specific functionality via API's through the datatype. If they are beneficial for you, then it would make sense to use XMLtype.

If you're wondering how *much* functionality is in XMLtype...well, we had to write an entire book on it :-)

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/adxdb/index.html

But it gives you a lot of flexibility, eg building XML from SQL

SQL> select xmltype(cursor(select empno,ename from scott.emp)) from dual;

XMLTYPE(CURSOR(SELECTEMPNO,ENAMEFROMSCOTT.EMP))
-----------------------------------------------------------------------------
<?xml version="1.0"?>
<ROWSET>
  <ROW>
    <EMPNO>7369</EMPNO>
    <ENAME>SMITH</ENAME>
  </ROW>
  <ROW>
    <EMPNO>7499</EMPNO>
    <ENAME>ALLEN</ENAME>
  </ROW>


and many other goodies

Review

A reader, July 07, 2020 - 1:03 pm UTC

Thanks for the answer. It really helped.

Regards,
Connor McDonald
July 08, 2020 - 6:40 am UTC

glad we could help
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.