4 Replies Latest reply on Dec 29, 2012 3:16 PM by darnowsi

Teiid Parser Error Trying to Create Transformation For Web Service to Relational Table

darnowsi Dec 27, 2012 3:25 PM

Using TEIID 7.8

Created project based on example in the following article: https://community.jboss.org/wiki/ConvertXMLDataIntoRelationalTableDataUsingTeiid

Entered following transformation and received error below:

SELECT

client.*

FROM

(EXEC relational_ws.invoke(binding => 'SOAP11', action => 'WINQN2', request => '<soapenv:Envelope xmlns:soapenv=\"http://schemas.xmlsoap.org/soap/envelope/\" xmlns:nyc=\"http://otda.ny.gov/wms/nyc\"><soapenv:Header/><soapenv:Body><nyc:WINQN2-XPARM><STDHDR-PARMS><HEADER-VERSION>02</HEADER-VERSION><CLIENT-USER-ID>Your id</CLIENT-USER-ID><CLIENT-MACHINE-ID>Your PC name</CLIENT-MACHINE-ID><CLIENT-APPLICATION-ID>ESB</CLIENT-APPLICATION-ID><CLIENT-COMPONENT-NAME>ESB</CLIENT-COMPONENT-NAME><CLIENT-REQUESTED-SVC>WINQN2</CLIENT-REQUESTED-SVC><HEADER-DEBUG-FLAG>F</HEADER-DEBUG-FLAG><CLIENT-ENVIRONMENT>D</CLIENT-ENVIRONMENT><CLIENT-TIMESTAMP>12081111</CLIENT-TIMESTAMP><HEADER-RESERVED></HEADER-RESERVED></STDHDR-PARMS><WINQN2-REQUEST-DATA><START-RESULT-NUMBER>1</START-RESULT-NUMBER><MAX-RESULTS-REQUESTED>1</MAX-RESULTS-REQUESTED><SEARCH-FILTER-CRITERIA><FILTER-BY-LASTNAME-FLAG></FILTER-BY-LASTNAME-FLAG><FILTER-BY-DOB-FLAG>N</FILTER-BY-DOB-FLAG><FILTER-SSN-FLAG>N</FILTER-SSN-FLAG><INCLUDE-OTHER-NAME-FLAG>N</INCLUDE-OTHER-NAME-FLAG><SEARCH-RESULT-SORT-CODE>N</SEARCH-RESULT-SORT-CODE><NAME-SEARCH-KEY><X03-030-NAME><X24-110-SURNAME>doe</X24-110-SURNAME><X24-120-GIVNAME>joe</X24-120-GIVNAME><X24-130-MIDINIT>x</X24-130-MIDINIT></X03-030-NAME></NAME-SEARCH-KEY><X03-050-SEX>M</X03-050-SEX><X03-201-ACCT-NO></X03-201-ACCT-NO><X03-060-DOB>19480917</X03-060-DOB></SEARCH-FILTER-CRITERIA></WINQN2-REQUEST-DATA></nyc:WINQN2-XPARM>/soapenv:Body></soapenv:Envelope>', endpoint => 'http://mbtda051itrcaa.hsen:7800/wms/nyc')) AS f, XMLTABLE(XMLNAMESPACES('http://schemas.xmlsoap.org/soap/envelope/' AS env), '$d/env:WINQ2-XDATA/WINQN2-RESULT-DATA/CLIENT-DATA/' PASSING f.result AS d COLUMNS dob string PATH 'X03-060-DOB') AS client

ERROR: Error Code:ERR.015.008.0041 Message:Unable to convert procedural parameter of type string to expected type xml

XMLView Project.zip 125.6 KB

1. Re: Teiid Parser Error Trying to Create Transformation For Web Service to Relational Table

shawkins Dec 28, 2012 11:10 AM (in response to darnowsi)

Presumably the message is referring to the request xml. You can confirm this with just "select cast('xml string' as xml)". The stacktrace from the error above or from that simple select should contain the reason why the conversion failed.

Steve
Actions
2. Re: Teiid Parser Error Trying to Create Transformation For Web Service to Relational Table

darnowsi Dec 28, 2012 2:38 PM (in response to shawkins)

Steve,

The simple select using the request parameter was validated. How do I capture the stack trace?
Actions
3. Re: Teiid Parser Error Trying to Create Transformation For Web Service to Relational Table

shawkins Dec 29, 2012 9:17 AM (in response to darnowsi)

By validated do you mean that you executed against the server? If you just enter it in Designer I don't think that it will validate the conversion proactively. Full stacktraces are logged in the server log if detail logging is enabled. In any case the xml shown above is not valid. Just to make sure, unless this SQL string is embedded somewhere else you shouldn't use \ to escape ". Also the closing "/soapenv:Body>" is malformed.

Steve
Actions
4. Re: Teiid Parser Error Trying to Create Transformation For Web Service to Relational Table

darnowsi Dec 29, 2012 3:16 PM (in response to shawkins)

Thanks Steve

I made the changes to the XML as you suggested as well as an additional change to correct a XQuery syntax and it now is validated and reconciled to target coulmns in the base table. I haven't executed as yet but it looks like you got me past the compile errors.
Actions

Go to original post