topic Re: XML Data Target output streaming using web services in Developers

XML Data Target output streaming using web services

jacenriquez — Tue, 26 Jan 2016 08:01:53 GMT

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

Re: XML Data Target output streaming using web services

Timmy2383 — Wed, 27 Jan 2016 16:49:00 GMT

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;

Re: XML Data Target output streaming using web services

jacenriquez — Thu, 28 Jan 2016 02:17:11 GMT

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.

Thank You,

Aaron

Re: XML Data Target output streaming using web services

Timmy2383 — Thu, 28 Jan 2016 15:14:23 GMT

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

Re: XML Data Target output streaming using web services

krishna_kmm — Thu, 09 Feb 2017 15:41:48 GMT

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