<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<vdb name="ExcelVDB" version="1">

  <description>The Excel VDB</description>

  <property name="UseConnectorMetadata" value="true" />


  <model name="PersonalValuations">
    <property name="importer.headerRowNumber" value="1"/>
    <property name="importer.ExcelFileName" value="otherholdings.xls"/>
    <source name="excel-connector" translator-name="excel"  connection-jndi-name="java:/excel-file"/>
    <metadata type="DDL"><![CDATA[

    SET NAMESPACE 'http://www.teiid.org/translator/excel/2014' AS teiid_excel;

    CREATE FOREIGN TABLE Sheet1 (
      ROW_ID integer OPTIONS (SEARCHABLE 'All_Except_Like', "teiid_excel:CELL_NUMBER" 'ROW_ID'),
      ACCOUNT_ID integer OPTIONS (SEARCHABLE 'Unsearchable', "teiid_excel:CELL_NUMBER" '1'),
      PRODUCT_TYPE string OPTIONS (SEARCHABLE 'Unsearchable', "teiid_excel:CELL_NUMBER" '2'),
      PRODUCT_VALUE string OPTIONS (SEARCHABLE 'Unsearchable', "teiid_excel:CELL_NUMBER" '3'),
      CONSTRAINT PK0 PRIMARY KEY(ROW_ID)
    ) OPTIONS ("teiid_excel:FILE" 'otherholdings.xls', "teiid_excel:FIRST_DATA_ROW_NUMBER" '2');
                                            
     ]]> </metadata>
   </model>

  <model name="OtherHoldings" type="VIRTUAL">
    <metadata type="DDL"><![CDATA[
                  
      CREATE VIEW PersonalHoldings (
        ACCOUNT_ID integer,
        PRODUCT_TYPE string,
        PRODUCT_VALUE bigdecimal
      )
        AS  
      SELECT S.ACCOUNT_ID, S.PRODUCT_TYPE, CONVERT(S.PRODUCT_VALUE, bigdecimal)  FROM PersonalValuations.Sheet1 as S
     ]]></metadata>
                                                                                                                                                                                                                                               </model>



</vdb>
