SQL "XML" data type Support

[Former Member]

Hi All,

Scenario - Proxy -> JDBC stored procedure Sync - SQL 2008

I am sending whole XML as a single string to Stored procedure input parameter defined datatype as XML. I understand from the JDBC help that XML SQL datatype is not supported by PI.

When I tested my scenario with XML as well as XMLTYPE datatype I am getting below error

Unsupported parameter type 'xml' for parameter InputFieldName.

I also tried to use CLOB but it is throwing incompatible error.

latest version of SQL driver installed.

Appreciate your help which is the correct datatype to be used.

Thanks

Chirag

[Former Member · Accepted solution]

Hello,

I assume that u r passing entire message as a XML string to ur SP (after removing xml declaration)?

If that's the case u can define SQL datatype as "VARCHAR" and then test ur scenario?

Thanks

Amit Srivastava

[Former Member · Accepted solution]

Hi Amit,

yes I am using return as XML function and mapped to SP input parameter.

Issue here is SP input parameter is defined as XML and if I use any other data type in PI it just throw above error.

I am trying to see any other options.

Chirag

[Former Member · Accepted solution]

Hello Chirag,

I would suggest you to ask ur DB dev to change the SP input parameter and put it as varchar?

Thanks

Amit Srivastava

[Former Member · Accepted solution]

Hi Amit,

Issue solved by changing the SP datatype to CLOB but having issue while receiving the response from JDBC SP.

Is there any specific Response struc i have to use.

I have tried with different structure combinations but it is failing in my mapping and I am unable to view JDBC res XML message because of ICO and single stack.

I tried

1. Root -> Root -> Child Field

2. Root -> Root -> Root -> Child field.

Non of the above structure working.

SP has two output parameter and created resp DT accordingly.

Thanks

Chirag

[Former Member · Accepted solution]

Hello Chirag,

Ideally this kind of structure should work: Mt**_response -> Statement_response -> child field

But may be u can u try this also:

MtName***_response

       Statement_response

                    response_1

                            row

                                ChildField

http://scn.sap.com/people/siva.maranani/blog/2005/05/21/jdbc-stored-procedures

Thanks

Amit Srivastava

[ambrish_mishra · Accepted solution]

Take a look at this link

http://scn.sap.com/people/bhavesh.kantilal/blog/2006/07/03/jdbc-receiver-adapter--synchronous-select...

Ambrish

[ambrish_mishra]

Hi Chirag,

Take a look at the link below:

https://scn.sap.com/thread/963732

Ambrish