Hitachi Vantara Pentaho Community Forums
Results 1 to 3 of 3

Thread: transforming flatfile with xml content

  1. #1

    Default transforming flatfile with xml content

    Hello everyone,

    i finally signed up here just to ask for a solution after reading the forums for a long time now ,

    i have a flatfile of the following syntax:
    i have some fields like organsisation-ID, date, time, etc .. in a "classical" header,
    the header is followed by an xml-document which contains the rest of the data.

    the file looks like this (here 3 sample records):

    ABCDEFGHIHJK1234567<?xml version="1.0" encoding="utf-8"?><FullOrder> xml content ... </FullOrder>
    ABCDEFGHIHJK1234568<?xml version="1.0" encoding="utf-8"?><FullOrder> xml content ... </FullOrder>
    ABCDEFGHIHJK1234569<?xml version="1.0" encoding="utf-8"?><FullOrder> xml content ... </FullOrder>

    the xml part contains orders (as example)
    every order has an entry "Order", then followed by "OrderItems", after it the "Customer", etc..
    <FullOrder>
    <Order> xml stuff </Order>
    <OrderItem> xml stuff </OrderItem>
    <OrderItem> xml stuff </OrderItem>
    <OrderItem> xml stuff </OrderItem>
    <Customer> xml stuff </Customer>
    </FullOrder>

    i am looking now for a mechanism to transform this file with PDI.

    My target is a relational DB with tables for Order, OrderItem, Customer, etc ..
    So i need to transform the xml-document of one record in 3 (or more) lists
    and then i have to process each list.

    someone ever done something like this and can give me general hints how to solve this?

    Regards
    Marcus

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

    Default

    Very ugly file format

    - Read the file using text file input in 1 big field.
    - Split the field with e.g. javascript step, or any other way you like
    - Use get xml data step (from 3.1 and onwards) to get the xml data from a field using xpath (example is in the samples/transformation directory).

    Regards,
    Sven

  3. #3

    Default

    thanks for reply, i am still using 2.5, i think i need to take a closer look at 3.1 :-)

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.