473,326 Members | 1,972 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

IMPORT'ing XML

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"."AIMRETRIEVEDITEM" (
"AIMRETRIEVEDITEMID" DECIMAL(13,0) NOT NULL GENERATED ALWAYS AS
IDENTITY ( START WITH +1 , INCREMENT BY +1 , CACHE 20 ) ,
"AIMRETRIEVALID" DECIMAL(13,0) NOT NULL ,
"ARCHIVEDOCUMENTID" VARCHAR(36) ,
"AIMDOCUMENTID" VARCHAR(256) ,
"CAPTUREDATE" TIMESTAMP ,
"ACCOUNTNUMBER" VARCHAR(24) ,
"ROUTINGTRANSITNUMBER" VARCHAR(9) ,
"IMAGETYPE" VARCHAR(5) ,
"STATUS" VARCHAR(256) NOT NULL ,
"IMAGESIZE" INTEGER ,
"SEQUENCENUMBER" INTEGER ,
"LOCATION" VARCHAR(24) ,
"SERVER" VARCHAR(24) ,
"FOLDER" VARCHAR(24) ,
"RESULTERRORSEVERITY" INTEGER ,
"RESULTERRORTYPE" VARCHAR(256) ,
"RESULTERRORMESSAGE" VARCHAR(256) ,
"IMAGEERRORSEVERITY" INTEGER ,
"IMAGEERRORTYPE" VARCHAR(256) ,
"IMAGEERRORMESSAGE" VARCHAR(256) ,
"CACHEDIMAGE" BLOB(65536) NOT LOGGED COMPACT ,
"ITEMVIEWCOUNT" 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>VP07T</locationName>
<serverName>VP07T</serverName>
<folderName>USBCMBINQ</folderName>
<imageList>
<imageDetail>
<imageErr>
<imageErrSeverity>null</imageErrSeverity>
<imageErrType>null</imageErrType>
<imageErrMsg>null</imageErrMsg>
</imageErr>
<docId>5013-5015-5017-DAA1-12FAAA-0-27413-1</docId>
<docLocation>Unknown</docLocation>
<imageType>AFP</imageType>
<imageStatus>true</imageStatus>
<imageSize>27413</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.
Nov 12 '05 #1
3 4141
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...s2unix/support
Nov 12 '05 #2
Douglas Doole <do***@ca.ibm.com> wrote in message news:<bu**********@hanover.torolab.ibm.com>...
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.


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!!
Nov 12 '05 #3
Ian
Anthony Robinson wrote:
Douglas Doole <do***@ca.ibm.com> wrote in message news:<bu**********@hanover.torolab.ibm.com>...
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.

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).


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.

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).


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! =-----
Nov 12 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: David Eger | last post by:
I'm trying to make a very simple extension which will return a new copy of a C++ string object to python, and I'm segfaulting in the process. I'm using Python 2.2.2, SWIG 1.3.17 and g++ 3.3. Am I...
4
by: Irmen de Jong | last post by:
Hello, I don't understand why the following doesn't work. What I want to do is dynamically import some generated Python code and I'm doing this using compile and exec'ing it in the dict of a new...
45
by: It's me | last post by:
I am new to the Python language. How do I do something like this: I know that a = 3 y = "a" print eval(y)
3
by: Pro Grammer | last post by:
Hello, all, I am not sure if this is the right place to ask, but could you kindly tell me how to "load" a shared object (like libx.so) into python, so that the methods in the .so can be used? That...
2
by: Piet | last post by:
Hello, Via Xpath, I want to access nodes which have a namespace prefix. THe document at hand is an Xsl-FO document. I tried the following: from xml.dom import minidom from xml.xpath import...
5
by: steve | last post by:
Hi, When I copy tables in a database from one server to another using enterprise manager, everything copies ok, except for field defaults. Has anyone seen this, and what is the solution? --...
2
by: Dany P. Wu | last post by:
Hi everyone, I downloaded a custom scroll bar script from this site: http://www.dynamicdrive.com/dynamicindex2/pagescroller.htm Can someone please tell me if it is possible to have a few...
3
by: teranews | last post by:
My question is this... Is 'LINK'ing a stylesheet required before 'IMPORT'ing another for successful hovering? I have a problem which cropped up with the introduction of IE 7 Beta 2... Yes.....
4
by: pdlemper | last post by:
Have carefully installed Python 2.5.1 under XP in dir E:\python25 . ran set path = %path% ; E:\python25 Python interactive mode works fine for simple arithmetic . Then tried >> import math Get...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.