BookmarkSubscribeRSS Feed
jacenriquez
SAS Employee

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

 


Data_Tab.jpgExecution_Tab.jpgParameters_Tab.jpg
4 REPLIES 4
Timmy2383
Lapis Lazuli | Level 10

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;

 

 

jacenriquez
SAS Employee

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

 

 

 

Timmy2383
Lapis Lazuli | Level 10

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

krishna_kmm
Calcite | Level 5

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.

 

data chartxml;  
  input price sell; 
  datalines; 
  20 250 
  30 180 
  40 130  
  50 250 
  60 250 
  70 90 
  80 94 
  90 90 
  88 50 
  120 25 
  ; 
 run; 
 
libname _webout XML XMLmeta=schemadata; 
 
data _webout.chartxml;
   set chartxml;
run;

  

 

Find below the SOAP response.

 

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
   <soapenv:Body>
      <n:chartResponse xmlns:n="http://tempuri.org/Chart">
         <n:chartResult/>
      </n:chartResponse>
   </soapenv:Body>
</soapenv:Envelope>
 
Appreciate your help at the earliest.
 
Thanks, Krishna

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 1998 views
  • 0 likes
  • 3 in conversation