CODEWITHSUNDEEP
xmlinsertdb2
user1374266
user1374266

Reputation: 333

storing large xml in db2 xml data type

We have a table with 10 columns, one of the column is of type xml. I wrote an insert statement in db visualizer and tried to insert a record with all the values inline in the insert statement it gives the below error DB2 SQL Error: SQLCODE=-102, SQLSTATE=54002,

I tried by changing the col type to clob and also tried XMLPARSE as shown in the below link but the problem still remains, can you please help.: http://www.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.sql.ref.doc/doc/r0022191.html

Upvotes: 2

Views: 2471

Answers (2)

user1374266
user1374266

Reputation: 333

I wrote a java program to run the insert using jdbc and it worked well. i had to remove tabs, new line characters & escape double quotes from xml to assign it to a string variable.

Upvotes: 1

ChrisHiebert
ChrisHiebert

Reputation: 200

Based on the SQL error, you are limited to 32k of text data with the method you are using.

I found another reference on IBM's knowledgecenter that may help.

Place the XML into a stream file and then use the IMPORT command to input the data into your file.

Excerpt from IBM DB2 Importing XML Data

When importing data into an XML table column, you can use the XML FROM option to specify the paths of the input XML data file or files. For example, for an XML file "/home/user/xmlpath/xmldocs.001.xml" that had previously been exported, the following command could be used to import the data back into the table.

IMPORT FROM t1export.del OF DEL XML FROM /home/user/xmlpath INSERT INTO USER.T1

Upvotes: 2

Related Questions