[SOLVED] Removing Invalid Characters from JSON response in TEIID

Removing Invalid Characters from JSON response in TEIID

I have a json response generated from invokeHTTP function and then passing it to materialized view. Apparently there is control character embedded in the response. When converting to XMLTABLE I get below error:

    at org.teiid.query.xquery.saxon.XQueryEvaluator.evaluateXQuery(XQueryEvaluator.java:147) [teiid-engine-9.1.1.jar:9.1.1]
    at org.teiid.query.processor.relational.XMLTableNode$1.run(XMLTableNode.java:269) [teiid-engine-9.1.1.jar:9.1.1]
    at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:282) [teiid-engine-9.1.1.jar:9.1.1]
    at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119) [teiid-engine-9.1.1.jar:9.1.1]
    at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210) [teiid-engine-9.1.1.jar:9.1.1]
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [rt.jar:1.8.0_202]
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [rt.jar:1.8.0_202]
    at java.lang.Thread.run(Thread.java:748) [rt.jar:1.8.0_202]
Caused by: nu.xom.ParsingException: 0x8 is not allowed in XML content

Example Response:

result
[
   {
      "firstName":"John\b",
      "lastName":"Doe"
   }
]

View definition:

CREATE VIEW Information (firstName string, lastName String) AS
    SELECT x.firstName, x.lastName 
    FROM (EXEC model.invokeHttp(action => 'GET', endpoint => url ,stream => 'TRUE')) AS f,
    XMLTABLE('/result/result' PASSING JSONTOXML('result',f.result) 
    COLUMNS 
    firstName string PATH 'firstName',
    lastName string PATH 'lastName')x

Is there any function in TEIID to remove unwanted/control characters from JSON response or skip/exclude the record that has the invalid character ?

Solution