7 Replies Latest reply on Dec 22, 2016 8:57 AM by Ramesh Reddy

    Local and table output JSON consumption in teiid

    Sanjay Chaturvedi Novice

      I am aware  of consuming JSON if it is exposed as webservice. But what if I have a local JSON file..

      How can I define procedure invokeHTTP ? what would be the value of endpoint ?

       

      Adding on to that, If some table exposed in teiid has an output of JSON format in one column ? Can I use that table output as a source model for some other model view ?

      We have one table where column output is in form of JSON that too in multiple rows. Please assist on this .

       

      Thanks.

        • 1. Re: Local and table output JSON consumption in teiid
          Sanjay Chaturvedi Novice

          SELECT cast(JSONTOXML('result', '{"firstName" : "John" , "children" : [ {"firstName": "Randy"}, {"firstName":"Judy"} ]}') as string)..

          this is i am aware of. as well as

          SELECT

            parsedate(A.Period_Date, 'yyyy-MM-dd') AS Period_Date, A.Period, A."Business Area", A."Managed By", A."Validation Complete & Approved", A."Validation Complete & Awaiting Committee", A."Validation Scheduled (& Model Docs Received)", A."Validation Scheduled (& Developer Docs Required)", A."Model Count (Calculated)", A.BOM, A.EOM

            FROM

            (EXEC Sharepoint_Source.invokeHttp(action => 'GET', request => null, endpoint => 'GetRiskData?type=Model', stream => 'TRUE', contentType => 'TRUE')) AS f, XMLTABLE(XMLNAMESPACES('urn:schemas-microsoft-com:rowset' AS rs, 'uuid:C2F41010-65B3-11d1-A29F-00AA00C14882' AS d, 'uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882' AS s), '/xml/rs:data/row' PASSING XMLPARSE(DOCUMENT f.result WELLFORMED) COLUMNS Period_Date string PATH '@ows_Date', Period string PATH '@ows_xPeriod_x0020_Date', "Business Area" string PATH '@ows_Department_x0020_or_x0020_Group', "Managed By" string PATH '@ows_Regional_x0020_or_x0020_Global', "Validation Complete & Approved" bigdecimal PATH '@ows_Validation_x0020_Complete', "Validation Complete & Awaiting Committee" bigdecimal PATH '@ows_Validation_x0020_Scheduled', "Validation Scheduled (& Model Docs Received)" bigdecimal PATH '@ows_Developer_x0020_Docs_x0020_Requi', "Validation Scheduled (& Developer Docs Required)" bigdecimal PATH '@ows_Validation_x0020_and_x0020_Doc_x', "Model Count (Calculated)" string PATH '@ows_Model_x0020_Count', BOM string PATH '@ows_BOM', EOM string PATH '@ows_EOM') AS A

           

          But what should be the data source then ? and what would be the endpoint?

          • 2. Re: Local and table output JSON consumption in teiid
            Steven Hawkins Master

            > How can I define procedure invokeHTTP ? what would be the value of endpoint ?

             

            For a file you wouldn't use invokeHTTP.  You'd use the file translator/resource adapter and one of the file procedures, such as getFile(path string) that returns a blob.

             

            > Adding on to that, If some table exposed in teiid has an output of JSON format in one column ? Can I use that table output as a source model for some other model view ?

             

            Yes, you can feed any json value into xmltable using a lateral join -

             

            select ... from tbl, XMLTABLE(... PASSING tbl.jsonColumn ...) ...

             

            for each row from tbl, the column value will be evaluated by XMLTABLE and joined to that row.

            • 3. Re: Local and table output JSON consumption in teiid
              Sanjay Chaturvedi Novice

              Thanks for reply Stevan, I got it.

              I have another query of similar kind in case of XML output

               

              <pnr>

                   <id>123</id>

                   <classification></classification>

                   <business>GLOBAL</business>

                   <Segements>

                   <segment>

                        <segnumber>1</segnumber>

                         <segname>first</segname>

                   </segment>

                   <segment>

                        <segnumber>2</segnumber>

                         <segname>second</segname>

                   </segment>

                   <segment>

                        <segnumber>3</segnumber>

                        <segname>third</segname>

                   </segment>

              </Segements>

              </pnr>

               

              while defining mapping for such kind of XML, I can easily map values of direct attributes like classfication,id and business. But not of segments because it multivalued attribute.

              For that I want one more tables mapped with it, So

              1. how to create a  table named segments having three rows in it. (Say its a local XML source)

              2. there would be several other PNRs of same kind, Hence, I want that segments table has one more column named pnr-id of its parent pnr, so that relevant pnr is mapped with it.

               

              Thanks.

              • 4. Re: Local and table output JSON consumption in teiid
                Sanjay Chaturvedi Novice

                From googling around, I found two options XMLPATHVALUE and XMLQuery. I think xmlquery would be more helpful.

                Also there would be some sort of looping would require to entertain all "segment" of "segments", Please help me building xpath/xquery for the document similar to mentioned above. Thanks.

                • 5. Re: Local and table output JSON consumption in teiid
                  Ramesh Reddy Master

                  You can put xml root path at <pnr>/<segments>, then read all the <segement> as rows, at the same time, for the parent id, classfication, business walk the xpath with ../id, ../classification etc.

                  • 6. Re: Local and table output JSON consumption in teiid
                    Sanjay Chaturvedi Novice

                    Thanks Ramesh for suggestion, But I am stuck in getting parent value say "id" in this case. Following definition would let me loop through all the rows but how to get Id ?

                    For that I have to define root as /pnr/ only instead of /pnr/Segements/segment, then , getting multiple segment value would not be possible. That's why I was looking for xpath to get rows as segement but every row has another column say id, which contains value from parent pnr-id.

                     

                     

                    SELECT

                      A.segname AS segname, A.segnumber AS segnumber

                      FROM

                      (EXEC XMLSrc.getTextFiles('test.xml')) AS f, XMLTABLE('/pnr/Segements/segment' PASSING XMLPARSE(DOCUMENT f.file) COLUMNS id string PATH 'id/text()', segname string PATH 'segname/text()', segnumber string PATH 'segnumber/text()') AS A

                     

                    Thanks.

                    • 7. Re: Local and table output JSON consumption in teiid
                      Ramesh Reddy Master

                      You need to use "../../" as I mentioned for reading the parent node. Try

                       

                      SELECT  A.id AS id, A.segname AS segname, A.segnumber AS segnumber
                      FROM
                      (EXEC XMLSrc.getTextFiles('test.xml')) AS f, XMLTABLE('/pnr/Segements/segment' PASSING XMLPARSE(DOCUMENT f.file) 
                           COLUMNS id string PATH '../../id/text()', segname string PATH 'segname/text()', segnumber string PATH 'segnumber/text()') AS A
                      

                       

                      Only when reading the different elements of sibling parents you will have an issue, that will require a different table or explicit access based on their positional index in XPATH.

                       

                      Ramesh..