topic Nested xml generation from multiple SAS tables in SAS Programming

Nested xml generation from multiple SAS tables

val_nikolajevs — Tue, 13 Jun 2023 21:57:29 GMT

Hello,

How to generate a xml file from multiple SAS tables?

I have created a xml map file in SAS XML Mapper using auto generate function. The results contain 3 different tables (x,y,z), with added columns that end with ORDINAL--which looks like IDs to map tables together.

Reading from xml file to sas tables is seamless and seems to be working as expected.

How do I assemble the xml file back from 3 SAS tables after I add/modify records in any of the tables?

the SAS XML Mapper has only one output table, so if in the output table I set up a specific table let say x1 as an output, I am able to generate xml file that would contain data only from x1 table. How to add and link data from all 3 tables?

Thank you.

filename out '\Imports\SampleFileOut.xml';
filename  SXLEMAP '\Imports\sample.map';
libname out xmlv2  xmlmap=SXLEMAP;

data out.x1;
   set  SampleFi.x1;
run;

Re: Nested xml generation from multiple SAS tables

Tom — Wed, 14 Jun 2023 02:42:31 GMT

I doubt you can. On this help page https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/engxml/n0kt78umyfvt91n1d5fyqamp3za8.htm

It says

Restriction:	The engine supports exporting from one SAS data set only.

Requirements	If you specify the OUTPUT element, you must specify one (and no more than one) TABLEREF element.

I suspect if you want to write a more complicated XML file just write the file yourself using a data step and PUT statement(s). XML is just text after all.

I believe there are other tools for transforming XML files. So perhaps you could merge your datasets together and then EXPORT the one dataset. Then call one of those external XML re-mapper tools to restructure it into what you want.

Re: Nested xml generation from multiple SAS tables

val_nikolajevs — Wed, 14 Jun 2023 13:35:47 GMT

Thank you for the reply;

I have found this article: https://support.sas.com/documentation/cdl/en/engxml/62845/HTML/default/viewer.htm#a002593019.htm

and was able to create a map file that generates a single table for the data in to SAS;however, My output, which based on only one table, still not getting desired results; in the article they were able to divide by Conference, division and only after by teams. Essentially creating nested/hierarchical structure.

The output I am looking to get is below (simplified--the hierarchy is more complex); how to create output from a single table that creates below.

        <UPLOAD_QTY>
            <Quantity>20</Quantity>
       </UPLOAD_QTY>

        <UPLOAD_QTY>
            <Quantity>30</Quantity>
       </UPLOAD_QTY>

</UploadGroup>

</UploadFile>

Using the following map,

<OUTPUT>
<TABLEREF name="UPLOAD_QTY"/>
</OUTPUT>

<TABLE description="UPLOAD_QTY" name="UPLOAD_QTY">
<TABLE-PATH syntax="XPath">/UploadFile/UploadGroup/UPLOAD_QTY</TABLE-PATH>

<COLUMN name="ExternalId" retain="YES">
<PATH syntax="XPath">/UploadFile/UploadGroup/ExternalId</PATH>
<TYPE>character</TYPE>
<DATATYPE>string</DATATYPE>
<LENGTH>10</LENGTH>
</COLUMN>

<COLUMN name="Quantity">
<PATH syntax="XPath">/UploadFile/UploadGroup/UPLOAD_QTY/Quantity</PATH>
<TYPE>numeric</TYPE>
<DATATYPE>double</DATATYPE>
</COLUMN>

</TABLE>

</SXLEMAP>

The above map for some reason does not separate by UploadGroup as in the article by Conference. How to set xpath or another parameter/s so UploadGroups are separated?

Instead of geting:

        <UPLOAD_QTY>
        .....
       </UPLOAD_QTY>

</UploadGroup>

        <UPLOAD_QTY>
        .....
       </UPLOAD_QTY>

</UploadGroup>

</UploadFile>

I got

        <UPLOAD_QTY>
        .....
       </UPLOAD_QTY>
         <ExternalId>234</ExternalId>
        <UPLOAD_QTY>
            ....
       </UPLOAD_QTY>

        <UPLOAD_QTY>
        .....
       </UPLOAD_QTY>

</UploadGroup>

</UploadFile>

Thank you.

Re: Nested xml generation from multiple SAS tables

Tom — Wed, 14 Jun 2023 19:16:45 GMT

Seems simple enough to generate.

Let's make some data to work with.

data have;
  ExternalId=123;
  do sequence=10,20,30;
    output;
  end;
run;

Now let's make an XML file.

filename want 'example.xml';
data _null_;
  set have end=eof;
  by ExternalId ;
  file want;
  if _n_=1 then put 
  '<UploadFile>'
  ;
  if first.ExternalId then put
  '  <UploadGroup>'
/ '    <ExternalId>' ExternalId +(-1) '</ExternalId>'
  ;
  put
  '    <UPLOAD_QTY>'
/ '      <sequence>' sequence +(-1) '</sequence>'
/ '    </UPLOAD_QTY>'
  ;
  if last.ExternalId then put
  '  </UploadGroup>'
  ;
  if eof then put 
  '</UploadFile>'
  ;
run;

Result

<UploadFile>
  <UploadGroup>
    <ExternalId>123</ExternalId>
    <UPLOAD_QTY>
      <sequence>10</sequence>
    </UPLOAD_QTY>
    <UPLOAD_QTY>
      <sequence>20</sequence>
    </UPLOAD_QTY>
    <UPLOAD_QTY>
      <sequence>30</sequence>
    </UPLOAD_QTY>
  </UploadGroup>
</UploadFile>

Re: Nested xml generation from multiple SAS tables

val_nikolajevs — Wed, 14 Jun 2023 20:16:56 GMT

Thank you Tom ! 🙂

Due to time constraints for xml mapping end up diving in to the "put" solution too.

THANK you.