asked on

How to load xml data in excess of 4,000 characters to an xmltype column in a table.

Using xmlspy, I created a sample xml document from a valid schema definition to use as test data for an Oracle database (11.2.0.4). However, the resulting document is substantially large and I am running into a "string literal too large" error when trying to load it. I have searched and searched for a possible solution and none of them seem to work. Has anyone at EE been able to load a large (>32 kb) text file into an xmltype column? If so, how?

slightwv (䄆 Netminder)

I use a PL/SQL block and CLOB:

declare
    junk clob;
begin
   junk := '<root>';
  junk := junk || '<node>Hello</node>';
...repeat 
  junk := junk || '</root>';

insert into some_table values(xmltype(junk));
end;
/

Open in new window

awking00

ASKER

Slightwv,
Thanks for your prompt response. That is similar to what I have tried (see code block), but still got the string literal too long message.

SQL> desc xmltbl_stg
 Name                                      Null?    Type
 ----------------------------------------- -------- 
 ID                                                 NUMBER
 XML_DATA                                           SYS.XMLTYPE STORAGE BINARY


declare
id pls_integer := 1;
x clob;
begin
x := 'extremely long xml string';
insert into xmltbl_stg values(id, xmltype(x));
end;
/


ERROR at line 5:
ORA-06550: line 5, column 6:
PLS-00172: string literal too long

Open in new window

I'm not sure how I can relate '<node>Hello</node>' to my scenario as there are hundreds of nodes with multiple levels in the xml. I'm also not sure of the meaning of ...repeat. I'm only trying to insert one record.

slightwv (䄆 Netminder)

>>x := 'extremely long xml string';

You cannot have a single string > 4000 characters.

>> I'm also not sure of the meaning of ...repeat.

Repeat the string concatenation until you have the entire XML doc in the clob, one NODE at a time.

Here is a complete example that ends up with a 4,597 character CLOB

drop table tab1 purge;
create table tab1(id number, xml_data xmltype);

declare
	id pls_integer := 1;
	x clob;
