1 Reply Latest reply on Jun 14, 2013 12:36 PM by Steven Hawkins

    Hierarchial data using teiid

    preeti1987060 Newbie

      Hi,

      I have two scenerios for getting the hierarchial data .Please let me know if its possible with teiid or not.

       

      1.I have a source which is sql server table A using whihc I need to build hierarchial data with vdb or whatever using recursive sql .Is it possible ?If so what is the procedure?

       

      2.I have two sources one is sql and the other is also sql server.Need to build hierarchial data from these two sources

       

      ex:country is present in one source,state is present in second source .Using recursive sql or some other mechanism of teiid, does it allow ?If so what is the procedure?

       

      Thanks in advance,

      Preeti

        • 1. Re: Hierarchial data using teiid
          Steven Hawkins Master

          There are primarily three mechanisms for building complex results:

           

          XML Document Models (developed in Desinger)

          SQL/XML

          JSON functions (8.3+)

           

          XML Document Models are best suited when starting from an xml schema.  They also have a concept of recursive subsections of the document.   See the Designer docs for more.

           

          SQL/XML and JSON functions are for creating XML and JSON repectively from just SQL statements.  This is quite useful in may simplistic situations.  Going off of the #2 example:

           

          SELECT jsonObject(country.name, (SELECT jsonArray_agg(state.name order by state.name) FROM state where state.country_name = country.name) as "states") FROM country

           

          or with a join and aggregation as:

           

          SELECT jsonObject(country.name, jsonArray_agg(state.name order by state.name) as "states") FROM country left outer join state where country.name = state.country_name GROUP BY country.name

           

          Either would produce a row result similar to:

          {code}

          {"name":USA,"states":["Alabama","Alaska"...]}

          {code}

           

          The SQL/XML query would be similar, just with XMLELEMENT instead of jsonObject and an XMLAGG of XMLELEMENTS for the states.  See the Reference for more on both options.

           

          As for recursive situations, our SQL doesn't yet support recursive common table expressions so you'd likely have to explore a virtual procedure - but the procedure is also currently not allowed to call itself so you'd have to use properly designed loops.  If you want, go ahead and log enhancement requests if there is a particlar style of recursion that would be useful.

           

          Steve