We have a requirement to access Stored procedures residing on SQL Sever 2008 from webMethods. However, the SPs have return types of XML RAW and XML AUTO which cannot be recognised by the JDBC adapter. Is there a way to extract the output of these SPs into varchar?
FYI the SP we are trying to access is as follows:
ALTER procedure [dbo].[Int_GetDate]
AS
declare @DateNow as datetime
set @DateNow = getDate()
select @DateNow as Int_Initiate_Message FOR XML RAW–, XMLDATA
SQL XML types can be resolved as LONGVARCHAR/CLOB/BLOB JDBC Types, while the first one can be converted to java string, other two can be converted as java objects.
refer: http://msdn.microsoft.com/en-us/library/ms378813.aspx
Thanks a lot. LONGVARCHAR is the one that has worked in mycase although the XML output from the SP is actually returned in multiple result set objects. These again need to be concatenated to form a single XML file.
I’m not good in SQLs but guess some minor changes in SP will resolve your issue of multiple result set, have you tested with “TYPE” keyword in XML RAW/AUTO statement?
although the XML output from the SP is actually returned in multiple result set objects. These again need to be concatenated to form a single XML file.