Search code examples
xmlimportdb2loaddb2-luw

DB2 Import (Raw) XML Data

Is there a way to import (or load) raw xml data into a table with a column of type xml.

<customerinfo Cid="1000"><name>Kathy Smith</name><addr country="Canada"><street>5 Rosewood</street><city>Toronto</city><prov-state>Ontario</prov-state><pcode-zip>M6W 1E6</pcode-zip></addr><phone type="work">416-555-1358</phone></customerinfo>
<customerinfo Cid="1001"><name>Kathy Smith</name><addr country="Canada"><street>25 EastCreek</street><city>Markham</city><prov-state>Ontario</prov-state><pcode-zip>N9C 3T6</pcode-zip></addr><phone type="work">905-555-7258</phone></customerinfo>
...

Each Row of the xml Document should be place in a row.

import from C:\temp\test.xml OF DEL INSERT INTO XMLCOLUMN

DB2 reads the XML file correct but failes to import it into the rows...

Number of rows read = 6
Number of rows skipped = 0
Number of rows inserted = 0
Number of rows updated = 0
Number of rows rejected = 6
Number of rows = 6

Any ideas?

Solution

  • You need to have a DEL file containing a description of where to find the XML file or files. Try something like:

    test.del:

    <XDS FIL='test.xml' />

    If you have additional columns, the above XDS would be a regular column value next to the other values.

    test.xml: your XML file

    Then 

    import from test.del OF DEL INSERT INTO MyTable

    "MyTable" is your table with the XML column. See the DB2 docs for more examples.