2

Websphere Message Broker: File in File out example. I have an XML file with repeating element structure. How can I access and modify value of a particular element in ESQL. I worte following code..

CREATE PROCEDURE CopyEntireMessage() BEGIN
     --SET OutputRoot = InputRoot;
      DECLARE I INTEGER 1;
      DECLARE J INTEGER;
      SET J = CARDINALITY(OutputRoot.*[]);
      WHILE I < J DO
         SET OutputRoot = InputRoot;
         SET OutputRoot.XMLNS.person.student[I].name = 'XYZ';
         SET I = I + 1;
      END WHILE;
 END;

But its not working. Picking up the file from input folder but i cannot see anything in Output folder. But if I comment

SET OutputRoot.XMLNS.student[I].name = 'XYZ';

then file is available in output folder as it is without any change.

My XML file is as below

<person>
 <student>
   <name>ABC</name>
   <age>20</age>
   <address>city1</address>
 </student>
 <student>
   <name>PQR</name>
   <age>20</age>
   <address>city2</address>
 </student>
</person>

can anybody help me on this?

Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
Neha Raje
  • 151
  • 3
  • 7
  • 16

3 Answers3

6

This compute module should do what you need, tested at 9001 on linux:

CREATE COMPUTE MODULE FileInputOutput_Compute
    CREATE FUNCTION Main() RETURNS BOOLEAN
    BEGIN
        -- CALL CopyMessageHeaders();
        CALL CopyEntireMessage();

        FOR source AS OutputRoot.XMLNSC.person.student[] DO
            SET source.name = 'XYZ';
        END FOR;


        RETURN TRUE;
    END;

    CREATE PROCEDURE CopyMessageHeaders() BEGIN
        DECLARE I INTEGER 1;
        DECLARE J INTEGER;
        SET J = CARDINALITY(InputRoot.*[]);
        WHILE I < J DO
            SET OutputRoot.*[I] = InputRoot.*[I];
            SET I = I + 1;
        END WHILE;
    END;

    CREATE PROCEDURE CopyEntireMessage() BEGIN
        SET OutputRoot = InputRoot;
    END;
END MODULE;

A couple of notes, firstly it is not good practice to redefine the auto-generated procedures, if you need to reuse functionality which sets every field in a message then it would be wise to create a new procedure to do this.

XMLNS is also deprecated so use XMLNSC instead, it is higher performance and has all the same capabilities as XMLNS which is retained only to support legacy applications.

Dave
  • 633
  • 4
  • 6
  • Thanks Dave.. its working as expected..also thanks for correcting on the standards.. :) – Neha Raje Jul 14 '14 at 14:30
  • But when I do it with While Loop in Main().. its not working.. any idea about this? or why is it not working the same with While? – Neha Raje Jul 14 '14 at 14:40
  • I think the biggest problem is that your select criteria in the cardinality clause is wrong. Assuming you have no headers OutputRoot.*[] I think is going to match the single "Person" element which has cardinality 1, your loop condition is therefore never true and you just fall through the while loop without doing any processing. – Dave Jul 14 '14 at 16:50
  • Each iteration of the loop you are also overwriting the entire Output tree with the input tree so even if you did execute the loop body and had the cardinality pointing at the right path you would only see the change on the last element. – Dave Jul 14 '14 at 16:51
  • `DECLARE I INTEGER 0; DECLARE J INTEGER; SET J = CARDINALITY(OutputRoot.XMLNSc.person.student[]); WHILE I <= J DO SET OutputRoot.XMLNSC.person.student[I].name = 'AAAA'; SET I = I + 1; END WHILE;` – Neha Raje Jul 15 '14 at 04:18
  • Agree with you.. the above code should change the last element, but after executing the file remains unchanged. – Neha Raje Jul 15 '14 at 04:24
  • XMLNSc (lowercase c) and XMLNSC (uppercase C) are different, the code above will return a cardinality of 0 because the OutputRoot.XMLNSc element does not exist. – Dave Jul 16 '14 at 14:10
0

I think your code should be like this:

CREATE PROCEDURE CopyEntireMessage() BEGIN
  SET OutputRoot = InputRoot;
  DECLARE I INTEGER 1;
  DECLARE J INTEGER;
  SET J = CARDINALITY(OutputRoot.XMLNS.person.*[]);
  WHILE I < J DO
     SET OutputRoot.XMLNS.person.student[I].name = 'XYZ';
     SET I = I + 1;
  END WHILE;
END;

Useless to make assignment like "OutputRoot = InputRoot" several times, you need it only once.

user3714601
  • 1,156
  • 9
  • 27
  • tried this code but then the file is moved to mqsibackout folder – Neha Raje Jul 14 '14 at 09:33
  • Please, can you do the following: post the complete code of "ESQL MODULE" (with your procedure inside) and check which parser you actually use (it must be in the settings of input node of the flow). And also, can you post value of "Compute mode" property of the compute node? – user3714601 Jul 14 '14 at 10:02
  • Parser: XMLNS : For XML messages (namespace aware) – Neha Raje Jul 14 '14 at 10:26
  • Compute Mode: Message – Neha Raje Jul 14 '14 at 10:27
  • Parser and compute mode look correct, what about "ESQL MODULE" code, can you post it? – user3714601 Jul 14 '14 at 10:39
  • I have already posted the ESQL code.. ESQL module contains 2 procedures.. "CopyMessage Headers" and "CopyEntireMessage".. I have posted the code from CopyEntireMessage – Neha Raje Jul 14 '14 at 10:57
  • You should have some code in ESQL module besides procedures definition. At least there must be something like "CALL CopyEntireMessage();" and "RETURN TRUE;" inside. Do you have such code? – user3714601 Jul 14 '14 at 11:08
  • Oh yes..sorry.. it is there – Neha Raje Jul 14 '14 at 11:12
  • CREATE FUNCTION Main() RETURNS BOOLEAN BEGIN CALL CopyMessageHeaders(); CALL CopyEntireMessage(); RETURN TRUE; END; – Neha Raje Jul 14 '14 at 11:12
  • You definitely do not need this line: "CALL CopyMessageHeaders();", if this function is auto-generated. All the headers are copied with CopyEntireMessage() call. But not sure it's related to the problem. Have you checked your file in mqsibackout? Anything related to the error inside? – user3714601 Jul 14 '14 at 11:27
  • The file in mqsibackout will simply be a copy of the input file. If you havent wired the catch or error terminals then the error info that caused the rollback would be in the syslog (unix), joblog (z/OS) or windows event log. – Dave Jul 14 '14 at 12:05
0

Try using the graphical debugger when resolving these types of issues, it allows you to step through the ESQL code and watch the tree being built. You'd easily be able to see where the code was not doing what you expected it to.

https://www-01.ibm.com/support/knowledgecenter/SSMKHH_9.0.0/com.ibm.etools.mft.doc/ag11050_.htm