Hitachi Vantara Pentaho Community Forums
Results 1 to 6 of 6

Thread: XML + webservice input and DB output

  1. #1
    Join Date
    Nov 2007
    Posts
    3

    Default XML + webservice input and DB output

    Hi all,

    First up I apologize if a similar question has already been answered on this forum before - searching the forums didn't turn up much.

    The problem:
    I have a set of multi-level XMLs that I want to extract data out of and push it into a DB. However not all data comes from the XML. Some of it comes from an external webservice and some from a tab delimited file.

    What I tried:
    I created a transformation that used an XML input to read the fields in the XML. I added a "Constant value" step to simulate the data coming from the webservice (I am ignoring the text file for now) and I added a 3rd Insert/update step to push the data into the table.

    What happened:
    The Insert/update step in the transformation refused to recognize the field names in the original XML. The only fields that were recognized were the ones coming from the 2nd step.

    If I remove the 2nd (Constant Value) step, the fields in the DB that are not present in the XML are simply deleted from the table schema. (I expected the fields to have their default values if the input didn't stream them.)

    Any ideas, suggestions, RTFMs?


    (I am using MySQL)

    thanks in advance.

    :aditya

  2. #2
    Join Date
    Oct 2007
    Posts
    15

    Default ???

    im not sure i understand your question.

    does the information from all the different sources make one complete record?

    i.e info from the xml contains info for column 1,2,3 and info from the webservice contains info for column 4,5,6 and info from the tab delimited file contains info for column 7,8,9?

  3. #3
    Join Date
    Nov 2007
    Posts
    3

    Default

    yes it does unfortunately

  4. #4
    Join Date
    Oct 2007
    Posts
    15

    Default multiple sources for one complete record

    i have done something similar in the past. I had to compile one record with information from various different sources, although my information was all coming from txt files.

    i split the process into 3 different transformations, each populating a different part of the record and linked them in the correct sequence with a job.

    the first transform used the source file with the largest portion of the record, this populated most of the information in the record. i used the normal "output to table" option for this step.

    the next transform only populated the relevant fields of the record with the information. I used an "insert/update" step to populate the rest of the record. i did the same for the third transform.

    i hope this helps. if you need some more info, let me know. maybe i can give attach a sample.

    Dan

  5. #5
    Join Date
    Nov 2007
    Posts
    3

    Default

    thanks for the pointer Dan. sounds close to what I tried today - but the insert/update step is where I have a problem. I need 2 insert/update steps and in the first step it seems to drop all the fields in the database that are not streamed in.
    A Sample would be most useful.

    thanks a lot again.

    regards,
    :aditya

  6. #6
    Join Date
    Oct 2007
    Posts
    15

    Default samlple coming right up!

    hi aditya

    i have included 4 transforms and a source file.

    the transforms extract data from different places in the source file and compile a complete record from the information. (i have X'ed out the particulars of the data). you can place the transforms into a job in the numbered sequence or run them one by one in the numbered sequence.

    you will have to extract it to a central location and make sure the input steps point to the source file. you will also need to update the database connection to your test database and table. (just create a table and run the SQL from the first tranform to create the relevant fields)

    If you need any further help, give me a shout!

    dan
    Attached Files Attached Files

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.