IMPORT'ing XML

[Anthony Robinson]

I have an issue I've been working on that has proven to be quite
troublesome. I already have an idea of what the anwser may be, but
would like to solicit some suggestions or other ideas from you guys
(and gals I'm sure).

We have an application that will store BLOB data (jpegs) along with
character data. The data comes to us in an xml format.; and this xml
document contains both the character and the blob/binary data. Here is
the table DDL:

CREATE TABLE "AIMD"."AIMRETR IEVEDITEM" (
"AIMRETRIEVEDIT EMID" DECIMAL(13,0) NOT NULL GENERATED ALWAYS AS
IDENTITY ( START WITH +1 , INCREMENT BY +1 , CACHE 20 ) ,
"AIMRETRIEVALID " DECIMAL(13,0) NOT NULL ,
"ARCHIVEDOCUMEN TID" VARCHAR(36) ,
"AIMDOCUMEN TID" VARCHAR(256) ,
"CAPTUREDAT E" TIMESTAMP ,
"ACCOUNTNUM BER" VARCHAR(24) ,
"ROUTINGTRANSIT NUMBER" VARCHAR(9) ,
"IMAGETYPE" VARCHAR(5) ,
"STATUS" VARCHAR(256) NOT NULL ,
"IMAGESIZE" INTEGER ,
"SEQUENCENUMBER " INTEGER ,
"LOCATION" VARCHAR(24) ,
"SERVER" VARCHAR(24) ,
"FOLDER" VARCHAR(24) ,
"RESULTERRORSEV ERITY" INTEGER ,
"RESULTERRORTYP E" VARCHAR(256) ,
"RESULTERRORMES SAGE" VARCHAR(256) ,
"IMAGEERRORSEVE RITY" INTEGER ,
"IMAGEERRORTYPE " VARCHAR(256) ,
"IMAGEERRORMESS AGE" VARCHAR(256) ,
"CACHEDIMAG E" BLOB(65536) NOT LOGGED COMPACT ,
"ITEMVIEWCO UNT" INTEGER )
IN "USERSPACE1 " ;


I know that I will have to use IMPORT in order to get the binary data
into the database. The first of my questions is this: since IMPORT is
strictly a CLP command, is there any way within DB2 to invoke a CLP
session behind the scenes from within a stored procedure and then send
CLP commands to that session?

The second issue has to do with the xml itself. Let's say for
simplicity sake that CLP is not an issue. Is there a file type within
the IMPORT command that would cover an input file type of xml? Or
would we need to convert that file to some other type (ixf, csv,
etc.), create a separate file for the binary data, and then import the
two files separately?

Here's a sample of the XML:

15Oct03 10:07:50:884 Reply XML: <?xml version="1.0"
encoding="UTF-8" standalone="yes "?>
<imageReply>
<status>success </status>
<resultCount> 1</resultCount>
<errorOutput>
<errSeverity> </errSeverity>
<errType></errType>
<errMsg></errMsg>
</errorOutput>
<imageOutput>
<locationName>V P07T</locationName>
<serverName>VP0 7T</serverName>
<folderName>USB CMBINQ</folderName>
<imageList>
<imageDetail>
<imageErr>
<imageErrSeveri ty>null</imageErrSeverit y>
<imageErrType>n ull</imageErrType>
<imageErrMsg>nu ll</imageErrMsg>
</imageErr>
<docId>5013-5015-5017-DAA1-12FAAA-0-27413-1</docId>
<docLocation>Un known</docLocation>
<imageType>AF P</imageType>
<imageStatus>tr ue</imageStatus>
<imageSize>2741 3</imageSize>
<image></image>
<imageFront> THIS IS WHERE THE BINARY DATA IS
</imageFront>
<imageFrontSize >7507</imageFrontSize>
<imageFrontType >TIF</imageFrontType>

Any help or suggestions would be greatly appreciated.

Thanks in advance.

[Douglas Doole]

Re: IMPORT'ing XML

IMPORT doesn't have a native XML type. You'll have to convert the file
yourself.

To do the import, you won't be able to use CLP commands. However, if you
look at the Administrative API Reference, you'll find that there is an
import function that you can use. (All CLP does is translate the command
into a function call.) Of course, this means that your procedure will have
to be written in C (or some equivalent language).

You might want to consider just doing the insert yourself. All IMPORT does
is issue a series of SQL INSERT statements. If you have to parse the file,
generate an intermediate file and then import the intermediate file, you
might find it quicker just to parse the file and do the insert directly.

--
_______________ _______________ _______________ _______________ _________
Doug Doole
DB2 Universal Database Development
IBM Toronto Lab

Visit the DB2 UDB and DB2 Connect Online Support site at:

http://www.ibm.com/software/data/db2/udb/winos2unix/support

[Anthony Robinson]

Re: IMPORT'ing XML

Douglas Doole <doole@ca.ibm.c om> wrote in message news:<bu6d1j$fb 7$1@hanover.tor olab.ibm.com>.. .[color=blue]
> IMPORT doesn't have a native XML type. You'll have to convert the file
> yourself.
>
> To do the import, you won't be able to use CLP commands. However, if you
> look at the Administrative API Reference, you'll find that there is an
> import function that you can use. (All CLP does is translate the command
> into a function call.) Of course, this means that your procedure will have
> to be written in C (or some equivalent language).
>
> You might want to consider just doing the insert yourself. All IMPORT does
> is issue a series of SQL INSERT statements. If you have to parse the file,
> generate an intermediate file and then import the intermediate file, you
> might find it quicker just to parse the file and do the insert directly.[/color]

Would love to do just straight inserts, but unfortunately the xml file
also contains an image (binary data). The only way to get that BLOB
data into the table is via an IMPORT. Have no problem doing the
IMPORT...the crux is either invoke a command session and pass in
parameters, or as you mentioned, write a stored procedure in C (which
I've NEVER done before).

Is there some place that you could point me to some code examples as
to how I could accomplish this (either solution - actually any
solution).

Thanks!!

[Ian]

Re: IMPORT'ing XML

Anthony Robinson wrote:[color=blue]
> Douglas Doole <doole@ca.ibm.c om> wrote in message news:<bu6d1j$fb 7$1@hanover.tor olab.ibm.com>.. .
>[color=green]
>>IMPORT doesn't have a native XML type. You'll have to convert the file
>>yourself.
>>
>>To do the import, you won't be able to use CLP commands. However, if you
>>look at the Administrative API Reference, you'll find that there is an
>>import function that you can use. (All CLP does is translate the command
>>into a function call.) Of course, this means that your procedure will have
>>to be written in C (or some equivalent language).
>>
>>You might want to consider just doing the insert yourself. All IMPORT does
>>is issue a series of SQL INSERT statements. If you have to parse the file,
>>generate an intermediate file and then import the intermediate file, you
>>might find it quicker just to parse the file and do the insert directly.[/color]
>
>
> Would love to do just straight inserts, but unfortunately the xml file
> also contains an image (binary data). The only way to get that BLOB
> data into the table is via an IMPORT. Have no problem doing the
> IMPORT...the crux is either invoke a command session and pass in
> parameters, or as you mentioned, write a stored procedure in C (which
> I've NEVER done before).[/color]

You can insert binary data into a table, no problem (from an application,
but not from the DB2 CLP).

I think that Doug's point was that since you're writing a program to
parse your XML, you could just modify the program to insert into the
database directly, instead of writing data to a flat file and a LOB file
and then executing IMPORT.

[color=blue]
> Is there some place that you could point me to some code examples as
> to how I could accomplish this (either solution - actually any
> solution).[/color]

This is pretty simple in perl, it has modules for both parsing XML and
accessing DB2.


-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----