Your SAS programs, embedded in web apps and elsewhere

Stream multiple data sets as xml output

Reply
Contributor
Posts: 28

Stream multiple data sets as xml output

I would like to stream multiple data sets to a single output stream. I have used the below code but it only seems to work if I use a single data set. I would try to do this with tagset library but do not have any expererience doing so from scratch.

Code

libname ostream xml92 ;

data ostream.class;

  set sashelp.class;

run;

data ostream.shoes;

  set sashelp.shoes;

run;

libname ostream;

My preferred output

<table>

     <shoes>

          <col1>value</col1>

           <col2>value</col2>

         <shoes>

</table>

<table>

     <class>

          <col1>value</col1>

           <col2>value</col2

     </class>

<table>

SAS Super FREQ
Posts: 8,864

Re: Stream multiple data sets as xml output

Hi:

  Generally, the XML LIBNAME engine (XML92) in my understanding is 1 SAS dataset in, 1 XML file out. Or, on the input side, 1 XML file in and 1 SAS dataset out. If you need to read or write XML that is more complex than that, then you need to use an XML map to create your "mappings" from columns to tags/elements or tags to columns.

  Also, when you use the SAS, generally, an XML processing instruction precedes the first (root) tag, in this case, shown below for just 1 row from SASHELP.CLASS:

<?xml version="1.0" encoding="windows-1252" ?>

<TABLE>

   <CLASS>

      <Name>Joyce</Name>

      <Sex>F</Sex>

      <Age>11</Age>

      <Height>51.3</Height>

      <Weight>50.5</Weight>

   </CLASS>

. . . more rows . . .

</TABLE>

  

Note, that in the "vanilla" form of invocation, the structure of the XML that comes out (using your code) uses the COLUMN names as the TAG names and the variable values as the values within the delimiting tags.

Another way to write the LIBNAME statement creates this form of XML:

libname other xml92 'c:\temp\other.xml' xmldataform=attribute;
  
data other.class;
  set sashelp.class;
run;


<?xml version="1.0" encoding="windows-1252" ?>
<TABLE>
   <CLASS>
      <COLUMN name="Name" value="Joyce" />
      <COLUMN name="Sex" value="F" />
      <COLUMN name="Age" value="11" />
      <COLUMN name="Height" value="51.3" />
      <COLUMN name="Weight" value="50.5" />
   </CLASS>

"Out of the box", I'm not sure that the XML LIBNAME engine will give you numbered columns, unless your variable names are numbered. Something like this (XMLDATAFORM=ELEMENT is the default to use the COLUMN names as tag names):

libname alt xml92 'c:\temp\alternate.xml' xmldataform=element;
    
data alt.class;
  set sashelp.class(rename=(name=col1 sex=col2 age=col3 height=col4 weight=col5));
run;
  
<?xml version="1.0" encoding="windows-1252" ?>
<TABLE>
   <CLASS>
      <col1>Joyce</col1>
      <col2>F</col2>
      <col3>11</col3>
      <col4>51.3</col4>
      <col5>50.5</col5>
   </CLASS>

. . . More rows . . .
</TABLE>

However, those are just my observations of the "vanilla" XML Libname engine behavior. Back to your stream issue.

Given your code, I would expect the XML processing instruction from the second DATA step program to generate some kind of termination of the previous "stream" -- depending on how/where you were submitting your SP.

Also, I would expect to see an ERROR message from your LIBNAME statement.

5716  libname ostream xml92;

   

ERROR: Error in the LIBNAME statement.

"OSTREAM" does not seem to be a reserved file reference or reserved LIBNAME. Usually, with streaming results, _WEBOUT, is used, as shown in this doc:

http://support.sas.com/documentation/cdl/en/wbsvcdg/62759/HTML/default/viewer.htm#program.htm

and this example:

libname _WEBOUT xml xmlmeta=_XMLSCHEMA;

Also, relevant, which you do not reveal, is your entire SP code: Are you using %STPBEGIN/%STPEND? Are you using the Stored Process  Server or the Workspace server? Are you executing this SP from EG? from the SAS Add-in? from the SPWA (Stored Process Web App)? From the Information Delivery Portal? From a custom web service? Do you have an STPSRV_HEADER to set the content-type for the stream?

Before you go on a snark hunt, down the tagset template road, I would recommend that you open a track with Tech Support to find out whether your initial underlying assumption (that you can send 2 datasets with 2 XML processing instructions down a stream to a receiving client) is correct and can be accomplished using the XML Libname Engine without an XML Map. Fiddling with a TAGSET template is not going to change the fundamental behavior of the XML Libname Engine. so if you can't get 2 datasets into 1 stream without an XML Map -- using a template isn't going to change that fundamental behavior.

  To open a track with Tech Support, fill out the form at this link:

