Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2003
    Location
    Los Angeles
    Posts
    18

    Unanswered: How can I bulk load XML data?

    I have 265,000 files to load in a database table. Each file has an XML like structure and represents one record.

    I need to load all the files today into a table that does not use any XML datatypes: all varchar(265).

    I am using an ETL tool to load the files but it's taken 2 days to load 50,000 files

    How can I do that with it taking weeks to complete?

    I am DB2 LUW v9.7.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Create a temporary table with an XML column, load your XML pieces into it, then use one of the DB2 pureXML functions to populate the target table. If you want more details, provide more details.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Jul 2003
    Location
    Los Angeles
    Posts
    18
    I ran a quick test tlike so

    db2 "create table tmp_xml (filecontents xml)"
    db2 "import from test.del of del xml from xmltest/ insert into tmp_xml"

    and it worked as expected.

    How can use pureXML to populate the target table? I want to "slice" each node of my xml file to load into their corresponding database columns.

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Check the function XMLTABLE - there are examples in the manual. It should look something like:
    Code:
    declare c1 cursor for select whatever from staging_table, xmltable('$XML_COLUMN/xpath/blah' columns ...);
    
    load from c1 of cursor insert into target_table...
    Last edited by n_i; 10-01-12 at 18:26.
    ---
    "It does not work" is not a valid problem statement.

Posting Permissions

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