Question

Primary Key and Foreign Key insertion

  • 15 May 2013
  • 1 reply
  • 12 views

I am reading a set of XML files and inserting data into oracle tables using FME.

 

Two sample XML are as below

 

 

<Feature>

 

          <Name>WXYZ</Name>

 

          <Type>DVD</Type>          

 

          <Range>

 

            <Option>

 

              <Shop>                

 

                <Price>250</Price>

 

                <Discount>20</Discount>

 

              </Shop>

 

              <Shop>                

 

                <Price>225</Price>

 

                <Discount>5</Discount>               

 

              </Shop>

 

              <Shop>                

 

                <Price>235</Price>

 

                <Discount>15</Discount>                

 

              </Shop>              

 

            </Option>

 

          </Range>

 

        </Feature>

 

 

 

 

        <Feature>

 

          <Name>ABCD</Name>

 

          <Type>Book</Type>          

 

          <Range>

 

            <Option>

 

              <Shop>                

 

                <Price>120</Price>

 

                <Discount>10</Discount>

 

              </Shop>

 

              <Shop>                

 

                <Price>115</Price>

 

                <Discount>0</Discount>               

 

              </Shop>

 

              <Shop>                

 

                <Price>125</Price>

 

                <Discount>5</Discount>                

 

              </Shop>              

 

            </Option>

 

          </Range>

 

        </Feature>

 

 

Two tables are created as below,

 

 

feature table (fid, name, type). fid is pk and is incremented using sequence within db.

 

range table(rid, price, discount, fid). rid is pk, and fid is fk to feature table.

 

 

While reading the xml files, 2 readers are created when i set the "elements to match" as "Feature" and "Range". The feature table is populated first with 2 rows and 2 fid are generated. The  range table is polulated with rid, price, discount, but I am unable to map the particular fid pk to the range table as fk. How do I assign the foreign key with their respective primary keys in FME?

1 reply

Userlevel 5
Hi,

 

 

one solution might be to first populate the "feature" table using a SQLCaller rather than an Oracle writer.

 

 

This will enable you to request the sequence value attriuted to the newly inserted record using the RETURNING INTO clause, which you could then use when saving the "range" features. See here for an example, as well as here for more in-depth info.

 

 

David

Reply