Sample XML format that can be used to write a SELECT query

[Former Member]

Hello,

I want to use and XML file in the scenario FILE to JDBC where i use the operation as SELECT in which i want to embbed an sql statemnet which is equivalent to

SELECT EMPNO, NAME FROM EMPLOYEE WHERE EMPNO>1

as in the form of XML format like,

<?xml version="1.0" encoding="UTF-8"?>

<ns0:MT_JDBC_SELECT_JDBC_REQ xmlns:ns0="http://JDBC_SELECT">

<STATEMENT>

<TABLENAME ACTION="SELECT">

<TABLE>EMPTEST</TABLE>

<ACCESS>

<EMPNO></EMPNO>

<NAME></NAME>

</ACCESS>

<KEY>

<EMPNO compareOperation="GT">1</EMPNO>

</KEY>

</TABLENAME>

</STATEMENT>

</ns0:MT_JDBC_SELECT_JDBC_REQ>

Can i do like this?

PLease help me out in using various conditions in the XML file formats like the above.

Thanks,

Soorya.

[justin_santhanam]

Surya,

I believe the GT option is not available in the above mentioned structure. But you can do in the below structure.

<ns0:MT_JDBC_SELECT_JDBC_REQ xmlns:ns0="http://JDBC_SELECT">

<STATEMENT>

<TABLENAME ACTION="SQL_QUERY">

<ACCESS>SELECT EMPNO, NAME FROM EMPLOYEE WHERE EMPNO > '$EMPNO$’</ACCESS>

<KEY>

<EMPNO>1</EMPNO>

</KEY>

</TABLENAME>

</STATEMENT>

</ns0:MT_JDBC_SELECT_JDBC_REQ>

raj.

[Former Member]

Hello raj,

I would like to write the query in that format where i mentioned as,

<?xml version="1.0" encoding="UTF-8"?>

<ns0:MT_JDBC_SELECT_JDBC_REQ xmlns:ns0="http://JDBC_SELECT">

<STATEMENT>

<TABLENAME ACTION="SELECT">

<TABLE>EMPTEST</TABLE>

<ACCESS>

<EMPNO></EMPNO>

<NAME></NAME>

</ACCESS>

<KEY>

<EMPNO compareOperation="GT">1</EMPNO>

</KEY>

</TABLENAME>

</STATEMENT>

</ns0:MT_JDBC_SELECT_JDBC_REQ>

without giving clear SQL statement as you mentioned here like,

<ns0:MT_JDBC_SELECT_JDBC_REQ xmlns:ns0="http://JDBC_SELECT">

<STATEMENT>

<TABLENAME ACTION="SQL_QUERY">

<ACCESS>SELECT EMPNO, NAME FROM EMPLOYEE WHERE EMPNO > '$EMPNO$’</ACCESS>

<KEY>

<EMPNO>1</EMPNO>

</KEY>

</TABLENAME>

</STATEMENT>

</ns0:MT_JDBC_SELECT_JDBC_REQ>

Thanks,

Soorya

[justin_santhanam]

Soorya,

I thought u want all the formats that are supported. Then ur structure itself holds good. Are u facing any issues?

raj.

[Former Member]

Raj,

i am getting this error when i am using that XML file,

Message processing failed. Cause: com.sap.aii.af.ra.ms.api.RecoverableException: Error processing request in sax parser: Error when executing statement for table/stored proc. 'EXAMPLE' (structure 'StatementName1'): java.sql.SQLException: FATAL ERROR in structure 'StatementName1': SQL queries are not supported in batch mode.

Soorya

[justin_santhanam]

Soorya,

>SQL queries are not supported in batch mode.

Are u using any advanced parameters in receiver comm.channel like batch mode. Coz SELECT query doesn't support batch mode!

raj.

[Former Member]

raj,

now it trows this error,

JDBC message processing failed; reason ERROR occured parsing request:com.sap.engine.lib.xml.parser.NestedSAXParserException: Fatal Error: com.sap.engine.lib.xml.parser.ParserException: Unsupported character: 92(:main:, row:5, col:72)(:main:, row=5, col=72) -> com.sap.engine.lib.xml.parser.ParserException: Unsupported character: 92(:main:, row:5, col:72)

Sorya

[Former Member]

raj,

i used this XML format,

<root>

<StatementName1>

<dbTableName action="SQL_QUERY">

<table>EXAMPLE</table>

<access>SELECT PROCESSED, NAME FROM EMPLOYEE WHERE EMPNO > '$ID$’</access>

<key>

<ID>1</ID>

</key>

</dbTableName>

</StatementName1>

</root>

Soorya

[justin_santhanam]

Surya,

How are u passing the value to Access element? By Constant or did u wrote UDF?

raj.

[Former Member]

and this: http://help.sap.com/saphelp_nw04/helpdata/en/2e/96fd3f2d14e869e10000000a155106/content.htm

[Former Member]

to use JDBC adapter see http://help.sap.com/saphelp_nw04/helpdata/en/14/80243b4a66ae0ce10000000a11402f/frameset.htm .

[Former Member]

I want some sample XML formats according to my requirements. So can you send some XML formats for testing