begin
	x := '<root>';
	x := x || '<node1>Hello</node1>';
	x := x || '<node2>Hello</node2>';
	x := x || '<node3>Hello</node3>';
	x := x || '<node4>Hello</node4>';
	x := x || '<node5>Hello</node5>';
	x := x || '<node6>Hello</node6>';
	x := x || '<node7>Hello</node7>';
	x := x || '<node8>Hello</node8>';
	x := x || '<node9>Hello</node9>';
	x := x || '<node10>Hello</node10>';
	x := x || '<node11>Hello</node11>';
	x := x || '<node12>Hello</node12>';
	x := x || '<node13>Hello</node13>';
	x := x || '<node14>Hello</node14>';
	x := x || '<node15>Hello</node15>';
	x := x || '<node16>Hello</node16>';
	x := x || '<node17>Hello</node17>';
	x := x || '<node18>Hello</node18>';
	x := x || '<node19>Hello</node19>';
	x := x || '<node20>Hello</node20>';
	x := x || '<node21>Hello</node21>';
	x := x || '<node22>Hello</node22>';
	x := x || '<node23>Hello</node23>';
	x := x || '<node24>Hello</node24>';
	x := x || '<node25>Hello</node25>';
	x := x || '<node26>Hello</node26>';
	x := x || '<node27>Hello</node27>';
	x := x || '<node28>Hello</node28>';
	x := x || '<node29>Hello</node29>';
	x := x || '<node30>Hello</node30>';
	x := x || '<node31>Hello</node31>';
	x := x || '<node32>Hello</node32>';
	x := x || '<node33>Hello</node33>';
	x := x || '<node34>Hello</node34>';
	x := x || '<node35>Hello</node35>';
	x := x || '<node36>Hello</node36>';
	x := x || '<node37>Hello</node37>';
	x := x || '<node38>Hello</node38>';
	x := x || '<node39>Hello</node39>';
	x := x || '<node40>Hello</node40>';
	x := x || '<node41>Hello</node41>';
	x := x || '<node42>Hello</node42>';
	x := x || '<node43>Hello</node43>';
	x := x || '<node44>Hello</node44>';
	x := x || '<node45>Hello</node45>';
	x := x || '<node46>Hello</node46>';
	x := x || '<node47>Hello</node47>';
	x := x || '<node48>Hello</node48>';
	x := x || '<node49>Hello</node49>';
	x := x || '<node50>Hello</node50>';
	x := x || '<node51>Hello</node51>';
	x := x || '<node52>Hello</node52>';
	x := x || '<node53>Hello</node53>';
	x := x || '<node54>Hello</node54>';
	x := x || '<node55>Hello</node55>';
	x := x || '<node56>Hello</node56>';
	x := x || '<node57>Hello</node57>';
	x := x || '<node58>Hello</node58>';
	x := x || '<node59>Hello</node59>';
	x := x || '<node60>Hello</node60>';
	x := x || '<node61>Hello</node61>';
	x := x || '<node62>Hello</node62>';
	x := x || '<node63>Hello</node63>';
	x := x || '<node64>Hello</node64>';
	x := x || '<node65>Hello</node65>';
	x := x || '<node66>Hello</node66>';
	x := x || '<node67>Hello</node67>';
	x := x || '<node68>Hello</node68>';
	x := x || '<node69>Hello</node69>';
	x := x || '<node70>Hello</node70>';
	x := x || '<node71>Hello</node71>';
	x := x || '<node72>Hello</node72>';
	x := x || '<node73>Hello</node73>';
	x := x || '<node74>Hello</node74>';
	x := x || '<node75>Hello</node75>';
	x := x || '<node76>Hello</node76>';
	x := x || '<node77>Hello</node77>';
	x := x || '<node78>Hello</node78>';
	x := x || '<node79>Hello</node79>';
	x := x || '<node80>Hello</node80>';
	x := x || '<node81>Hello</node81>';
	x := x || '<node82>Hello</node82>';
	x := x || '<node83>Hello</node83>';
	x := x || '<node84>Hello</node84>';
	x := x || '<node85>Hello</node85>';
	x := x || '<node86>Hello</node86>';
	x := x || '<node87>Hello</node87>';
	x := x || '<node88>Hello</node88>';
	x := x || '<node89>Hello</node89>';
	x := x || '<node90>Hello</node90>';
	x := x || '<node91>Hello</node91>';
	x := x || '<node92>Hello</node92>';
	x := x || '<node93>Hello</node93>';
	x := x || '<node94>Hello</node94>';
	x := x || '<node95>Hello</node95>';
	x := x || '<node96>Hello</node96>';
	x := x || '<node97>Hello</node97>';
	x := x || '<node98>Hello</node98>';
	x := x || '<node99>Hello</node99>';
	x := x || '<node100>Hello</node100>';
	x := x || '<node101>Hello</node101>';
	x := x || '<node102>Hello</node102>';
	x := x || '<node103>Hello</node103>';
	x := x || '<node104>Hello</node104>';
	x := x || '<node105>Hello</node105>';
	x := x || '<node106>Hello</node106>';
	x := x || '<node107>Hello</node107>';
	x := x || '<node108>Hello</node108>';
	x := x || '<node109>Hello</node109>';
	x := x || '<node110>Hello</node110>';
	x := x || '<node111>Hello</node111>';
	x := x || '<node112>Hello</node112>';
	x := x || '<node113>Hello</node113>';
	x := x || '<node114>Hello</node114>';
	x := x || '<node115>Hello</node115>';
	x := x || '<node116>Hello</node116>';
	x := x || '<node117>Hello</node117>';
	x := x || '<node118>Hello</node118>';
	x := x || '<node119>Hello</node119>';
	x := x || '<node120>Hello</node120>';
	x := x || '<node121>Hello</node121>';
	x := x || '<node122>Hello</node122>';
	x := x || '<node123>Hello</node123>';
	x := x || '<node124>Hello</node124>';
	x := x || '<node125>Hello</node125>';
	x := x || '<node126>Hello</node126>';
	x := x || '<node127>Hello</node127>';
	x := x || '<node128>Hello</node128>';
	x := x || '<node129>Hello</node129>';
	x := x || '<node130>Hello</node130>';
	x := x || '<node131>Hello</node131>';
	x := x || '<node132>Hello</node132>';
	x := x || '<node133>Hello</node133>';
	x := x || '<node134>Hello</node134>';
	x := x || '<node135>Hello</node135>';
	x := x || '<node136>Hello</node136>';
	x := x || '<node137>Hello</node137>';
	x := x || '<node138>Hello</node138>';
	x := x || '<node139>Hello</node139>';
	x := x || '<node140>Hello</node140>';
	x := x || '<node141>Hello</node141>';
	x := x || '<node142>Hello</node142>';
	x := x || '<node143>Hello</node143>';
	x := x || '<node144>Hello</node144>';
	x := x || '<node145>Hello</node145>';
	x := x || '<node146>Hello</node146>';
	x := x || '<node147>Hello</node147>';
	x := x || '<node148>Hello</node148>';
	x := x || '<node149>Hello</node149>';
	x := x || '<node150>Hello</node150>';
	x := x || '<node151>Hello</node151>';
	x := x || '<node152>Hello</node152>';
	x := x || '<node153>Hello</node153>';
	x := x || '<node154>Hello</node154>';
	x := x || '<node155>Hello</node155>';
	x := x || '<node156>Hello</node156>';
	x := x || '<node157>Hello</node157>';
	x := x || '<node158>Hello</node158>';
	x := x || '<node159>Hello</node159>';
	x := x || '<node160>Hello</node160>';
	x := x || '<node161>Hello</node161>';
	x := x || '<node162>Hello</node162>';
	x := x || '<node163>Hello</node163>';
	x := x || '<node164>Hello</node164>';
	x := x || '<node165>Hello</node165>';
	x := x || '<node166>Hello</node166>';
	x := x || '<node167>Hello</node167>';
	x := x || '<node168>Hello</node168>';
	x := x || '<node169>Hello</node169>';
	x := x || '<node170>Hello</node170>';
	x := x || '<node171>Hello</node171>';
	x := x || '<node172>Hello</node172>';
	x := x || '<node173>Hello</node173>';
	x := x || '<node174>Hello</node174>';
	x := x || '<node175>Hello</node175>';
	x := x || '<node176>Hello</node176>';
	x := x || '<node177>Hello</node177>';
	x := x || '<node178>Hello</node178>';
	x := x || '<node179>Hello</node179>';
	x := x || '<node180>Hello</node180>';
	x := x || '<node181>Hello</node181>';
	x := x || '<node182>Hello</node182>';
	x := x || '<node183>Hello</node183>';
	x := x || '<node184>Hello</node184>';
	x := x || '<node185>Hello</node185>';
	x := x || '<node186>Hello</node186>';
	x := x || '<node187>Hello</node187>';
	x := x || '<node188>Hello</node188>';
	x := x || '<node189>Hello</node189>';
	x := x || '<node190>Hello</node190>';
	x := x || '<node191>Hello</node191>';
	x := x || '<node192>Hello</node192>';
	x := x || '<node193>Hello</node193>';
	x := x || '<node194>Hello</node194>';
	x := x || '<node195>Hello</node195>';
	x := x || '<node196>Hello</node196>';
	x := x || '<node197>Hello</node197>';
	x := x || '<node198>Hello</node198>';
	x := x || '<node199>Hello</node199>';
	x := x || '<node200>Hello</node200>';
	x := x || '</root>';


	dbms_output.put_line('Len: ' || dbms_lob.getlength(x));

	insert into tab1 values(id, xmltype(x));
	commit;
end;
/

Open in new window

This question needs an answer!

Become an EE member today

7 DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform.

View membership options

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.