Inserting large xml data into DB2

Question

I have the following situation,

in my database table I have a column for storing xml data. When I try to insert data into this table by using Insert query, I am getting string is too large exception.

I tried keeping the type as XML and VACHAR(32000), I get the same exception.

Can anyone help me with this?

UPDATE

This is the error I am getting while inserting.

The string constant beginning with "'<!--Sample XML file generated by XMLSpy v2013 sp1 (http://www.altova." is too long.. SQLCODE=-102, SQLSTATE=54002, DRIVER=3.59.81

The limit should be 2gb. Are you inserting directly into the database or are you using some kind of application? The application might be truncating it. — Edwin Stoteler
– Edwin Stoteler, Commented Apr 29, 2013 at 14:46

Fred Sobotka · Accepted Answer · 2013-05-10 21:50:27Z

1

The XMLPARSE function is useful for converting text strings into DB2's XML data type. Have you tried wrapping your XML text with a call to XMLPARSE?

If your XML document is in a file, there's a UDF you can add that makes it easier to pull the file contents directly into an XML column.

answered May 10, 2013 at 21:50

Fred Sobotka

5,33225 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

prasad vsv · Accepted Answer · 2015-03-09 08:21:35Z

0

you can set the size of each column in your table. Try changing the default size(which is not max size) to an appropriate one.

Also when you try to query directly using SQL command line, the DBMS has to create a large string constant form its string pool that can hold your XML(which in this case, is very large and hence String cannot be created). If you query the same programmatically though, it works.
If the same error persists or if DBMS throws Data Integrity error, then try changing the data structure to a bigger one like CLOB(most likely in this case) or BLOB(in case of images and multi media).

edited Mar 9, 2015 at 8:21

answered Mar 2, 2015 at 9:12

prasad vsv

1,1688 silver badges10 bronze badges

Comments

Stavr00 · Accepted Answer · 2015-09-09 15:34:59Z

0

It's a bit silly but the answer is use the XMLPARSE and segment your XML string in 32K chunks and prepend a CLOB statement to override the string size limitation

XMLPARSE ( DOCUMENT 
CLOB('<?xml version="1.0"?>') ||
'<aLotOfVeryBigXmlData32kPart1 ......
' ||
'<aLotOfVeryBigXmlData32kPart2 ......
' ||
... etc ...
'<aLotOfVeryBigXmlData32kPartN ......
' )

answered Sep 9, 2015 at 15:34

Stavr00

3,3562 gold badges19 silver badges30 bronze badges

Collectives™ on Stack Overflow

Inserting large xml data into DB2

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related