import XML file into DB2 table

[Alvin SIU]

Hi all,

I have 6 tables inside a MS Access 2003 mdb file.

I want to convert them as DB2 version -8 tables in AIX 5.2.

I have exported them as 6 XML files.

The XML files look fine.
Each record is embeded by a tag which is the table name.
Then, each column is embeded by a tag which is the column name.
Even memo fields are represented as multi-lines within its tag.

Each whole file just looks like a table of m x n cells, nice and neat.

However, after I transfer the XML files into AIX for DB2 importing,
it seems to me that DB2 has no such an importing option for XML.

There are options for IXF, DEL (i.e. CSV), ... etc but seems not for
XML.

Does anyone has similar experience ?

Does anyone know know to import that XML file into a table in DB2 ?

Thanks in advance.
Alvin SIU

[fprahst]

On Jun 22, 7:45*am, Alvin SIU <[e-mail]:

Hi all,

I have 6 tables inside a MS Access 2003 mdb file.

I want to convert them as DB2 version -8 tables in AIX 5.2.

I have exported them as 6 XML files.

The XML files look fine.
Each record is embeded by a tag which is the table name.
Then, each column is embeded by a tag which is the column name.
Even memo fields are represented as multi-lines within its tag.

Each whole file just looks like a table of m x n cells, nice and neat.

However, after I transfer the XML files into AIX for DB2 importing,
it seems to me that DB2 has no such an importing option for XML.

There are options for IXF, DEL (i.e. CSV), ... etc but seems not for
XML.

Does anyone has similar experience ?

Does anyone know know to import that XML file into a table in DB2 ?

Thanks in advance.
Alvin SIU

hi,

there is no xml-import option.
if you want to use xml as transfer-tool then you have to use the xml-
extender on the db2 side.

cu

ferdinand

[gimme_this_gimme_that]

If it's a one time thing I'd recommend writing a VBA Macro in MS
Access that loops through the rows in the tables and writes a SQL
INSERT statement for each row of the table to disk.

Then execute the SQL statements in that file.

If you don't want to use VBA then you could read the XML file in your
favorite programming language and similarly create an output-script
file of INSERT statements.

[Dave Hughes]

Alvin SIU wrote:

Hi all,

I have 6 tables inside a MS Access 2003 mdb file.

I want to convert them as DB2 version -8 tables in AIX 5.2.

The XML support in DB2 8 is rather sparse (quite a bit for generating
XML from tables, but not for parsing or importing it - that stuff was
added in DB2 9).

I have exported them as 6 XML files.

The XML files look fine.
Each record is embeded by a tag which is the table name.
Then, each column is embeded by a tag which is the column name.
Even memo fields are represented as multi-lines within its tag.

Each whole file just looks like a table of m x n cells, nice and neat.

However, after I transfer the XML files into AIX for DB2 importing,
it seems to me that DB2 has no such an importing option for XML.

There are options for IXF, DEL (i.e. CSV), ... etc but seems not for
XML.

IXF, delimited (CSV), and fixed-width text files each represent tables
of data (columns and rows). XML files represent a tree hierarchy of
information. A table-like structure as you describe is merely a
"special case" - moreover there are multiple ways of specifying such a
table-like structure in XML (attributes or elements for columns, does
ordering matter or are columns matched by name, if using elements, do
the element tags name the columns or are attributes used for this, if
using attributes how do you specify "abnormal" column names containing
spaces, etc. etc. etc.)

Does anyone has similar experience ?

Does anyone know know to import that XML file into a table in DB2 ?

If you move to DB2 9 (or above), you'll find the XML support introduced
in that version includes the ability to import from any XML structure
into multiple target tables (although I think there's some restrictions
on recursive structures) - provided you can describe the structure in
an XML schema [1], and/or with XPath expressions [2]. Alternatively,
you could just dump the whole XML file into an XML column - although
personally I only consider those useful for storing mixed-type docs
(i.e. HTML and other stuff that doesn't "shred" well). Still, you won't
find any options for importing "tabular" XML files directly via IMPORT
or LOAD (as mentioned above, given the plethora of styles one could use
you'd still have to describe the structure of the XML at which point
you may as well use the parsing support to shred the document into
tables).

However, if you want to stick with DB2 8, I'd suggest you forget about
using XML. Just export the data to a CSV file and use that. If you're
wondering how you can handle textual data containing line breaks with
CSV, have a look at the "delprioritychar" modifier for LOAD [3] (or
IMPORT) which will allow you to include line breaks within quoted
values.

Another option, if you're feeling /really/ bored is to get your
application to write out IXFs directly - the structure is described
reasonably well [4] in the InfoCenter :-)
[1]
http://publib.boulder.ibm.com/infoce.../com.ibm.db2.l
uw.xml.doc/doc/c0022319.html

[2]
http://publib.boulder.ibm.com/infoce.../com.ibm.db2.l
uw.sql.ref.doc/doc/r0002169.html (see Example 5 at the bottom)

[3]
http://publib.boulder.ibm.com/infoce.../com.ibm.db2.l
uw.admin.cmd.doc/doc/r0008305.html#r0008305__d1e2531 (find
delprioritychar in the "modifiers for DEL" table)

[4]
http://publib.boulder.ibm.com/infoce.../com.ibm.db2.l
uw.admin.dm.doc/doc/r0004667.html
Cheers,

Dave.

[Alvin SIU]

Hi all,

Thanks for your invaluable inputs.

I finally find a solution : using SAS programming language.

Inside SAS, I can declare the XML file as a XML library.

This XML library is expecting the XML structure which is
exactly the same as my file's m x n cells table-like structure.

Therefore, SAS can automatically convert the XML table-like structure
into a SAS table.

Then, I can even do some transformation or data clean up
before inserting into DB2.

I am now focusing on inserting rows into DB2 thru SAS.
There is some SAS-DB2 setup problem in my AIX.

Anyways, I will fix it.

Once again, thanks everybody for your help.
Bye.
Alvin SIU