Nested xml generation from multiple SAS tables

[val_nikolajevs]

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;

[Tom]

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.

[val_nikolajevs]

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.

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

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

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

 </UploadGroup>

</UploadFile>

Using the following map,

<SXLEMAP name="AUTO_GEN" version="2.1">

<!-- ############################################################ -->
<OUTPUT>
<TABLEREF name="UPLOAD_QTY"/>
</OUTPUT>

<NAMESPACES count="0"/>

<!-- ############################################################ -->
<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:

<UploadFile>
     <UploadGroup>
         <ExternalId>123</ExternalId>
        <UPLOAD_QTY>
            ....
       </UPLOAD_QTY>

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

 </UploadGroup>

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

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

 </UploadGroup>

</UploadFile>

I got

<UploadFile>
     <UploadGroup>
         <ExternalId>123</ExternalId>
        <UPLOAD_QTY>
            ....
       </UPLOAD_QTY>

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

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

 </UploadGroup>

</UploadFile>

Thank you.

[Tom · Accepted solution]

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>

[val_nikolajevs]

Thank you Tom ! 🙂

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

THANK you.