how to create table from proc http and XML data

[shlomiohana]

Hello,

I run the following code:

filename resp temp;

proc http
 url="https://https-url.com"
 method="GET"
 out=resp;

run;

The resp file contains:

<?xml version="1.0" encoding="UTF-8"?><SMF_REPLY><SMF_SERVICE_NAME>CREATE_INTERACTION</SMF_SERVICE_NAME><SMF_REQUEST_ID>4VF1

o8j/4=</SMF_REQUEST_ID><SMF_RETURN_CODE>1</SMF_RETURN_CODE><SMF_RETURN

_MESSAGE>SMF-I-1-SUCCESS [SEND successful]</SMF_RETURN_MESSAGE></SMF_REPLY>

I want to create a table that will be created dynamically according to the tags that will be in the resp file. 

for example:

SMF_SERVICE_NAME	SMF_REQUEST_ID	SMF_RETURN_CODE	SMF_RETURN_MESSAGE
CREATE_INTERACTION	4VF1o8j/4=	1	SMF-I-1-SUCCESS [SEND successful]

If there is an additional tag in the resp file, a field will be added to the table automatically.

thanks.

[ChrisHemedinger]

If this is indeed a sample of the response, it's XML content. You can use the XML libname engine to view this as data. Try this to get started:

libname data XMLv2 resp;
proc datasets lib=data; quit;

[shlomiohana]

Hi,

I ran libname data xmlv2 resp; and got several errors:

ERROR: Libref DATA is not assigned.

ERROR: Error in the LIBNAME statement.

ERROR 23-7: Invalid value for the RESP option.

[ChrisHemedinger]

See the documentation for the XML libname engine.

If you assign your PROC HTTP output to resp, then this should work:

libname resp xmlv2;
proc datasets lib=resp; quit;

[shlomiohana]

Hi,

I ran libname resp xmlv2; and got several errors:

Error: PCDATA content found outside column/table boundary. content is CREATE_INTERACTION
Error: XML data is not in a format supported natively by the XML libname engine. Files of this type may require an XMLMap to be input properly.

[shlomiohana]

I would appreciate help on how to solve the issue?

[Quentin]

Ugh, I thought everything was returning JSON by now.  

I never dealt much with XML, but if I had to do it, I would start by searching lexjansen.com index of user group papers, for XML MAP.  This was a popular topic not that many years ago.

https://www.lexjansen.com/search/searchresults.php?q=xml+map

[Tom]

If the responses are simple, like your example, then just parse it using a simple data step.

Let's convert your example into a file.

filename resp temp;
options parmcards=resp;
parmcards4;
<?xml version="1.0" encoding="UTF-8"?><SMF_REPLY>
<SMF_SERVICE_NAME>CREATE_INTERACTION</SMF_SERVICE_NAME><SMF_REQUEST_ID>4VF1o8j/4=</SMF_REQUEST_ID>
<SMF_RETURN_CODE>1</SMF_RETURN_CODE>
<SMF_RETURN_MESSAGE>SMF-I-1-SUCCESS [SEND successful]</SMF_RETURN_MESSAGE>
</SMF_REPLY>
;;;;

And try reading it.

data want;
  infile resp dsd dlm='<' recfm=f lrecl=1000000 ;
  input @'<SMF_RETURN_CODE>' code +(-1) 
        @'<SMF_RETURN_MESSAGE>' message :$200.
  ;
run;

Result

Obs    code                 message

 1       1     SMF-I-1-SUCCESS [SEND successful]

[Patrick · Accepted solution]

Below code works for me

filename resp temp;
options parmcards=resp;
parmcards4;
<?xml version="1.0" encoding="UTF-8"?>
<SMF_REPLY>
  <SMF_SERVICE_NAME>CREATE_INTERACTION</SMF_SERVICE_NAME>
  <SMF_REQUEST_ID>4VF1o8j/4=</SMF_REQUEST_ID>
  <SMF_RETURN_CODE>1</SMF_RETURN_CODE>
  <SMF_RETURN_MESSAGE>SMF-I-1-SUCCESS [SEND successful]</SMF_RETURN_MESSAGE>
</SMF_REPLY>
;;;;

filename map temp;
libname resp xmlv2 automap=replace xmlmap=map;
proc print data=resp.smf_reply;
run;

If you've got multiple such response files to read in the same SAS session that all have the same structure then I'd be using automap=reuse in the libname statement.