parsing json response having jsonarr

[dineshnasa]

I am having a json structure like

{

response:{

docs:[{

test1:"SOME_VALUE1",

test2:"SOME_VALUE2"

},

{

test1:"SOME_VALUE3",

test2:"SOME_VALUE4"

}

}

I am using the following

Create VIEW TestView (test1 varchar(255), test1 varchar(255))

as

select A.test1, A.test2

from (EXEC MySource.invokeHttp('GET', null, 'testURL', 'TRUE')) as f

XMLTABLE('/RESPONSE/test' passing JSONTOXML('response', f.result) COLUMNS test1 string path 'response/docs/test1', test2 string path 'response/docs/test2') as A.

I am getting error

Error:TEIID30171:Unexpected multi valued result was returned for XMLTABLE column test1

[rareddy]

Your JSON document is is wrong, not formatted correctly. Missing quotes around names, array closing, closing brace etc. Make sure that is correct. Once corrected a query like below

select test1, test2 from XMLTABLE('/response/response/docs' passing  
     JSONTOXML('response', '{"response":{"docs":[{"test1":"SOME_VALUE1","test2":"SOME_VALUE2"},{"test1":"SOME_VALUE3","test2":"SOME_VALUE4"}]}}')
     COLUMNS test1 string path 'test1', test2 string path 'test2') as A

gives back

test1	test2
SOME_VALUE1	SOME_VALUE2
SOME_VALUE3	SOME_VALUE4