Results 1 to 11 of 11
  1. #1
    Join Date
    Jun 2011
    Posts
    6

    Unanswered: xml data insertion into a table through procedure

    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.
    Last edited by rtpenki; 06-06-11 at 06:48.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Check out XMLPARSE()

  3. #3
    Join Date
    Jun 2011
    Posts
    6
    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

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    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.

  5. #5
    Join Date
    Jun 2011
    Posts
    6
    Quote Originally Posted by n_i View Post
    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

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  7. #7
    Join Date
    Jun 2011
    Posts
    6
    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

  8. #8
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    When you go the "Java code" route, do you insert the data into a column of type XML or (VAR)CHAR?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  9. #9
    Join Date
    Jun 2011
    Posts
    6
    XML type column

  10. #10
    Join Date
    Jun 2011
    Posts
    6
    XML type column

  11. #11
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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...
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •