Local and table output JSON consumption in teiid

[sanjay_chaturvedi]

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.

[sanjay_chaturvedi]

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?

[shawkins]

> 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.

[sanjay_chaturvedi]

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.

[sanjay_chaturvedi]

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.

[rareddy]

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.

[sanjay_chaturvedi]

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.

[rareddy]

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..