Hitachi Vantara Pentaho Community Forums
Results 1 to 10 of 10

Thread: How to convert complex XML and transform to required CSV. Please help on this

  1. #1

    Default How to convert complex XML and transform to required CSV. Please help on this

    hi,

    I have multiple XML which is of complex structure. I am to read it and denormalise. but transforming to required CSV I am facing issue
    I have tried for two days. Not able to find solution .

    Please Please help on this.

    I have attached required files and CSV format.

    Hema
    Attached Images Attached Images  
    Attached Files Attached Files

  2. #2
    Join Date
    Apr 2008
    Posts
    4,690

    Default

    Please fix your KTR file to be runable on any machine...
    You currently have hardcoded values for the XML file, which name a file completely different than the attached XML.

    Also any time you have C:\Users\hemavathi_j.ITLINFOSYS it needs to be replaced with the appropriate ${Internal.Transformation.Filename.Directory}

  3. #3

    Default

    hi gutlez,

    I am sorry for the inconvience. I am new to this tool and can u please tell me how to replace with ${Internal.Transformation.Filename.Directory} . and please modify the ktr file to generate required CSV.

    Regards,
    Hema

  4. #4

    Default

    I m not getting how to give the input file in the directory which u have told. :-(
    Attached Files Attached Files

  5. #5
    Join Date
    Apr 2008
    Posts
    4,690

    Default

    In your example file, you are looking for a file called: C:\Users\hemavathi_j.ITLINFOSYS\Downloads\inputXML.xml
    You should be looking for : ${Internal.Transformation.Filename.Directory}\inputXML.xml

    This will allow anyone who downloads both your KTRFromRelationship.ktr and inputXML.xml files (and places them in the same directory) to run your example.

    Next question.
    Is there any significance to the grouping of the relationship items?

    Business Object ID 3241 has 5 Relationship items attached to it. (Note: fromRelationshipList count seems wrong... it says 3 relationships)
    The first Relationship has no attributes, the other 4 do.

    You are likely to have a lot of trouble making a "typical" table out of this, since the attribute nodes of the child records don't match between them.

  6. #6
    Join Date
    Apr 2008
    Posts
    4,690

    Default

    Took me much too long to figure out why things didn't work...

    I usually don't post solutions, because they don't really help anyone learn, and encourages "Give me teh codez!" type posts.
    See if you can figure out what I've done here.
    Attached Files Attached Files

  7. #7

    Default

    Quote Originally Posted by gutlez View Post
    In your example file, you are looking for a file called: C:\Users\hemavathi_j.ITLINFOSYS\Downloads\inputXML.xml
    You should be looking for : ${Internal.Transformation.Filename.Directory}\inputXML.xml

    This will allow anyone who downloads both your KTRFromRelationship.ktr and inputXML.xml files (and places them in the same directory) to run your example.

    Next question.
    Is there any significance to the grouping of the relationship items?

    Business Object ID 3241 has 5 Relationship items attached to it. (Note: fromRelationshipList count seems wrong... it says 3 relationships)
    The first Relationship has no attributes, the other 4 do.

    You are likely to have a lot of trouble making a "typical" table out of this, since the attribute nodes of the child records don't match between them.
    Actually gutlez, u r right few relationship contains attributes and few doest. I mannully modified the xml file as I am not supposed to post the original data. This xml structure which I have posted is same as the my original.

  8. #8

    Default

    Quote Originally Posted by gutlez View Post
    Took me much too long to figure out why things didn't work...

    I usually don't post solutions, because they don't really help anyone learn, and encourages "Give me teh codez!" type posts.
    See if you can figure out what I've done here.
    Thank you gutlez :-)

    I didn't understand the functionality of calculator. Can I please tell me how does it function and in which other context can it be used?

  9. #9
    Join Date
    Apr 2008
    Posts
    4,690

    Default

    Quote Originally Posted by Hemavathi View Post
    Thank you gutlez :-)

    I didn't understand the functionality of calculator. Can I please tell me how does it function and in which other context can it be used?
    It's not necessary. It just adds the "<?xml version="1.0" encoding="UTF-8"?>" on the beginning of the XML fragment that is being extracted out of your complex XML. After I posted this, I discovered that the XML Data step will process the XML fragment without it.

    Calculator can do a LOT of things. You should look at it carefully.

  10. #10

    Default

    Quote Originally Posted by gutlez View Post
    It's not necessary. It just adds the "<?xml version="1.0" encoding="UTF-8"?>" on the beginning of the XML fragment that is being extracted out of your complex XML. After I posted this, I discovered that the XML Data step will process the XML fragment without it.

    Calculator can do a LOT of things. You should look at it carefully.
    Ok thank you :-)

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.