-
1. Re: JSON structure access
shawkins Jul 10, 2012 1:17 PM (in response to mlopezal)The issue is that the element names contain the / character. When possible just look at the xml structure of the result using something similar to:
SELECT JSONTOXML('FreebaseIDResult', '{"result": {"id": "/en/palencia", "/common/topic/article": [{"id": "/m/02wz7b"}]}}')
You should see the document structure:
<?xml version="1.0" encoding="UTF-8"?><FreebaseIDResult xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><result><id>/en/palencia</id><_u002F_common_u002F_topic_u002F_article><id>/m/02wz7b</id></_u002F_common_u002F_topic_u002F_article></result></FreebaseIDResult>
Note that /common/topic/article when used as an element name must be escaped by the SQL/XML escaping rules to generate a valid xml identifier. So you'll want to use:
XMLTABLE('/FreebaseIDResult/result/_u002F_common_u002F_topic_u002F_article/' PASSING JSONTOXML('FreebaseIDResult', result) COLUMNS FreebaseDescription string PATH 'id')
I've also updated the docs - https://docs.jboss.org/author/display/TEIID/XML+Functions - to include an example of this instead of just mentioning the escaping effect.
-
2. Re: JSON structure access
mlopezal Jul 10, 2012 1:31 PM (in response to shawkins)You're a master !!! Thanks a lot !!.