Hitachi Vantara Pentaho Community Forums
Results 1 to 2 of 2

Thread: using spoon to convert XML to mySQL

  1. #1

    Default using spoon to convert XML to mySQL

    I am a noob with pentaho, so please forgive me if this is a simple question. I have an XML file from the census bureau that really equates to two cross referenced tables, or a single table that is not normalized.

    The file lists "concepts" - and under each "concept", there are zero to "X" number of variables. In pentaho, I have seen how to load each "concept", but each one only brings in one variable associated with it. Am I missing something on how to bring this in correctly?

    Source sample (i shortened some fields for brevity):
    <concept name="H10">
    <variable name = "H0100001" concept="H10"> Population in occupied housing units </variable>
    </concept>
    <concept name="H11">
    <variable name = "H0110001" concept="H11"> Population in occupied housing units </variable>
    <variable name = "H0110002" concept="H11"> Owned with a mortgage or a loan </variable>
    <variable name = "H0110003" concept="H11"> Owned free and clear </variable>
    <variable name = "H0110004" concept="H11"> Renter occupied </variable>
    </concept>

    So, I would expect
    Concept VarName Variable
    H10 ..... H0100001 Population in occupied housing units
    H11 ..... H0110001 Population in occupied housing units
    H11 ..... H0110002 Owned with mortgage or a loan
    H11 ..... H0110003 Owned free and clear
    H11 ..... H0110004 Renter occupied


    What I actually get: (only the first varialbe for "concept H11")
    H10 ..... H0100001 Population in occupied housing units
    H11 ..... H0110001 Population in occupied housing units


    The actual (partial) source is attached.

    sf1_partial.xml

    any help is appreciated!

  2. #2
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    Can't tell what exactly you did wrong, because you didn't attach your transformation.
    Here's a minimalist approach to produce the normalized tables, assuming each "concept" occurs only once in the XML document - else we would have to Sort rows before joining.
    Attached Files Attached Files
    So long, and thanks for all the fish.

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.