| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| Storing XML data into DB2 database |
« View previous topic :: View next topic » |
| Author |
Message
|
| dhincali |
 Posted: Mon Dec 29, 2003 10:45 am Post subject: Storing XML data into DB2 database |
 |
|
Novice
Joined: 29 Dec 2003
Posts: 22
|
Hi,
I am a newcomer to MQSI. I am doing a XML message flow that sends the information in XML tags into DB2 database.
My input XML is as follows:
<IN100>
<Hdr>
<Interface>IN100</Interface>
<BatchId>SAAAAAABBBBCCCCCCCCCCDDDD</BatchId>
<NoOfRecs>25</NoOfRecs>
</Hdr>
<Dtl>
<Table>T001</Table>
<Type>I/D</Type>
<BUKRS>ABCD</BUKRS>
</Dtl>
<Dtl>
<Table>SKB1</Table>
<Type>I/D</Type>
<SAKNR>ABCDEFGHIJ</SAKNR>
<BUKRS>GTFR</BUKRS>
</Dtl>
<Dtl>
<Table>CEPC</Table>
<Type>I/D</Type>
<KOSTL>ABCD</KOSTL>
</Dtl>
</IN100>
I need to loop through all occurences of Dtl tag. I am able to send the information in first Dtl tag successfully into DB2 database. Since the XMl structure is not similar for each Dtl tag, I am unable to loop into all the other Dtl tags. I want to loop through individual Dtl tags and send the information in that tag to the respective database table in DB2.
My code looks like this:
SET Environment.SEQ = null;
SET Environment.SEQ[] = PASSTHRU('select nextval for tradmin.vseq from sysibm.sysdummy1');
DECLARE OUTERREC REFERENCE TO InputBody.IN265A.Dtl[1];
WHILE LASTMOVE(OUTERREC) DO
IF OUTERREC.Type ='I' THEN
INSERT INTO Database.MQSIADM.T001 (ID, BUKRS) VALUES (Environment.SEQ."1",OUTERREC.BUKRS);
ELSE IF OUTERREC.Type ='D' THEN
DELETE FROM Database.MQSIADM.T001 AS H WHERE H.BUKRS = OUTERREC."BUKRS";
END IF;
END IF;
MOVE OUTERREC NEXTSIBLING REPEAT TYPE NAME;
END WHILE;
I would appreciate any quick help. Thanks in Advance.
_________________
learning traveller |
|
| Back to top |
|
 |
| jefflowrey |
| Posted: Mon Dec 29, 2003 10:56 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
Type is a reserved word (or a non reserved keyword, I forget which).
If you need to use it to reference a field in your data, you need to put it in double-quotes. So OUTERREC."Type" instead of OUTERREC.Type.
Also, you are checking if Type is equal to 'I' or 'D', but not if it is equal to 'I/D', which is the only value your sample shows. So nothing will happen with the data you posted.
One way to debug ESQL code in version 2.1 is to set an Environment variable to hold debug data. So, you could store a loop counter in Environment.Variables.LoopsExecuted to know how many times your code went through the loop - and then see the result in a trace file that included ${Environment}.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| dhincali |
| Posted: Mon Dec 29, 2003 11:09 am Post subject: |
|
|
Novice
Joined: 29 Dec 2003
Posts: 22
|
Hi Jeff,
I agree with the change to "Type". The Input XML can have either I or D for every flow run. I tried hard coding but the XML structure is not unique. For example, If you look at the Dtl[1] tag, I need to insert the data in BUKRS field into database. i could do that but for the next Dtl tag which has two fields in it ,i did not know how to write the code. So if you could help me with sample code to loop through all the individual "Dtl" tags in my XML and then write the field element into database, that would be great. Thanks for all the help.
_________________
learning traveller |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Mon Dec 29, 2003 11:39 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
| dhincali wrote: |
| So if you could help me with sample code to loop through all the individual "Dtl" tags in my XML and then write the field element into database, that would be great. Thanks for all the help. |
I don't see anything blatantly wrong with the code you posted.
You seem to think it's not working. How do you know that? Are you getting any errors anywhere? Are you tracing through your flow to know if it gets to the code in question in the first place? Can you tell if it got past the code in question, and then got an error later on in your flow, and rolled back all the database changes?
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| dhincali |
| Posted: Mon Dec 29, 2003 12:07 pm Post subject: |
|
|
Novice
Joined: 29 Dec 2003
Posts: 22
|
Hi Jeff,
You seem to think it's not working. How do you know that? Are you getting any errors anywhere? Are you tracing through your flow to know if it gets to the code in question in the first place?
-- The code is working fine with one <Dtl> tag. I did tace the flow and i got desired results. But I am unable to use it for the second <Dtl> tag which has two field elements to be sent into the database. Could you provide me with some tips so that the code can work for all <Dtl> tags. Thanks.
_________________
learning traveller |
|
| Back to top |
|
|
| dhincali |
| Posted: Mon Dec 29, 2003 5:43 pm Post subject: |
|
|
Novice
Joined: 29 Dec 2003
Posts: 22
|
Hey Jeff,
I could nail the thing at last. Since the XML structure was too inconsistent, i had to give you all the trouble. Thanks anyway for your help.
_________________
learning traveller |
|
| Back to top |
|
|
|
|
|
| |
|
Page 1 of 1 |
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
|
|
