Executing an Oracle stored procedure with XMLTYPE output and capturing it in an SSIS variable

Question

I desperately need help executing an Oracle stored procedure that takes XML XMLTYPE as input and outputs an Oracle XMLTYPE XML document.

I have to execute this Oracle stored procedure using SSIS Execute SQL Task with mapping input variables and mapping OUTPUT variables.

I tried with the OLEDB adapter and the ADO.NET adapter, but did not manage to get it working.

I managed to create the type of the input variable using expressions to build the script, but could not get the Output.

If someone worked on executing Oracle stored procedures with an XMLTYPE data type, that would be very helpful if you could point me in the right direction

Here is the Oracle stored procedure that I tried to execute in SSIS.

declare
  x_api_call XMLTYPE := XMLTYPE('<APICall><SeqID>110682</SeqID></APICall>');
  x_result XMLTYPE; 
BEGIN
  XML_READ_API.GET_PERSON ( p_api_call => x_api_call, p_result => x_result );
END;

I tried to execute the above Oracle procedure in SSIS, as shown below, and with a difficult time determination for the type of the connection variable and the type of variable that I have to use to capture the XML.

declare
BEGIN
  XML_READ_API.GET_PERSON ( p_api_call => ?, p_result => ? );
END;

Thanks in advance.

Answer 1

Xmltypeis a complex thing and contains a binary representation of an XML document. Thus, there is a very low probability of its standardization as a type of interface between two such different systems.

Try passing XML as text to CLOB:

declare
  x_result XMLTYPE; 
BEGIN
  XML_READ_API.GET_PERSON ( p_api_call => XMLTYPE(?), p_result => x_result );
  ? := x_result.getCLOBVal;
END;

SQLFiddle illustration