http://support.sas.com/ctx/supportform/createForm

cynthia

Contributor
Posts: 28

Re: Stream multiple data sets as xml output

Posted in reply to Cynthia_sas

Thanks for your input here. The SAS code I provided earlier is all the stored process code there is nothing more to it. I am using a Stored Process server and testing the web service  using soap ui. I have currently opened a ticket with SAS to see if it is possible to add more then one data set to a single stream. Also I believe you misunderstood the format I was looking for, below is and example of what I would be looking for using the shoes and class table provided by SAS. I will update once I have heard back from SAS Support

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">

   <soapenv:Body>

      <n:xmltest2Response xmlns:n="http://www.sas.com/xml/namespace/biwebservices">

         <n:xmltest2Result>

            <nSmiley Frustratedtreams>

               <nSmiley Surprisedstream contentType="text/xml;charset=windows-1252">

                  <n:Value>

                     <TABLE>

                        <CLASS>

                           <Name>Alfred</Name>

                           <Sex>M</Sex>

                           <Age>14</Age>

                           <Height>69</Height>

                           <Weight>112.5</Weight>

                           <Region>Africa</Region>

                           <Product>Boot</Product>

                           <Subsidiary>Addis Ababa</Subsidiary>

                           <Stores>12</Stores>

                           <Sales>29761</Sales>

                           <Inventory>191821</Inventory>

                           <Returns>769</Returns>

                        </CLASS>

                        <CLASS>

                           <Name>Alice</Name>

                           <Sex>F</Sex>

                           <Age>13</Age>

                           <Height>56.5</Height>

                           <Weight>84</Weight>

                           <Region>Africa</Region>

                           <Product>Men's Casual</Product>

                           <Subsidiary>Addis Ababa</Subsidiary>

                           <Stores>4</Stores>

                           <Sales>67242</Sales>

                           <Inventory>118036</Inventory>

                           <Returns>2284</Returns>

                        </CLASS>

  <SHOES>

                           <Region>Western Europe</Region>

                           <Product>Women's Casual</Product>

                           <Subsidiary>Rome</Subsidiary>

                           <Stores>2</Stores>

                           <Sales>19964</Sales>

                           <Inventory>62256</Inventory>

                           <Returns>954</Returns>

                        </SHOES>

                        <SHOES>

                           <Region>Western Europe</Region>

                           <Product>Women's Dress</Product>

                           <Subsidiary>Rome</Subsidiary>

                           <Stores>16</Stores>

                           <Sales>106676</Sales>

                           <Inventory>389861</Inventory>

                           <Returns>3160</Returns>

                        </SHOES>

  </TABLE>

                  </n:Value>

               </nSmiley Surprisedstream>

            </nSmiley Frustratedtreams>

         </n:xmltest2Result>

      </n:xmltest2Response>

   </soapenv:Body>

</soapenv:Envelope>

Regular Contributor
Posts: 216

Re: Stream multiple data sets as xml output

From my experience with this sort of functionality, I think you will have to

- Develop your own macro that builds this XML structure for you.

  * You'll need to pay attention to special characters (' " & < >) in your string/character variables when converting to XML.

- Pay a little bit attention when processing/validating your returned XML. Depending on the programming language used, the XML parser may complain about mixed-up tag sets!!

SAS Super FREQ
Posts: 8,864

Re: Stream multiple data sets as xml output

Hi:

  Interesting. I've seen PROC SOAP examples that have the XMLNS for SOAPENV in the doc:

http://support.sas.com/documentation/cdl/en/proc/65145/HTML/default/viewer.htm#n1vj66aphk7b9rn1xffar...

But, this is an example where you use SOAPENV with a request. Are you writing your own custom BI web services? I think the Tech Support is still your best bet.

cynthia

filename request 'c:\temp\simpleTest_REQUEST.xml';

filename response 'c:\temp\simpleTest_RESPONSE.xml';

data _null_;

   file request;

   input;

   put _infile_;

   datalines4;

<soapenv:Envelope xmlns:add="http://tempuri.org/addintegersWS"

                  xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">

   <soapenv:Header/>

   <soapenv:Body>

      <add:addintegers>

         <addSmiley Tonguearameters>

            <add:int1>20</add:int1>

            <add:int2>30</add:int2>

         </addSmiley Tonguearameters>

      </add:addintegers>

   </soapenv:Body>

   </soapenv:Envelope>

;;;;

run;

%let response=response;

proc soap in=request

          out=&response

          url="http://localhost:8080/SASBIWS/services/addintegersWS"

          wssusername="user-name"

          wsspassword="password";

run;

Ask a Question
Discussion stats
  • 4 replies
  • 1552 views
  • 3 likes
  • 3 in conversation