Hitachi Vantara Pentaho Community Forums
Results 1 to 3 of 3

Thread: Creating XML files out of multiple mySQL tables

  1. #1
    Join Date
    Jul 2009
    Posts
    2

    Default Creating XML files out of multiple mySQL tables

    Hi everybody!
    I have a problem with creating XML files from data coming from a mySQL database.

    I have several tables in my database:

    Title:
    +--------+-----+-----+-----+-----+
    |TitleID*|TTTTT|TTTTT|TTTTT|TTTTT|
    +--------+-----+-----+-----+-----+

    Item:
    +-------+-------+-----+-----+
    |ItemID*|TitleID|IIIII|IIIII|
    +-------+-------+-----+-----+

    Page:
    +-------+------+-----+-----+-----+
    |PageID*|ItemID|PPPPP|PPPPP|PPPPP|
    +-------+------+-----+-----+-----+

    PageName:
    +-----------+------+-----+-----+-----+
    |PageNameID*|PageID|NNNNN|NNNNN|NNNNN|
    +-----------+------+-----+-----+-----+

    The problem is there are several Items for every Title, several Pages for every Item and several PageNames for every Page.
    What i want to create is a XML file for every Title like this:

    <title>
    <item>
    <page>
    <pagename>
    NNNNNNNN
    </pagename>
    <pagename>
    NNNNNNNN
    </pagename>
    .....
    <pagename>
    NNNNNNNN
    </pagename>
    </page>
    .....
    </item>
    .....
    </title>

    Now this is what I already have tried:

    I tried to split up the transformation. Getting all information from the Title table with a table input step. Then
    creating a XML file out of it like this:

    <title>
    TTTTTTT
    <item/>
    </tile>

    (The empty item tag is a placeholder for the page information added later)

    So the input step is connected to a Add XML step and at the same time to another table input step, which selects all items with the appropriate TitleID and creates a XML (Add XML step) out of it. (A screenshot is attached to this post)
    When i use a XML Join step afterwards to merge them every row of Item is added to the first title row resulting in this:

    <title>
    TTTTTT
    <item>
    PPPPP
    </item>
    <item>
    PPPPP
    </item>
    ......
    </title>

    The problem is only one XML file is created with the information from the first Title row and all information from Item.
    How can this be achieved. I already tried to use loops in jobs, but that turned out to be quite hard. It worked to merge all Title rows with the according Items, but I don't know how I can merge more than two, which is required in my example, this way.

    I hope I made it clear what I need and what I tried, and I hope somebody knows a way how this can be solved.

    Thanks in advance,
    Jürgen
    Attached Images Attached Images  

  2. #2

    Default

    Hi,

    Even I came across to similar problem,but my source is flat file.
    I have created multilevel xml file by using attached sample transformation.

    Please find attached trasformation and source file..it may help you.
    Please let me know any issues.

    Thanks,
    Attached Files Attached Files

  3. #3
    Join Date
    Jul 2009
    Posts
    2

    Default

    Thanks for your answerd, but the problem was that my source file was not flat. Anyway I found some kind of workaround.

    I stored it like this first:


    <title>
    <item>
    </item>
    ....

    <page>
    </page>
    <page>
    </page>
    ....

    <pagename>
    NNNNNNNN
    </pagename>
    <pagename>
    NNNNNNNN
    </pagename>
    <pagename>
    NNNNNNNN
    </pagename>
    ....

    </title>


    I used a xsl transformation afterwards to move the elements to their appropriate positions.
    Anyway if sombody knows or finds a more convenient way to get this done, please let me know!

    Kind regards,
    Jürgen

Tags for this Thread

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.