Importing XML Files with XSD Schema

[deco-dv]

Hi,

I'm currently struggeling at the task of importing mutiple XML Files that share the same XSD Schema. Using jdv 6.4.0 and teiid version 8.12.11.

Is there a support for importing multiple XML Files over a XSD Schema definition?

Example:

Accounts00-09.xml

Accounts10-19.xml

...

Account.xsd

How can I import this with the teiid designer and use it as a virtualbaselayer?

[rareddy]

Cedric,

No, you cannot substitute XSD import with multiple files.  What you can do is, but it is kind little tedious process as the complete integration does not seem to be there. You can

1) import the xsd file from file system

2) Then right click on xsd file in workspace, select "Modeling -> create relational View Model from Schema", that will generate stub View model, but empty transformation.

3) Then you can also right click on the model or project, do import "File Source (XML)  >> Source View Model" on one of the XML files you have above. When you are done with this wizard you will have "source" and "view" model.

4) Keep the source model as is, then take the generated View Model's table's transformation as base and move it into step 2's view transformation and edit to fit that view, you may have add more columns etc.

5) Then you need to come with a way how you are going to supply the file name as a parameter, that you can send in the TEXTTABLE construct.

Ramesh..

[deco-dv]

Hi Ramesh,

thank you for your answer.

My XSD Schema is a complex in fact it's a whole datamodel. When building a source model from one xml I can't get multiple tables out.

base.xsd

...

  <xs:element name="Base">

    <xs:complexType>

      <xs:sequence>

        <xs:element ref="Rule"/>

        <xs:element ref="Data"/>

        <xs:element ref="Contact"/>

        <xs:element ref="Account"/>

      </xs:sequence>

    </xs:complexType>

  </xs:element>

...

  <xs:element name="Rule">

    <xs:complexType>

      <xs:sequence>

        <xs:element minOccurs="0" ref="Reference"/>

        <xs:element minOccurs="0" ref="Term"/>

        <xs:element ref="Date"/>

      </xs:sequence>

    </xs:complexType>

  </xs:element>

....

  <xs:element name="Reference" type="SIR"/>

  <xs:element name="Term" type="FLAG"/>

  <xs:element name="Date" type="NONEMPTYDATE"/>

....

So in fact we have multiple Tables which multiple nestings, how can this be solved with the jdvand the teiid-designer? I hope you can help me.

[rareddy]

You can select a root parent for each nesting and build tables for that nesting. Then repeat the task for each nesting. Then collate all to single or multiple models 

[deco-dv]

Okay, but wouldn't this leave you without the needed relations between the entities ....

<xmll>

<base><rule></rule><rule></rule><rule></rule><base>

<base><rule></rule><base>

<base><rule></rule><rule></rule><rule></rule><base>

<base><rule></rule><rule></rule><rule></rule><rule></rule><base>

</xml>

So this should lead to 2 Tables Base and Rule, Base would have 4 entries and rules 11, but the relations would be from the 3 first rules record pointing to the first base record. I mean in the end xsd is representing a complex datamodel in my case, is the way you described viable for such a situation?

[rareddy]

No, if you set the root path at the "/" then you create one table called "rule", that is it. The elements underneath the "rule" are expected to be same.

[deco-dv]

Okay sorry to ask so much, but I really tried it a few times, however besides having massiv issues when importing the view model from xsd, I still don't understand how you would do the relational mappings. I build a small simple example, which represents a possible xml file, with a relational data model behind.

How would you solve this with the teiid designer?

<TermSheets>

<TermSheet>

  <Instrument>

   <Name>pan flute</Name>

   <Type>closed tube</Type>

   <Material>bamboo</Material>

   <Players>

    <Player>

     <Name>hans</Name>

     <Age>29</Age>

     <Orchestras>

      <Orchestra>

       <Name>teiid orchestra</Name>

      </Orchestra>

      <Orchestra>

       <Name>jboss orchestra</Name>

      </Orchestra>

     </Orchestras>

    </Player>

   </Players>

  </Instrument>

  <Instrument>

   <Name>guitar</Name>

   <Type>strings</Type>

   <Material>wood</Material>

   <Players>

    <Player>

     <Name>fritz</Name>

     <Age>35</Age>

     <Orchestras>

      <Orchestra>

       <Name>jboss orchestra</Name>

      </Orchestra>

     </Orchestras>

    </Player>

    <Player>

     <Name>hanna</Name>

     <Age>21</Age>

     <Orchestras>

     </Orchestras>

    </Player>

    <Player>

     <Name>lara</Name>

     <Age>45</Age>

     <Orchestras>

      <Orchestra>

       <Name>london orchestra</Name>

      </Orchestra>

      <Orchestra>

       <Name>teiid orchestra</Name>

      </Orchestra>

      <Orchestra>

       <Name>denodo orchestra</Name>

      </Orchestra>

     </Orchestras>

    </Player>

   </Players>

  </Instrument>

</TermSheet>

</TermSheets>

Result should be:

Instrument Table:

inst_pk, name, type, material

1, panflute, closed tube, bamboo

2, guitar, strings, wood

Player Table:

play_pk, name, age, inst_fk

1, hans, 29, 1

2, fritz, 35, 2

3, hanna, 21, 2

4, lara, 45, 2

Orchestra Table:

orch_pk, name

1, teiid orchestra

2, jboss orchestra

3, london orchestra

4, denodo orchestra

PlayerOrchestraRel_Table

plorc_pk, play_fk, orch_fk

1, 1, 1

2, 1, 2

3, 2, 2

4, 4, 1

5, 4, 3

6, 4, 4

[rareddy]

Are you trying to go from XML to relational or Relational to XML? If former, then you would have to import XSD is not may have manually design your structure.

The innermost row is "Orchestras", you set that as root, then build up one table, that combines all the data into a single table. Maybe use ROW_NUMBER like function to give an identity column. Then you can further divide that into respective Player, Instrument, Orchestra tables using big table as a base.