1 Reply Latest reply on Dec 14, 2016 6:24 PM by Ramesh Reddy

    Teiid Embedded: Can I use XmlTable/XMLParse and TextTable functionality in my implementation of ResultSetExecution?

    John Rodrigues Novice

      Consider the following model definitions:

      Virtual View

      <model name="Test" type="VIRTUAL" visible="true">
        <metadata type="DDL">
        <![CDATA[
        CREATE VIEW Sales (
        Product_line VARCHAR(62),
        Product_type VARCHAR(82),
        Unit_sale_price DOUBLE,
        Unit_cost DOUBLE,
        Revenue DOUBLE)
        AS SELECT A.Product_line, A.Product_type, A.Unit_sale_price, A.Unit_cost, A.Revenue
        FROM (EXEC AdfcoreSource.executeProfileXML(52)) AS f,
        XMLTABLE('/*:dataset/*:data/*:row' PASSING XMLPARSE(DOCUMENT f.result WELLFORMED)
        COLUMNS Product_line VARCHAR(62) PATH '*[local-name()=''value''][1]/text()',
        Product_type VARCHAR(82) PATH '*[local-name()=''value''][2]/text()',
        Unit_sale_price DOUBLE PATH '*[local-name()=''value''][3]/text()',
        Unit_cost DOUBLE PATH '*[local-name()=''value''][4]/text()',
        Revenue DOUBLE PATH '*[local-name()=''value''][5]/text()') AS A]]>
        </metadata>
        </model>
      

       

      Physical Table:

      <model name="SalesMdl" type="PHYSICAL" visible="true">
        <source name="SalesMdl" translator-name="translator-adf" connection-jndi-name="java:/AdfcoreSource"/>
        <metadata type="DDL">
        <![CDATA[
         
        CREATE FOREIGN TABLE Sales (
        Product_line varchar(62),
        Product_type varchar(82),
        Unit_sale_price double,
        Unit_cost double,
        Revenue double
        ) OPTIONS (UPDATABLE FALSE);  
         ]]>
        </metadata>
        </model>
      

       

      The data backing the view or the table is obtained in the form of a stream (Could be XML or delimited text). In case of the view, all the heavy lifting of parsing the input and converting the column data to the appropriate types, returning the specified and the iteration etc is done by XMLTABLE/XMLPARSE (or by TEXTTABLE in case of the csv stream).

       

      // Procedure Execution
         @Override
         public List<?> getOutputParameterValues() throws TranslatorException {
      //... returnValue is an InputStream which gets parsed out by Teiid...
      return Collections.singletonList(new BlobType(new StreamingBlob(returnValue)));
      }
      

      My question is that in the case of the Physical table, can I leverage some of the Classes which do stream processing like in the view?

      // ResultSetExecution   
      @Override
         public List<?> next() throws TranslatorException, DataNotAvailableException {
      // Leverage XMLTABLE/XMLPARSE type functionality here??
      }