Error building Source for context item.
mlopezal Jul 24, 2012 1:31 PMI've created a procedure what consume a service which return the next output:
{
"result": {
"guid": "#9202a8c04000641f800000000002db30",
"/travel/travel_destination/tourist_attractions": [
{
"/common/topic/official_website": "http://www.notredamedeparis.fr",
"guid": "#9202a8c04000641f800000000007678f",
"name": "Notre Dame de Paris"
},
{
"/common/topic/official_website": null,
"guid": "#9202a8c04000641f8000000000014101",
"name": "Eiffel Tower"
},
{
"/common/topic/official_website": null,
"guid": "#9202a8c04000641f80000000001d3985",
"name": "\u00cele de la Cit\u00e9"
},
{
"/common/topic/official_website": null,
"guid": "#9202a8c04000641f8000000000274f09",
"name": "Folies Berg\u00e8res"
}, ...
to avoid errors of characters I use "convert" and to_chars functions in this way:
CREATE VIRTUAL PROCEDURE
BEGIN
DECLARE string VARIABLES.IN_city = LocationREST.getTouristAttraction.guid;
DECLARE string VARIABLES.INPUT_ENDPOINT;
VARIABLES.INPUT_ENDPOINT = (('?query={"guid":"%23' || VARIABLES.IN_city) || '","/travel/travel_destination/tourist_attractions":[{"guid":null,"name":null,"/common/topic/official_website" : null}]}');
VARIABLES.INPUT_ENDPOINT = REPLACE(VARIABLES.INPUT_ENDPOINT, ' ', '%20');
CREATE LOCAL TEMPORARY TABLE TEMP (name string, website string, guid string);
LOOP ON (SELECT CONVERT(TO_CHARS(t.guidtemp, 'BASE64'), string) AS guidtemp, CONVERT(TO_CHARS(t.nametemp, 'BASE64'), string) AS nametemp, CONVERT(TO_CHARS(t.websitetemp, 'BASE64'), string) AS websitetemp FROM (EXEC FreebaseMQLRead.invokeHttp(action => 'GET', endpoint => VARIABLES.INPUT_ENDPOINT)) AS x, XMLTABLE('/FreebaseIDResult/result/_u002F_travel_u002F_travel_destination_u002F_tourist_attractions/*' PASSING JSONTOXML('FreebaseIDResult', x.result) COLUMNS guidtemp blob PATH 'guid', nametemp blob PATH 'name', websitetemp blob PATH '_u002F_common_u002F_topic_u002F_official_website') AS t) AS txncursor
BEGIN
INSERT INTO TEMP (TEMP.guid, TEMP.name, TEMP.website) VALUES (txncursor.guidtemp, txncursor.nametemp, txncursor.websitetemp);
END
SELECT XMLELEMENT(NAME TouristAttractions, XMLAGG(XMLELEMENT(NAME TouristAttraction, XMLFOREST(temp.guid AS GUID, temp.name AS NAME, temp.website AS WEBSITE)))) AS xmlResult FROM temp;
END
But I obtain the next error:
Remote org.teiid.core.TeiidProcessingException: Error building Source for context item.
Any help please?