Import command in DB2

In DB2, I have one table:

CREATE TABLE test ( test_id INTEGER NULL, test_name VARCHAR(255) NOT NULL, test_test VARCHAR(255))

Now I开发者_如何学编程 want to import data into this table and used the command

IMPORT from '/home/test.txt' of del MODIFIED BY LOBSINFILE INSERT INTO test

It works fine if the test.txt file contains the below content.

\N, "aaaaaaa", "aaaaaaa"
\N, "bbbbbbb", "bbbbbbb"

But if the text file contains below data, it inserts multiple records.

\N, "<section>\
               <id root=\"84f194ae-f573-4249-8368-e38c74891965\"/>\
               <code code=\"34071-1\" codeSystem=\"2.16.840.1.113883.6.1\" displayName=\"WARNINGS SECTION\"/>\
               <title>Warnings</title>\
               <text>\
                  <paragraph>For external use only, do not use in the eye area.<br/>\
                  </paragraph>\
               </text>\
               <effectiveTime value=\"20100225\"/>\
            </section>","aaaaaaa"
\N, 'bbbbbbb', 'bbbbbb'

What might be wrong?

---

Try escaping the quotes in your data with another quote like this:

\N, "<section>\
               <id root=""84f194ae-f573-4249-8368-e38c74891965""/>\
               <code code=""34071-1"" codeSystem=""2.16.840.1.113883.6.1"" displayName=""WARNINGS SECTION""/>\
           <title>Warnings</title>\
           <text>\
              <paragraph>For external use only, do not use in the eye area.<br/>\
              </paragraph>\
           </text>\
           <effectiveTime value=""20100225""/>\
        </section>","aaaaaaa"

Then use this import syntax:

IMPORT from '/home/test.txt' of del MODIFIED BY DELPRIORITYCHAR XMLPARSE PRESERVE WHITESPACE INSERT INTO test

Also, varchar(255) isn't big enough for your example.

---

From the DB2 reference guide: "Note: XML data cannot be stored directly in the base table row in delimited files. It must be placed in separate files and referenced using an XDS."

You cannot import XML data just like that (or anything that implies new lines - this applies to LOBs as well). Instead, what you need to specify in the column is the XML file name, and the import file type will be DEL XML instead of DEL.

Check Exporting XML data for more clarifications.