Hitachi Vantara Pentaho Community Forums
Results 1 to 13 of 13

Thread: How to handle multiple occurance of a xml element dynamically in the insert/Update

  1. #1

    Post How to handle multiple occurance of a xml element dynamically in the insert/Update

    Hi,

    We would like to have your valuable advice in solving one issue we are facing in kettle usage.

    We have an xml file to process using kettle. While processing this file, we are storing the values into Mysql table. We need some guidance to handle a specific situation.

    Case :

    In our xml we have an element named '<ALLLEDGERENTRIES.LIST>'.
    We need to insert the values into our mysql table. We have done this by placing an insert/update transformation. This will insert one row in the table. This is fine.


    same xml entry is as follows.

    <ALLLEDGERENTRIES.LIST>
    <REMOVEZEROENTRIES>No</REMOVEZEROENTRIES>
    <ISDEEMEDPOSITIVE>No</ISDEEMEDPOSITIVE>
    <LEDGERFROMITEM>No</LEDGERFROMITEM>
    <LEDGERNAME>Cash</LEDGERNAME>
    <AMOUNT>4000.00</AMOUNT>
    </ALLLEDGERENTRIES.LIST>

    Our problem is here.

    In some cases we will have more than one entry of similar type. e.g


    <ALLLEDGERENTRIES.LIST>
    <REMOVEZEROENTRIES>No</REMOVEZEROENTRIES>
    <ISDEEMEDPOSITIVE>No</ISDEEMEDPOSITIVE>
    <LEDGERFROMITEM>No</LEDGERFROMITEM>
    <LEDGERNAME>Cash</LEDGERNAME>
    <AMOUNT>4000.00</AMOUNT>
    </ALLLEDGERENTRIES.LIST>


    <ALLLEDGERENTRIES.LIST>
    <REMOVEZEROENTRIES>No</REMOVEZEROENTRIES>
    <ISDEEMEDPOSITIVE>No</ISDEEMEDPOSITIVE>
    <LEDGERFROMITEM>No</LEDGERFROMITEM>
    <LEDGERNAME>Cash</LEDGERNAME>
    <AMOUNT>1000.00</AMOUNT>
    </ALLLEDGERENTRIES.LIST>

    In this case we need to insert two rows in the table.

    The problem is the number of times this entry appears in the xml varies.


    How do we handle this case dynamically without changing the kettle file? Can we write a kettle transformation in such a way that, at run-time the process decides the number of occurrence of this entry and call that many insert/update.


    Pls help us to solve this issue.

    Sheeja


  2. #2
    Join Date
    May 2006
    Posts
    4,882

    Default

    You're not making sense... check if on the input side the XML step gets multiple rows out of the XML file. If that works you're good to go, as the insert/update step will get more than 1 row.

    Regards,
    Sven

  3. #3

    Default

    Thanks for your quick reply Sven.

    I would like to explain our problem in detail.

    It is true that kettle reads multiple rows out of the xml file and
    insert steps execute more than ones.


    E.g in our xml we have the main element as '<TALLYMESSAGE> .. </TALLYMESSAGE>

    This occurs multiple times. Insert/Update in transformation takes care of
    inserting these entries in the table.


    But we have an additional processing step inside this main element. Here is our actual problem.

    <ALLLEDGERENTRIES.LIST> element occurs multiple times under the main element <TALLYMESSAGE>. The data inside this child element should go into child table.

    In Kettle these are represented as
    'Voucher1Allledgerentries.List1Ledgername1,
    Voucher1Allledgerentries.List1Amount1 etc
    for the first occurance.

    Voucher1Allledgerentries.
    List2Ledgername1, Voucher1Allledgerentries.List2Amount1
    for the second occurance.


    We have added two [Insert/Update] to insert into child table inside the main flow [processflow.jpg in attachment]. But as mentioned earlier, we will not know how many such occurance of this child element will be there in xml upfront.

    e.g.
    <TALLYMESSAGE xmlns:UDF="TallyUDF">
    ...
    ..
    <ALLLEDGERENTRIES.LIST>
    <REMOVEZEROENTRIES>No</REMOVEZEROENTRIES>
    <ISDEEMEDPOSITIVE>No</ISDEEMEDPOSITIVE>
    <LEDGERFROMITEM>No</LEDGERFROMITEM>
    <LEDGERNAME>Cash</LEDGERNAME>
    <AMOUNT>4000.00</AMOUNT>
    </ALLLEDGERENTRIES.LIST>

    <ALLLEDGERENTRIES.LIST>
    <REMOVEZEROENTRIES>No</REMOVEZEROENTRIES>
    <ISDEEMEDPOSITIVE>No</ISDEEMEDPOSITIVE>
    <LEDGERFROMITEM>No</LEDGERFROMITEM>
    <LEDGERNAME>Cash</LEDGERNAME>
    <AMOUNT>1000.00</AMOUNT>
    </ALLLEDGERENTRIES.LIST>
    ..
    </TALLYMESSAGE>

    <TALLYMESSAGE xmlns:UDF="TallyUDF">
    ..
    ..
    ..
    </TALLYMESSAGE>

    Pls see the processflow.jpg file for the transformation we used.

    Sheeja
    Attached Images Attached Images  

  4. #4
    Join Date
    May 2006
    Posts
    4,882

    Default

    I would try to process parents in 1 transformation, and children in another. Else you're going to get very stuck as each row on a hop has to be of the same format.

    What I don't know is whether the XML step that you use supports that for "children". The XML get data plugin should be able to support it, but is not yet available on v3.

    Regards,
    Sven

  5. #5

    Default

    am using kettle2.4. I need to change the version to solve problem or in which version xml input plugin is available

  6. #6
    Join Date
    May 2006
    Posts
    4,882

    Default

    Ouch 2.4.... at least upgrade to 2.5.2, then the XML Get data plugin will work as well. And try 3.0.1 for fun

    One of the important things to realize is that in PDI all rows flowing over the same hiop have to be of the same structure. So when you want to process something with parents and children, you either need to have a fixed number of children (or a maximum number of children). Or you need to process parents and children separately.

    What I think you need for your processing is the ability to process the parents... probably already ok. And the the ability to process children, but also attach to each child row the key part of the parent. XML get data should allow you to do that, maybe Samatar will come along in a little while

    Regards,
    Sven

  7. #7

    Default

    Hi,

    actually what am asking is there any way to get the sizeof the xml attribute 'Voucher1Allledgerentries.List1Amount1'.

    e.g: There is a chance of coming

    'Voucher1Allledgerentries.List1Amount1'
    'Voucher1Allledgerentries.List2Amount1'
    'Voucher1Allledgerentries.List3Amount1' etc..

    Any way to knw the size of above attribute in XML Input transformation itself

  8. #8

    Default

    Thank you very much for your inputs.

    We would like to try out the XML getdata feature in Kettle. Could you pls help us in giving some more steps in this line as we are new to Kettle.

    In your ealier post, you mentioned about Samatar. You meant to say that Samatar can provide some help in this regard ?


    Thanks in Advance.

    Sheeja

  9. #9
    Join Date
    May 2006
    Posts
    4,882

    Default

    You can dowload the plugin from http://wiki.pentaho.org/display/EAI/...ation+Plug-Ins

    Samatar Hassan is one of the open source developers, he wrote that plugin.

    Regards,
    Sven

  10. #10

    Default

    Thanks for yoiu guidance.

    Could you pls help me. Which plugin I need to download

  11. #11
    Join Date
    May 2006
    Posts
    4,882

    Default

    XML Get Data.

    Regards,
    Sven

  12. #12

    Default

    Hi,

    Is there any way to skip the transafornation.
    means if the any one row is null, than that will go to insert/update and will insert null value or if not null filed it will show error.

    is there any way to solye this

  13. #13
    Join Date
    May 2006
    Posts
    4,882

    Default

    Add a filter step in between.

    Regards,
    Sven

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.