Hitachi Vantara Pentaho Community Forums
Results 1 to 2 of 2

Thread: Reading hierarchical XML and writing to multiple tables

  1. #1
    Join Date
    Jul 2010
    Posts
    10

    Default Reading hierarchical XML and writing to multiple tables

    I have some XML files coming in that are created using a .NET DataSet and the xmlWrite.xmlwritemode.WriteSchema method. The incoming XML looks like roughly like this:

    Code:
    <?xml version="1.0" standalone="yes"?>
    <NewDataSet>
      <tblCompletions>
        <CompletionsID>6b76921e-60f7-4c24-a4b0-ee936a7ee033</CompletionsID>
        <State>ND</State>
        <OriginalFileName>Sample Well File W18355.pdf</OriginalFileName>
        <tblCasing>
          <CasingID>8</CasingID>
          <CompletionsID>6b76921e-60f7-4c24-a4b0-ee936a7ee033</CompletionsID>
          <IndexOnForm>1</IndexOnForm>
          <CasingType>SURFACE</CasingType>
        </tblCasing>
        <tblCasing>
          <CasingID>9</CasingID>
          <CompletionsID>6b76921e-60f7-4c24-a4b0-ee936a7ee033</CompletionsID>
          <IndexOnForm>2</IndexOnForm>
          <CasingType>VERTICAL</CasingType>
        </tblCasing>
        <tblCoreSamples>
          <CoreSamplesID>1</CoreSamplesID>
          <CompletionsID>6b76921e-60f7-4c24-a4b0-ee936a7ee033</CompletionsID>
          <IndexOnForm>1</IndexOnForm>
          <Formation>BAKKEN</Formation>
        </tblCoreSamples>
        <tblCoreSamples>
          <CoreSamplesID>2</CoreSamplesID>
          <CompletionsID>6b76921e-60f7-4c24-a4b0-ee936a7ee033</CompletionsID>
          <IndexOnForm>2</IndexOnForm>
          <Formation>BAKKEN</Formation>
        </tblCoreSamples>
      </tblCompletions>
      <tblCompletions>
        <CompletionsID>d42b0471-b78a-4038-adec-e7d7b4aa1322</CompletionsID>
        <State>ND</State>
        <OriginalFileName>SampleWellFile_W182369.pdf</OriginalFileName>
        <tblCasing>
          <CasingID>11</CasingID>
          <CompletionsID>d42b0471-b78a-4038-adec-e7d7b4aa1322</CompletionsID>
          <IndexOnForm>1</IndexOnForm>
          <CasingType>SURFACE</CasingType>
        </tblCasing>
        <tblCasing>
          <CasingID>12</CasingID>
          <CompletionsID>d42b0471-b78a-4038-adec-e7d7b4aa1322</CompletionsID>
          <IndexOnForm>2</IndexOnForm>
          <CasingType>VERTICAL</CasingType>
        </tblCasing>
      </tblCompletions>
    </NewDataSet>
    The basic relationship is that the XML file contains multiple completions, and each completion can have zero or more casings, core samples, etc.

    I would like to read this in and then save it back to a database in basically the same format - one table for completions, one for casing, etc., with the relationships preserved. Seems like it should be very straightforward, but when I started writing the transformations it seems like I have to have multiple Get XML Data steps, then multiple wrtite table steps, possiblly with some joins in between. Since all I am really doing is replicating an existing structure, I was hoping for something closer to a single step. Does such a thing exist?

  2. #2
    Join Date
    Sep 2009
    Posts
    810

    Default

    Hi there,

    as far as I know there is no step that would guess how the XML maps to a table structure and serialize it. However, if you don't have FK-Constraints or can afford to disable them as long as the transformation runs, you can get away with only three input steps that directly map to three output steps. Each Entity (Completions, Casing, Coresamples) would be selected from the XML Input and written directly to the DB. No Joins necessary. If you cant disable FK-checks and need to observe insertion order things might get a bit more complicated, as you describe...

    Cheers

    Slawo
    Last edited by slawomir.chodnicki; 07-22-2010 at 01:47 PM.

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.