hi guys,

i created a procedure which inserts xml data into a table but i couldn't find any direct conversion mechanism to xml from char.

can anybody suggest me how to do this.

procedure is

CREATE PROCEDURE UPMDATAPOPULATIONTEST2(IN startId INTEGER,IN endId INTEGER)
LANGUAGE SQL BEGIN
DECLARE v_startid INTEGER;
DECLARE v_endid INTEGER;
DECLARE v_recid VARCHAR(255);
DECLARE v_temp XML;
SET v_startid = startId;
SET v_endid = endId;
WHILE(v_startid <= v_endid)
DO
SET v_recid =CHAR(v_startid);
SET v_temp = ' <row id="initialId"><c1>initialValue</c1><c2>1stNode</c2><c3>2ndNode</c3><c4>3rdNode</c4><c5>4thNode</c5></row>';
insert into test_table values(v_recid,v_temp);
SET v_startid = v_startid+1;
END WHILE;
COMMIT;
END
----------------------------------------------------------------------------------------------------------------------------------------------

for v_temp what i have to add to make it as XML compatible.

Check out XMLPARSE()

hi
thank you for your response

i tried with that

but the main problem here is when i insert data(char type) into xml type column i.e for 1 million rows its taking nearly 7 minutes
but if i insert any other type of data(integer or char) into that data type(integer or char) column its taking 52 seconds only for 1million rows
that's why i thought that problem is in xml conversion.

for that only i am searching for process of converting text to xml

7 minutes divided by 1 million rows is about 0.4 msec. I say it's pretty darn good for an XML parser. If you don't want to spend time parsing character data into XML, store your fragments as VARCHAR.

hi thank you for reply, but the problem here is

when i insert data through java code its taking 52 seconds only for inserting 1million rows

in java code there is a mechanism to create an object of type xml for db2 database through that i am inserting xml data, its giving a fine result but to do some enhancements i want to use procedures

I thought you had 52s when inserting the data as VARCHAR only, which doesn't involve XML parsing. Could you lay out which scenarios you tried and what you have measured so far? It is a bit confusing to me.

hi, thanks for the reply

let me explain properly

through Java code:
Constructs an object that implements the SQLXML interface, through a method i am passing chartype of data which is in XML format then i am inserting data into tables. for this its taking 52 seconds

through procedure:
created a variable of type XML
for that i am assigning data which is in XML format then
using that variable inserting the data into table(with out using any casting and by using some type of XML castings like XMLPARSE)
for this type of insertion its taking more than 7 mins

When you go the "Java code" route, do you insert the data into a column of type XML or (VAR)CHAR?

XML type column

XML type column

I have no good answer for you - short of a few guesses:
(1) your measurement in the 1st case may not include the XML parsing and the construction of the XML value; if it measures only the INSERT statement, this would be an explanation
(2) you have completely different hardware (slow server vs. fast client)
(3) when you do the XML parsing, you don't do validation against a schema etc. but DB2 does it (in that case, you won't have a fair comparison)

If that's not it, then there may be potential in DB2 to improve the XML parsing performance...