Hello Experts,
I just want to ask some questions on how to create a stored process with input parameters and output data stream (xml) and deploy it as web service? I have stored process here that fetch a string input parameter and I would like to output stream a data set as xml. But when I test it on soap UI as a webservice no value was displayed on xml. Kindly see the code below and the output from SOAP UI. I also attached the screenshots of the properties of the stored process.
CODE:
options symbolgen mlogic mprint;
%global tbl_name tbl_nm tbl_id id;
proc printto log="/sasdata/ooc/Logs/test.log";
run;
libname b "/sasdata/ooc/Lookup_Tables";
data _null_;
call symputx("tbl_nm","&tbl_name");
run;
%macro tbl_var_lst;
proc sql;
select TABLE_ID into:tbl_id
from b.ABT_TBL_LOOKUP where TABLE_NAME="&tbl_nm";
quit;
data _null_;
call symputx("id",&tbl_id);
run;
proc sql;
create table vrbl_list as
select variable_nm
from b.ABT_VRBL_LOOKUP where VARIABLE_FLG="Y" and TABLE_ID=&id;
quit;
libname out XML;
data out.vrbl_list;
set vrbl_list;
run;
%mend;
%tbl_var_lst;
OUTPUT:
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
<soapenv:Body>
<n:aBT_VRBL_Lookup2Response xmlns:n="http://tempuri.org/qwerty">
<n:aBT_VRBL_Lookup2Result>
<n:Streams/>
</n:aBT_VRBL_Lookup2Result>
</n:aBT_VRBL_Lookup2Response>
</soapenv:Body>
</soapenv:Envelope>
THANK YOU,
Aaron
Hello! I've done a few stored process web services before, but they were pretty basic in terms of the inputs/outputs.
I think you may need to make some corrections on the last step, particularly the libname and the data step. I think the data step needs to be a PROC PRINT. And I think it needs to go to _WEBOUT.
I'm using this for reference: http://support.sas.com/documentation/cdl/en/wbsvcdg/62759/HTML/default/viewer.htm#program.htm
I would try something like this, to start:
libname _WEBOUT xml xmlmeta=&_XMLSCHEMA;
proc print data=vrbl_list noobs;
run;
Hello Timmy,
Good Day! Thanks for your reply.
I tried to changed the last step of my code instead using data step I changed it to proc print like the one that you said but still no output on SOAPUI but when I check the logs created I encountered an error. Please see the new code, output on SOAPUI and the error that I encountered below:
NEW CODE:
libname _WEBOUT xml xmlmeta=&_XMLSCHEMA;
proc print data=vrbl_list noobs;run;
RESULTS ON SOAPUI:
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
<soapenv:Body>
<n:sampleResponse xmlns:n="http://tempuri.org/test">
<n:sampleResult>
<n:Streams/>
</n:sampleResult>
</n:sampleResponse>
</soapenv:Body>
</soapenv:Envelope>
ERROR:
NOTE 137-205: Line generated by the invoked macro "TBL_VAR_LST".
68 VARIABLE_FLG="Y" and TABLE_ID=&id; quit; libname _WEBOUT xml xmlmeta=&_XMLSCHEMA; proc print data=vrbl_list noobs;run;
_______
23
ERROR 23-7: Invalid value for the XMLMETA option.
NOTE 137-205: Line generated by the invoked macro "TBL_VAR_LST".
68 VARIABLE_FLG="Y" and TABLE_ID=&id; quit; libname _WEBOUT xml xmlmeta=&_XMLSCHEMA; proc print data=vrbl_list noobs;run;
__________
22
ERROR 22-7: Invalid option name _XMLSCHEMA.
Thank You,
Aaron
Maybe try changing &_XMLSCHEMA to SchemaData. I'm just going by the documentation now. Haven't tried this myself.
I don't entirely understand the differences between XMLA and structured web services, but maybe try changing your web service to XMLA so it will accept the _XMLSCHEMA value as valid? The documentation says you need to add the key word "XMLA web Service" to the stored process. I'm looking at page 9 of this documents: https://support.sas.com/documentation/cdl/en/wbsvcdg/64883/PDF/default/wbsvcdg.pdf
Hi jacenriquez,
Can you please share the solution if you have for the problem posted as Im facing same issue in streaming XML data out using web service. Find the SP code I'm running.
Find below the SOAP response.
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.