|
View previous topic :: View next topic
|
| Author |
Message |
subramsa
New User
Joined: 15 Apr 2005
Posts: 16
Location: Bangalore, India
|
|
|
|
Dear All,
I have a db2 table with 3 COLS
COL1 - TRANID - SMALLINT
COL2 - TRANDATE - DATE
COL3 - TRANCONT - VARCHAR(32504) (32K)
i need to insert XML data into COL3 - TRANCONT. How can i do that?
Have anyone worked on such situation?
Thanks in advance for your help
Regards |
|
| Back to top |
|
 |
Gurmeet
New User
Joined: 22 Feb 2006
Posts: 46
Location: Pune
|
|
|
|
Sai,
What is XML data?
~Gurmeet |
|
| Back to top |
|
|
subramsa
New User
Joined: 15 Apr 2005
Posts: 16
Location: Bangalore, India
|
|
|
|
Sample XML data given below
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE page_content [<!--
DOSSIER VIEW / DOSSIER UPDATE WEB APPLICATION -
DOCUMENT TYPE DEFENITION (DTD) FOR ATS UPDATE XML BASE FILE
RELEASE 1
<!ELEMENT page_content (page_update, form_process_parameters?)>
<!ELEMENT form_process_parameters ANY>
<!ELEMENT page_update (submission_data, update_details?)>
<!ELEMENT submission_data (user, impersonated_user?, employee?, employeesForMassUpdate?, formComment?)>
<!ELEMENT user (person)>
<!ELEMENT impersonated_user (person)>
<!ELEMENT employee (person)>
<!ELEMENT person (username?)>
<!ELEMENT username ANY>
<!ELEMENT formComment ANY>
<!ELEMENT update_details (section_update*)>
<!ELEMENT section_update (changes?, additions?, deletions?)>
<!ELEMENT changes (occurrence_update*)>
<!ELEMENT additions (occurrence_update*)>
<!ELEMENT deletions (occurrence_update*)>
<!ELEMENT occurrence_update (black_box_key, application_id, item_update*)>
<!ELEMENT black_box_key ANY>
<!ELEMENT application_id ANY>
<!ELEMENT item_update (old_value, new_value, hr_error_descr)>
<!ELEMENT old_value ANY>
<!ELEMENT new_value ANY>
<!ELEMENT hr_error_descr ANY>
<!ELEMENT employeesForMassUpdate (employeeForMassUpdate+)>
<!ELEMENT employeeForMassUpdate (person+)> |
|
| Back to top |
|
|
rajandhla
Active User
.jpg)
Joined: 18 Oct 2005
Posts: 182
Location: Luton UK
|
|
|
|
Hi,
According to my knowledge there is no direct way of inserting (i.e support from db2) XMl data in db2. I think you need to write a code to delimit all the XML tag get the required data required to insert in db2 table.
or let's wait for the other chaps suggestions......
Regards
jai |
|
| Back to top |
|
|
prasadvrk
Active User
Joined: 31 May 2006
Posts: 200
Location: Netherlands
|
|
|
|
| In oracle you can insert BLOB, CLOB types(by creating objects in binary format) but I think in DB2 we do not have any equivalent types. |
|
| Back to top |
|
|
twissi
Active User
Joined: 01 Aug 2005
Posts: 105
Location: Somerset, NJ
|
|
|
|
Sai,
DB2 won't differentiate between XML tags and plain text, I really didn't understand what exactly you're looking for. If my understanding is correct, you need to populate a DB2 table with some data that when accessed by a web application, should produce a meaningful page (!?).
Well, if that's the case, declare a working storage variable (I'm assuming you'll be using a COBOL program) as below:
| Code: |
01 WS-FILE-NOTE-DESC.
05 FILLER PIC X(5)
VALUE '<DIV>'.
05 FILLER PIC X(4)
VALUE '<BR>'.
05 FILLER PIC X(26)
VALUE 'Scan Date : '.
05 WS-SCAN-DATE.
10 WS-SCAN-DATE-DD PIC X(2).
10 FILLER PIC X
VALUE '/'.
10 WS-SCAN-DATE-MM PIC X(2).
10 FILLER PIC X
VALUE '/'.
10 WS-SCAN-DATE-YYYY PIC X(4).
05 FILLER PIC X(5)
VALUE SPACES.
05 FILLER PIC X(4)
VALUE '<BR>'.
05 FILLER PIC X(26)
VALUE 'Image Indexed UserId : '.
05 WS-IMAGE-INDEXED-USERID PIC X(8).
05 FILLER PIC X(5)
VALUE SPACES.
05 FILLER PIC X(4)
VALUE '<BR>'.
05 FILLER PIC X(26)
VALUE 'Image Description : '.
05 WS-IMAGE-DESC PIC X(15).
05 FILLER PIC X(5)
VALUE SPACES.
05 FILLER PIC X(4)
VALUE '<BR>'.
05 FILLER PIC X(26)
VALUE 'Referred From UserId : '.
05 WS-REF-FROM-USERID PIC X(8).
05 FILLER PIC X(5)
VALUE SPACES.
05 FILLER PIC X(4)
VALUE '<BR>'.
05 FILLER PIC X(26)
VALUE 'Referred To UserId : '.
05 WS-REF-TO-USERID PIC X(8).
05 FILLER PIC X(5)
VALUE SPACES.
05 FILLER PIC X(4)
VALUE '<BR>'.
05 FILLER PIC X(26)
VALUE 'Referral Reason : '.
05 WS-REF-REASON PIC X(70)
OCCURS 5 TIMES.
05 FILLER PIC X(5)
VALUE SPACES.
05 FILLER PIC X(4)
VALUE '<BR>'.
05 FILLER PIC X(26)
VALUE 'Referral Date : '.
05 WS-REF-DATE.
10 WS-REF-DATE-DD PIC X(2).
10 FILLER PIC X
VALUE '/'.
10 WS-REF-DATE-MM PIC X(2).
10 FILLER PIC X
VALUE '/'.
10 WS-REF-DATE-YYYY PIC X(4).
05 FILLER PIC X(5)
VALUE SPACES.
05 FILLER PIC X(4)
VALUE '<BR>'.
05 FILLER PIC X(26)
VALUE 'Referral Reply : '.
05 WS-REF-REPLY PIC X(70)
OCCURS 5 TIMES.
05 FILLER PIC X(5)
VALUE SPACES.
05 FILLER PIC X(4)
VALUE '<BR>'.
05 FILLER PIC X(26)
VALUE 'Reply Date : '.
05 WS-REPLY-DATE.
10 WS-REPLY-DATE-DD PIC X(2).
10 FILLER PIC X
VALUE '/'.
10 WS-REPLY-DATE-MM PIC X(2).
10 FILLER PIC X
VALUE '/'.
10 WS-REPLY-DATE-YYYY PIC X(4).
05 FILLER PIC X(5)
VALUE SPACES.
05 FILLER PIC X(4)
VALUE '<BR>'.
05 FILLER PIC X(26)
VALUE 'Completed UserID & Date : '.
05 WS-COMPLETED-USERID PIC X(8).
05 FILLER PIC X(2)
VALUE '& '.
05 WS-COMPLETED-DATE.
10 WS-COMPLETED-DATE-DD PIC X(2).
10 FILLER PIC X
VALUE '/'.
10 WS-COMPLETED-DATE-MM PIC X(2).
10 FILLER PIC X
VALUE '/'.
10 WS-COMPLETED-DATE-YYYY PIC X(4).
05 FILLER PIC X(5)
VALUE SPACES.
05 FILLER PIC X(4)
VALUE '<BR>'.
05 FILLER PIC X(26)
VALUE 'Document Pended Flag : '.
05 WS-DOC-PENDED-FLAG PIC X(1).
05 FILLER PIC X(5)
VALUE SPACES.
05 FILLER PIC X(4)
VALUE '<BR>'.
05 FILLER PIC X(26)
VALUE 'Document Retained Flag : '.
05 WS-DOC-RETAINED-FLAG PIC X(1).
05 FILLER PIC X(5)
VALUE SPACES.
05 FILLER PIC X(4)
VALUE '<BR>'.
05 FILLER PIC X(26)
VALUE 'Reindexed Claim Number : '.
05 WS-REINDEXED-CLAIM-NUM PIC X(6).
05 FILLER PIC X(5)
VALUE SPACES.
05 FILLER PIC X(6)
VALUE '</DIV>'. |
Read the file (or whatever) and get the values of above variables, move that to TRANCONT and insert into the table.
Please ignore my reply if the solution stands far too away from your requirement; I did see this post quite sometime back but was waiting for some one to respond.
Cheers, Twissi. |
|
| Back to top |
|
|
Gurmeet
New User
Joined: 22 Feb 2006
Posts: 46
Location: Pune
|
|
|
|
I second Twissi..
~Gurmeet |
|
| Back to top |
|
|
twissi
Active User
Joined: 01 Aug 2005
Posts: 105
Location: Somerset, NJ
|
|
|
|
| Sorry Gurmeet, What did you mean? |
|
| Back to top |
|
|
Gurmeet
New User
Joined: 22 Feb 2006
Posts: 46
Location: Pune
|
|
|
|
| twissi wrote: |
| Sorry Gurmeet, What did you mean? |
I think your's is the best solution I can think of..
~Gurmeet |
|
| Back to top |
|
|
|
|
