Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2015
    Posts
    2

    Unanswered: Loading xml data into a db2 table

    Hello!!

    I am new to using xml.
    I am required to load the data from xml to a table in the DB2 database.
    Basically, I have to get tag values from the xml and insert them into the columns in a DB2 table.

    I looked up online and I think most of the people load the xml itself in a xml type column in a table first and then use this column to get tag values and insert those into a final table.
    Is there a way to load the tag values directly from xml file to the columns in a table? I am using Linux shell script.

    Since most of the examples I found online were loading the xml first in a xml type column of a table, I tried to do that as well.
    so I created a temporary table .. let's call it TEMP_TABLE.
    So I did the following:
    CREATE TABLE TEMP_TABLE(id int generated always as identity, actual_xml xml)
    Then I tried to load the table:
    INSERT INTO TEMP_TABLE(actual_xml) values('file.xml')
    But this gives me an error message:
    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL16132N XML document contains an invalid document structure.
    SQLSTATE=2200M

    I checked that xml file does not has comments. I tried with few different xml files but got the same error message.
    Can someone please help me figure out what am I doing wrong here?
    This is the simplest xml file that I tried:
    <?xml version="1.0" encoding="ISO8859-1" ?>
    - <note>
    <to>Tove</to>
    <from>Jani</from>
    <heading>Reminder</heading>
    <body>Don't forget me this weekend!</body>
    </note>

    Please advise me .. Thanks!
    Last edited by nptfdo; 07-15-15 at 01:52.

  2. #2
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hello,

    Place the following content into some /fullpath/file.xml file (without a dash in the 2-nd line, otherwise it's really a document with incorrect structure):
    Code:
    <?xml version="1.0" encoding="ISO8859-1" ?>
    <note>
    <to>Tove</to>
    <from>Jani</from>
    <heading>Reminder</heading>
    <body>Don't forget me this weekend!</body>
    </note>
    Then create a file file.txt with a single line:
    Code:
    file.xml
    Try this:
    Code:
    $ db2 "import from /fullpath/file.txt of del modified by lobsinfile insert into temp_table(actual_xml)"
    To get values from xml documents loaded you can use the following query:
    Code:
    select t.id, x.* 
    from 
      temp_table t
    , xmltable ('$d/note' passing t.actual_xml as "d"
    columns 
      TO   VARCHAR(20) path 'to'
    , FROM VARCHAR(20) path 'from'
    , HEAD VARCHAR(20) path 'heading'
    , BODY VARCHAR(50) path 'body'
    ) x;
    Regards,
    Mark.

  3. #3
    Join Date
    Jul 2015
    Posts
    2
    Thanks for the suggestion Mark! .. it really helped

    Just curious -- Is there any way to load the xml tag values directly to db2 table using Linux/DB2 commands? ..

  4. #4
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55

Posting Permissions

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